Using Manual Filename Variable To Import Data

Aug 18, 2006

referencing a variable during a data import. I have searched the forums here and the web for a couple weeks and attempted enclosing the variable in many different symbols.

Nothing I have tried works. I have also read chip pearsons guide but I was still not able to get it to work.

I am trying to provide a way for the user to input the filename and location in an input box and then use that variable to import the data. It is only one file that is needed.

The message box filename is only included to allow me to verify the text input.

Dim Filename As String

Sheets.Add. Name = "All Data"
Filename = InputBox("Enter Filename: ", "Enter Filename Location")

MsgBox Filename
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;Filename", Destination:=Range("A1"))
.Name = "SHOAlarmsJune2-9"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False

View 3 Replies


ADVERTISEMENT

Import CSV To Excel But Filename Changes Daily

Nov 3, 2011

Problem I am having:

I want to import a csv file to an Excel workbook. The csv file name does not change, but the workbook which it is pasted into changes name every day (it has a filename which ends in todays date, and is updated daily).

Using the macro recorder I can get the process to work for today, but when as soon as I update the recipient files name the next day, the macro fails.

Code below:

' Sheets("MORCOM").Select
'Range("A5").Select
'Cells.Select
'Selection.ClearContents
'Range("A1").Select
'Workbooks.Open Filename:= _

[Code] .......

View 5 Replies View Related

Import Multiple CSV Files From Single Directory And Include All Or Part Of Filename

Jul 18, 2014

I am looking for some VB to import multiple csv files, stored in a single directory, into a single excel worksheet. However I am looking to also include all or part of the filename, so to differentiate each file.

I've attached an example spreadsheet, inc the below code, of what I am trying to achieve.

Current code is (this parsers all .csv files in the specified folder into a single worksheet):

[Code] ......

Attached File : csv_loader.xlsm‎

View 6 Replies View Related

Using Variable Filename In VLOOKUP

Mar 26, 2014

I'm working on a project where I need to populate a column with vlookups taken from different workbooks, I have for the most part got it working. However currently, everytime it populates a cell it prompts the user to select the sheet from the workbook as there are two. The sheet name is always the same so I tried to add it into the code so it would avoid having to repeatedly click okay while it populated the table.

This is the original code where it asks for the user to select the sheet every time.

[Code].....

This is what is produced when it is run and the user selects the worksheet each time (which works perfectly fine):

[Code] ...........

I tried to enter the Full Costs sheet name into the code like so:

[Code] ......

However this produces the following:

[Code] .....

The issue I have found is that the square brackets that are around the filename are generated automatically, they aren’t in the actual filename and I haven’t put them in, so I am struggling to work out how to add the sheet name in where it wont be included within the square brackets, as that is what's breaking the lookup.

View 3 Replies View Related

Variable In Filename Reference

Apr 17, 2007

I'm working on a spreadsheet that will access two weeks worth of data at a time. Is it possible to have the filename reference contain a variable or a link to another cell in it?

For example: ...

View 6 Replies View Related

Split String Variable And Use Some In Filename

Mar 16, 2009

I have a filname called ex: "Statusreport xxyyzz.xls"

I'm only interested to put xxyyzz in a variable and reuse it for saving another workbook.

How can I use the different string functions for this. The word "Statusreport" is used for apporx 20 different workbooks.

View 9 Replies View Related

Pass Filename To String Variable

Aug 3, 2006

Have a file lets say is named

sample.xls

Does anyone know how to retrieve the name of the file in VB?

just the name and put it in a string?

View 3 Replies View Related

Define Path & Filename As Variable

Feb 16, 2007

Private Sub cmdImport_Click()
Dim exportFile As String
exportFile = "c:jpmimportTrinity_ImpImport_" & Format( Date, "dd-mm-yy") + "_" + Format(Time, "hh:mm:ss") & ".csv"
Open exportFile For Output As #1

I put a watch on exportFile and it's correctly set to: "c:jpmimportTrinity_ImpImport_15-02-07_17:55:01.csv"

