Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Refresh Multiple Sheets at Once (2002/SP-2)

    I have a text file (text.txt) with multiple formats (for example let us assume 3). I have imported text.txt into three sheets using an unique format for each sheet. Of course, text.txt changes over time, so I presently "Refresh" each sheet1, then sheet2, then sheet3. How can I "Refresh" all three sheets in one step?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh Multiple Sheets at Once (2002/SP-2)

    Welcome to Woody's Lounge!

    Can you explain
    a) How you import the text file to 3 different sheets.
    [img]/forums/images/smilies/cool.gif[/img] How you refresh each sheet now.
    Thanks.

  3. #3
    New Lounger
    Join Date
    Jan 2005
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Refresh Multiple Sheets at Once (2002/SP-2)

    Hans,
    Thanks for your interest in my refresh of multiply Excel sheets in one "step". See the attached file for a detailed response to your questions.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Refresh Multiple Sheets at Once (2002/SP-2)

    Try this. Replace the sheet names with the appropriate ones.

    Sub RefreshTables()
    Dim varFilename As Variant
    varFilename = Application.GetOpenFilename("Text files (*.txt), *.txt")
    If Not (varFilename = False) Then
    RefreshSheet "Sheet1", varFilename
    RefreshSheet "Sheet2", varFilename
    RefreshSheet "Sheet4", varFilename
    RefreshSheet "Sheet7", varFilename
    RefreshSheet "Sheet12", varFilename
    End If
    End Sub

    Sub RefreshSheet(ByVal strSheetname As String, ByVal strFilename As String)
    Dim varDatatypes, varColWidths
    With Worksheets(strSheetname).QueryTables(1)
    varDatatypes = .TextFileColumnDataTypes
    varColWidths = .TextFileFixedColumnWidths
    .TextFilePromptOnRefresh = False
    .Connection = "TEXT;" & strFilename
    .TextFileParseType = xlFixedWidth
    .TextFileColumnDataTypes = varDatatypes
    .TextFileFixedColumnWidths = varColWidths
    .Refresh BackgroundQuery:=False
    .TextFilePromptOnRefresh = True
    End With
    End Sub

Posting Permissions

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