Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting tabs from files (Excel 2000 or 2003)

    I need to get "Summary" tabs from a number of closed workbooks and bring them into a master file for reporting. I am planning to have each individual company workbook put into a central folder, say "MASTER". Is it possible for a macro in a workbook residing in the same folder to get that sheet and pull it in? Let's say the tab is named "Summary" within each workbook file. Each workbook filename has its company number in it, at least, and maybe the word "clearing." Is it possible to say "get from file 587 clearing" the tab named "Summary" and put it in this workbook with the tab name "587 Summary"? From there, I would probably setup a Data Subtotal on a lead tab that has cell references to the indivdual company summary tabs, to show combined totals with the ablility to expand to company-specific lines. I am trying to get these summary tabs into the combined summary and have it give us a global picture without having to hand-craft one for the next 12 months for 25 companies, maybe 2 or 3 times each month as things change. Can you blame me? Thanks!

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

    Re: Getting tabs from files (Excel 2000 or 2003)

    Here is a macro:

    Sub ImportLotsOfSheets()
    ' Substitute correct path, must end in backslash
    Const strPath = "F:Master"

    Dim strFile As String
    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wbkTarget As Workbook

    On Error GoTo ErrHandler

    Set wbkTarget = ActiveWorkbook

    strFile = Dir(strPath & "*.xls")
    Do While Not strFile = ""
    Set wbkSource = Workbooks.Open(strPath & strFile)
    Set wshSource = wbkSource.Worksheets("Summary")
    wshSource.Name = Val(strFile) & " Summary"
    wshSource.Copy After:=wbkTarget.Worksheets(wbkTarget.Worksheets.C ount)
    wbkSource.Close SaveChanges:=False
    strFile = Dir
    Loop

    ExitHandler:
    Set wbkTarget = Nothing
    Set wshSource = Nothing
    Set wbkSource = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Change the path as needed. The macro will process ALL workbooks in the source folder. I have assumed that the file names begin with the company number. If that is not correct, the code will have to be adapted.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Getting tabs from files (Excel 2000 or 2003)

    Thanks! That DIR function is interesting...so it will plow through each filename in the directory and "source" a new tab in the current Target workbook from each "Summary" Worksheet tab? And I don't need to specifiy anywhere the list of files in the directory. That's a big break. Cant' wait to experiment with it. It should do the trick, though.

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

    Re: Getting tabs from files (Excel 2000 or 2003)

    The Dir function provides a way to retrieve file names in a folder. The first time, you specify what you are looking for: strPath & "*.xls" tells Dir to look for all workbooks in the folder whose name is in the strPath variable; Dir(Dir(strPath & "*.xls") returns the name of the first file that matches the specification. Subsequent calls to Dir (without argument) return the next ones, and if no more files are found, Dir returns an empty string "". Dir doesn't do anything with the found files. It is up to the programmer to do something with them. Here, the file is opened in Excel, the "Summary" worksheet is renamed and copied into the workbook running the code, and the file is closed again without saving it.

Posting Permissions

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