However if I try to run this I get "bad file name or numer" when I open for output - the path exists and I can write to it, so must have a fundamentally wrong approach.

View 2 Replies View Related

Vba Code Which Saves A Workbook With A Variable As The Filename

Jun 25, 2007

I have a VBA form that the user enters information into and once they click submit on the form the code opens a new workbook and then enters the information from the form into that workbook. What I need is for that code to then save the workbook with a filename from one of the fields entered into the form but I'm not too sure how to do this.

View 3 Replies View Related

Get A Module Wide Variable To Provide The Current Filename To The Various Subs

Mar 10, 2009

I am trying to get a module wide variable to provide the current filename to the various subs.

View 2 Replies View Related

Import Variable From Another Function Into Subroutine?

Jan 19, 2014

In these lines of code I am adding hyperlinks to cells in a row. It shows my steps recorded but since there are some 4,000 cells I need a macro to do it automagically. The target cells are in sequential worksheets but in each case follow the sane pattern (i.e. P2, H3, X3, D4, etc.). Could I capture this routine for each sheet by the sub for each? If so how?

Range("B2").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, address:="", SubAddress:= _
"'Max tree base'!P2", TextToDisplay:="'Max tree base'!P2"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, address:="", SubAddress:= _
"'Max tree base'!H3", TextToDisplay:="'Max tree base'!H3"

[Code] ...........

View 1 Replies View Related

Import From File One Level Up With Variable Name

Mar 11, 2007

I wish to import data from another workbook which will always be one directory level up from the target workbook. The problem is that the source workbook's name will change but it can always have the first four characters the same, ie, List_Dublin, List_Kildare, etc.

View 4 Replies View Related

Preventing Manual Data Entry Errors

Mar 21, 2009

how to protect my spreadsheet from data entry errors.

I have a column of data that must be entered manually (column B). Sometimes, the entry in column B could have been calculated by running a formula run upon the data in column A. Sometimes it has to be entered manually.

Is there a way to use data validation or conditional formatting to make sure that the proper figure is entered into column B when it could have been calculated by a formula to avoid data errors?

View 9 Replies View Related

Replace Filename With Variable In "Activecell.FormulaR1C1=" Expression

Feb 27, 2009

Replace filename with variable in "Activecell.FormulaR1C1=" expression
I have recorded a macro which contains several lines of code like this one:

View 2 Replies View Related

Excel Macro Referencing A Variable File Name To Import

Oct 30, 2008

I have this Macro for Excel which imports 1 file C:datafilesuser1-data-1.txt into cell E52:

View 6 Replies View Related

Windows ("variable Filename").activate

May 26, 2009

I have created a macro to split out information from one excel workbook into 2 additional workbooks, and format them the way we need. Everything works fine on the filename I originally recorded the macro from, but is giving me a 'subscript out of range' error when trying to run the macro on a file with a different name.

currently, my test file has the 2 following fields that I somehow need to have as variables:

Windows("insmacrotest20090418.xls").Activate

and

Windows("Book1").Activate

I need the filenames and workbooks in the parenthesis to be variable so that the macro can be run on other reports with varying filenames.

View 10 Replies View Related

Conditional Copying - Variable Ranges - Sprint Cell Phone Usage Import

Nov 30, 2008

I am working on a project to import cellular phone usage data from Sprint into a workbook.

All cells in Column A are blank. Column B contains 1 instance of the cell phone account #, followed by dates of calls made, which continues until the next cell phone account # appears, and it’s calls made. The # of calls made obviously varies per account #.

The dates are in date format. The cell phone account # is in text format and is formatted with blue fill.

I need to copy the cell phone account # into column A for each line that has a date value in ColumnB, but the value copied must change when it reaches the next cell phone account #, so that the calls are matched up with the cell phone account # and can be imported into SQL.

Acct #
Date
Time
City Called from
State Called From
Called #
City Called
State Called................

