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

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.

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

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message news:[email protected]...
>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.
>

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