Excel auto-import 4 files into a worksheet with variable name?

[jouell]

Hi all,

I have four different scripts that run and download for different sets of information. Each time the four sets of script run for a different user.

For example:

I.E. When all four scripts have finished I'll have the following data sets:

user1-data-1.txt,user1-data-2.txt,user3-data-1.txt,user4-data-1.txt

I do the same for all the other users.

I have a blank spreadsheet template that I already have formula to calculate growth rates based on these data files.

What I'm doing right now space is copying and pasting each of the four data sets for each user manually in the spreadsheet.

What I would like to do is upon opening the spreadsheet, I would like Excel to import the four data sets for user1 if it's named user1.xls, etc. and put each data set in a starting specific cell.

I have created a macro that can automatically import a specific file name, but I'll be doing lots of these and I'd like to script this. I don't know how to have the file names to be imported be a variable as above.

Has anyone done this before?

I am versed in shell scripting and batch files but am just starting out in Excel.

Any thoughts are welcome!

-jouell

 

[xlbo]

Please post what you have so that we can show you how to make it dynamic

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Check out Data > Get External Data probably IMPORT. Once this has been setup for the files of interest, all you need do is Data > Refresh to get a new set of data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jouell]

Thanks All,

Here' my macro thus far.


Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/29/2008 by jouell
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\datafiles\user1-data-1.txt", Destination:=Range("E52"))
.Name = "user1-data-1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Application.Goto Reference:="Macro2"
End Sub


Also I like Data > Refresh idea, but I'd like to be able to "dynamically" set the file names as above.

Thanks again!
-jouell

 

[SkipVought]

Do you want any of the four files to import into the same querytable? Please be clear about what you mean by, "I'd like to be able to 'dynamically' set the file names as above."

Check out the Data > Validation - LIST where you can list your filenames.

Instead of the macro that you recorded, record EDITING this QueryTable and post that code please.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jouell]

Hi SkipVought

I'd like file1 to be imported and start at Cell B8.
I'd like file2 to be imported and start at Cell E8.
I'd like file3 to be imported and start at Cell B24.
I'd like file4 to be imported and start at Cell E24.

By, "I'd like to be able to 'dynamically' set the file names as above." I mean I'd like to, say, run a batch file, like "run_excel.bat JoeSmith" and then the macro will be passed the parameter JoeSmith as variable, thus importing

JoeSmith-data-1.txt,JoeSmith-data-2.txt,JoeSmith-data-3.txt,JoeSmith-data-4.txt in the cells outlined above.

Please let me know if that makes sense.

Best
-jouell

 

[SkipVought]

That's not really DYNAMIC.

Each QueryTable has a name, you can interactively choose to refresh any or all QueryTables.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jouell]

Thanks SkipVought

Do you see a solution for this? If I can tell the macro the name of the file to import via a batch file (as above) or even based on the name of the .xls file itself I believe this will work.

-jouell

 

[SkipVought]

Batch File?

I just outlined a method using Data > Import External Data.

You can NAME each QueryTable appropraitely. Then turn on your macro recorder and record the Data > Refresh and post back if you need help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jouell]

Hi SkipVought

Thanks for the response.

1) I recorded the macro using Data > Refresh, however 1) It prompted for the file 2) Data > Validation - LIST does not exist as an option for.

I don't understand your suggested solution overall. Can you clarify?


2) I'd like to be able to set user1-data-1.txt as in the macro above, snippet here:


"TEXT;C:\datafiles\relative_name_of_excel_file-data-1.txt", Destination:=Range("E52"))

That way there is no user interaction at all and everything just imports seemlessly.

Best,
-jouell

 

[SkipVought]

What I said was...

Check out the Data > Validation - LIST where you can list your filenames.

Instead of the macro that you recorded, record EDITING this QueryTable and post that code please.

You obviously only did PART 1 of what I suggested. The selection result from your Data Validation LIST, must be "plugged into" your QueryTable Refresh code, that you never posted, unless you do not need any help with "plugging in" the selection value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jouell]

Thanks - I don't understand what you're suggesting. I read what you posted, I just don't know what it means. I also don't believe that is the solution. If you can clarify I'll retry.

I am going to pursue the Macro referencing a variable name based on the name of the file as above.

Thanks for looking at this.
-jouell

 

[SkipVought]

"I also don't believe that is the solution."

Did you try Data > Import External Data > IMPORT...?

If you did, and your FORMER posted code indicates that you did, you only do that ONE TIME. That is something that you do not need to macro record because you do it ONE TIME.

What I asked you to do is macro Record EDITING the QueryTable that you inserted on the sheet. Just edit and return data to excel. Then post your recorded code. It is a VERY SIMPLE task.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jouell]

SkipVought

Thank for the update. There's no need for CAPS though or referring to task as simple or otherwise.

Here's the result. I anticipate your best solution!


Sub edit()
'
' edit Macro
' Macro recorded 10/30/2008 by Google
'

'
Range("F9").Select
ActiveCell.FormulaR1C1 = "3"
Range("F10").Select
End Sub


-jouell

 

[SkipVought]

This is not difficult.

First you ADDED an IMPORT QueryTable like this...

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/30/2008 by SkipVought
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\ii36250\My Documents\_test.txt", Destination _
        :=Range("A1"))
        .Name = "_test"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Then I asked you to EDIT the QueryTabel you added, and it would look something like thie...

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/30/2008 by SkipVought
'

'
    Range("A1").Select
    With Selection.QueryTable
        .Connection = _
        "TEXT;C:\Documents and Settings\ii36250\My Documents\_test.txt"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

but...

YOUR code will be different than mine.

So let's see your macro recorded EDIT of your QueryTable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jouell]

All,

This is now solved.

http://www.excelforum.com/excel-pro...variable-file-name-to-import.html#post1989237

Thanks to all who posted.

Best,
-jouell

 

[SkipVought]

Tere is no need to ADD the QueryTable each time this is done. Each time the ADD is executed, a QueryTable Object is added to the sheet. After a while you have a poliferation of QueryTable Objects on your sheet. The useless objects in Excel, may eventually cause you problems!

I've been TRYING to HELP you learn and understand some things, by getting you to go thru the steps, via the macro recorder, rather that just handing you a rote solution. But it seems I was not at all sucessful.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}