View 17 Replies View Related

Auto Generate Save As Filename From Multiple Cell Data?

Sep 14, 2012

way for my engineers to save a field ticket with a certain name based on data from a couple of cells in the worksheet. Re: Auto generate "Save As" filename from text and tried to use some code posted in the thread, but I an still not having any luck.

What I want to do is create an active X button when clicked on, would save the workbook to a certain folder. I want the name to look like this:

SO1!M3_SO1!M6_SO1!H2.xls

This is what I have so far:

Private Sub SaveMe()
ThisWorkbook.SaveAs Filename:="C:usersdefaultdesktop" & Range("SO1!M3").Value & Format(Range("SO1!M3").Value, "text") & ".xls"
End Sub

Would I click "general" or "workbook" in VBA when I enter this code?

View 5 Replies View Related

Increase Invoice Number And Save To Desktop As PDF Using Cell Data For Filename

May 15, 2014

Our business has a spreadsheet set up for invoicing. A Macro has been created to increase the invoice number, save to the desktop as a PDF with a file name taken from the cells

This is working on my colleagues mac but when we've loaded onto my computer it errors, when I debug I am getting the following error:

Sub Macro3()
'
' Macro3 Macro
'

'
Range("g10").Select
[g10] = [g10] + 1

[Code] .......

View 2 Replies View Related

Amending The Filename In Cell("filename")

Jul 7, 2006

I am trying to display the Filename in a cell on my worksheet. However, when I enter the =Cell("filename"), it provides the full path (C:/Documents......Cost Summary.xls) How can I display only the filename without the path?

View 3 Replies View Related

Excel 2007 :: Import Data From Worksheet Into Another And Group Data Into Sections

Mar 2, 2012

I want to be able to run a macro that will export some of the data from a worksheet called Generated Report into another worksheet called Matrix. The data will be taken Generated REport and grouped into sections of the worksheet. These sections will be defined by the BRID value.

The Data that i want to export will be from the first 5 columns within the Generated Report(note there are other columns which data is not required from).

The following columns will be exported from Generated Report -

BRID,Requirement, Bug Description, Bug ID and Alt Bug ID.

BRIDRequirement Bug DescriptionBUG ID Alt Bug ID
PQ115 PQ115-Login Bug Description text xihllloloj43712 123
PQ116PQ116-Landing Bug Description text ghghghgoot 43713 126

For Matrix

The data will be imported into Matrix worksheet into the following columns as part of the row headings

- Requirement, Description, Bug ID and VF Bug ID.There will also be other columns (outlined below) which i will use to manually enter data

The data will be grouped and imported into the relevant section by its BRID eg PQ115, PQ116 etc.

I want each section (marked by BRID number)to be separated by row headings.

These row headings are to be generated after populating all data within each BRID number.

When the data has been populated in each row there is a column called 'Status' which contains drop down list boxes which can be assigned.

The default status will be set to 'To Do'. The complete row headings are displayed with Output from Generated Report underneath

BRID Priority Requirment Description BugID Alt Bug ID PC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ115 PQ115-Login Bug Description text xihllloloj43712 123 To Do

BRID Priority Requirment Description BugID Alt Bug ID QC UpdateNotes Actual Result Type of DEfect Test Status Tested by
PQ116 PQ116-Landing Bug Description text ghghghgoot43713 126 To Do

So far the macro that i have been using allows me to locate the data for each specific BRID in Generated Report and copy into the corresponding BRID section within the Matrix. I have to repeat this step for each new BRID and i want to be able to whole automate this process.

View 5 Replies View Related

Date Format- Data Retrieved Via Import Data Tools

Feb 4, 2009

I have the following data retrieved via import data tools ...

View 9 Replies View Related

Import Few Data From Table To Another Sheet By Date Data?

Jan 22, 2014

i attach the file so you can look after you read

