+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    ExcelTricks@gmail.com
    Guest

    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.


  2. #2
    Bernie Deitrick
    Guest

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

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

    HTH,
    Bernie
    MS Excel MVP


    <ExcelTricks@gmail.com> wrote in message news:1154891335.793487.82120@75g2000cwc.googlegroups.com...
    >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.
    >




  3. #3
    ExcelTricks@gmail.com
    Guest

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

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1