Refreshing all data imports on different sheets from the same file at the same time

[ExcelTricks]

I am importing a text file that has a lot more than 256 columns. The
data will be updated weekly with a different text file. I know how to
get past the 256 column limit when importing a text file like this.
This link explains the method I am using to import the text file.
http://office.microsoft.com/en-gb/as...548201033.aspx Since I am
importing onto several sheets I get the dialogue box asking me to
choose a file to import from for every sheet when I use RefreshAll. The
same file that is used by the first sheet will be used by the rest of
the sheets. Each sheet just imports a different section of columns from
the text file. I see why Excel makes me do this. When I import the .txt
file into the first sheet I reach the 256 column limit. So on the next
sheet I run another Data>Import External Data. This time choosing to
skip the columns I have already imported into the first sheet. Then I
just repeat this process with every sheet. A bit of a pain to setup,
but it only has to be done once. That is the solution Microsoft gives
in the link I posted above when you are importing more than 256 columns
worth of data. So really I am performing a seperate query for each
sheet. I want to avoid choosing the same file 30 plus times (once for
each sheet) when I click refresh all. Is there a code that would allow
me to choose the file for the first sheet, and the other sheets would
follow suite? I want to us code. I just don't know how to go about
creating such a code. I have tried recording a macro (which is at
work), but of course this won't work everytime since the filename
changes and I don't know exactly how many sheets will be used. Right
now I have to spot check what I have to make sure I still have enough
sheets to import all of the columns. Thank you for any help you can
offer.

 

[Bernie Deitrick]

Post what code you have, and someone will help you modify it to work.

HTH,
Bernie
MS Excel MVP

 

[ExcelTricks]

This is the code that excel recorded when I went through the process on
a couple of sheets. Excel didn't record the file or path I chose. When
I play the macro it stops everytime to ask for the file in the import
text dialogue box. I want to be asked once, and then that response will
be used for the rest of the queries.

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/7/2006
'

'
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Sheet2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
End Sub