i have soccer table 1 sheet is "teams" which is the table of the league. 1 sheet is "games" which is the table of games of the league the last sheet called "import by date". in the end of every game day i sent email with the results of current day. for now im copy and pate all results and its not that easy and convenient. i want to make some formula that when i click in import data sheet the date its shows me all the results from that date. i try to do it with pivot table but didnt like how it displayd

View 2 Replies View Related

Import Website Data Into Excel (Scrapping Of Data)

Jun 20, 2014

I have a tedious task of copy/paste from our website. have a look at below sample data. I have around 1500 rows of data like this.

Sheet1
A
B

[Code]....

Column A has input data which i need to copy and paste in a web form. Column B will have results scrapped form webpage[ URL]. This webpage will need login details

If a code can be written which take value form column A of spreadsheet and paste in webpage like shown in above image and hit search button. Next webpage will be loaded with number of results like shown in below. Take the number of results and paste in column B of spreadsheet.

View 1 Replies View Related

Import Multiple Text Files & Copy Each Import

Jan 29, 2008

I'm attempting to import around 200 (and growing!) separate text files into Excel. I am using the formula below to import the text file and then using a separate macro to select the information I need, copy it into another spreadsheet, and then run the import macro again.

However, I have a problem in that my import macro gives me 'Run-time error '1004:

Application defined or user defined error''. At first this wasn't a problem as the information is pasted into the spreadsheet despite the error anyway. However, now that I am looping the macro it is obviously causing more problems as it prevents the loop. I would really appreciate it if anyone knows of a work-around or can spot an error in the coding to resolve this!

The code below shows is for the import macro only:

Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

View 8 Replies View Related

External Data In Spreadsheet Without Having To Import All The Data

Aug 6, 2009

I'm wondering if I can use external data in an excel spreadsheet without having to import all the data. I basically need a persistent connection with the external data and I want the excel spreadsheet to get the item descriptions by using the item number as the user types in the item number. Writing out the descriptions is killing me and I have all my products in an excel spreadsheet already. I really need someway to link two separate excel files together. One with data and one with user input that searches that data as I type in the item number.

View 2 Replies View Related

Import Data From Different Tab

Jun 17, 2014

I have a tab in my workbook with 10 various items 50 pieces each, in the 2nd tab i have about 100 orders for these items. What is the best way for me to find out for each of the 10 items how much i sold?

View 6 Replies View Related

Import Data

Jan 14, 2010

I have this VBA macro which imports data from a text file. I do not know exactly how to make it good.

I have two sheets, named <INDATA> <Drawing_list>

On sheet <Drawing_List> I made a command button with this ....

View 14 Replies View Related

Import Of Data ...

May 18, 2009

The task that I have to make is a little bit complicated, especially for reason that I am not v VBA programmer. But however here is the task (and is not a school assignment).

First I have to clear the worksheet Invoice_Plan (file Financialplan.xls), but the whole row(s) that have data in column F (comment) have to stay on worksheet.

Than I have a worksheet Data (file invoices.xls where are all invoices from year 2009) and in column A are the data, that I have to import into in worksheet Invoice_Plan to (second file FinancialPlan.xls) in column A data under following rules (other must be skipped):

View 9 Replies View Related

Import Data From The Web (VBA)

Aug 27, 2009

there is i website that i want to imoport data from it to my excel sheet using vba, but the poblem is that the website will need my username and password...

i search around for possible threads and i found a very close one but couldnt have it do the submit option. also i donot want it to show me the intenet explorer window. i just wantit to do these steps:
after clicking a button:

1-logon to the site using my user and password
2-redirecting to the report link and improt what ever is in that page
3- all this with out opening IE.

Public Declare Function ShowWindow Lib "user32" (ByVal lHwnd As Long, ByVal lCmdShow As Long) As Boolean

Private Sub LoginTone()

'Dim ie As InternetExplorer
'Set ie = New InternetExplorer
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

With ie
.Navigate "http://s8.brono.com:80/mongol/fiona/index.php" ; this is the login address
Do While .busy And .readyState 4: DoEvents: Loop.......................

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved