Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    merging workbooks (2003 SP2)

    I have more than 20 workbooks, each of which contains 3 to 6 worksheets.
    I would like to find an easy way to merge them into one workbook.
    Any help would be appreciated.

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

    Re: merging workbooks (2003 SP2)

    If you place all the workbooks to be merged in a single folder, with no other workbooks, you can run the following macro:

    Sub MergeWorkbooks()
    ' Modify as needed, but keep trailing backslash
    Const strPath = "C:Test"
    Dim strFile As String
    Dim wbkSrc As Workbook
    Dim wbkTrg As Workbook

    On Error GoTo ErrHandler

    ' Suppress messages
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    ' Create workbook with one sheet
    Set wbkTrg = Workbooks.Add(Template:=xlWBATWorksheet)
    ' Loop through workbooks in folder
    strFile = Dir(strPath & "*.xls")

    Do While Not strFile = ""
    ' Open workbook
    Set wbkSrc = Workbooks.Open(Filename:=strPath & strFile, _
    AddToMRU:=False)
    ' Copy sheets
    wbkSrc.Worksheets.Copy After:=wbkTrg.Worksheets(1)
    ' Close workbook
    wbkSrc.Close SaveChanges:=False
    ' On to the next one
    strFile = Dir
    Loop

    ' Delete initial sheet
    wbkTrg.Worksheets(1).Delete

    ExitHandler:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Exit Sub

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

    Change the constant strPath to the path of the folder that contains the workbooks before running the macro.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merging workbooks (2003 SP2)

    I get a "compile error - syntax error" for the line that contains my actual path name...
    It almost looks like it doesn't like the ":" in the path name (?)

    Sorry, I don't often run macros. Maybe it's my stupidity...

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

    Re: merging workbooks (2003 SP2)

    Did you put quotes around the path?

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

    Re: merging workbooks (2003 SP2)

    I have attached a workbook with a slightly modified version of the macro.
    Click the button on the worksheet to run the macro; you can select the folder in a dialog.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merging workbooks (2003 SP2)

    When I click on the button, It wants me to select the folder containing the XLS files that I want to merge...
    After I select the folder, nothing seems to happen...

  7. #7
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merging workbooks (2003 SP2)

    No, I didn't...

    Should I?

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

    Re: merging workbooks (2003 SP2)

    Yes, string values must be enclosed in quotes, as in the code I originally posted. For example, if your workbooks are in the folder

    Cocuments and SettingsTFBoniMy Documents

    the definition of the constant should be
    <code>
    strPath = "Cocuments and SettingsTFBoniMy Documents"
    </code>
    Note the backslash after the path and the quotes around it.

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

    Re: merging workbooks (2003 SP2)

    Are the workbooks password protected?

  10. #10
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merging workbooks (2003 SP2)

    I'll try that...

    Thanks for your patience.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merging workbooks (2003 SP2)

    Not as far as I can tell...

    When I go to tools, protection they don't seem to be.
    I am able to edit them...

  12. #12
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merging workbooks (2003 SP2)

    When I do alt+F11 for the Visual Basic Editor, how do I get a blank "sheet" to paste what you gave me onto?

    Earlier today, I was able to paste what you gave me, now I can't remember how I was able to...

    Obviously I need to learn the basics of using the VBE...

    Thanks again for your patience.

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

    Re: merging workbooks (2003 SP2)

    The code should work then - I tested it several times on a folder with some workbooks.
    You might temporarily turn the line
    <code>
    Application.DisplayAlerts = False
    </code>
    into a comment by inserting an apostrophe ' in front of it:
    <code>
    ' Application.DisplayAlerts = False
    </code>
    (There's no need to do anything with the line Application.DisplayAlerts = True)
    Perhaps you'll see a dialog after selecting a folder and clicking OK.

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

    Re: merging workbooks (2003 SP2)

    Select Insert | Module in the Visual Basic Editor.

  15. #15
    3 Star Lounger
    Join Date
    Aug 2003
    Location
    Voorhees, New Jersey
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: merging workbooks (2003 SP2)

    It worked !...

    It was the quotes and having the "" at the end...

    I really need to get used to using the VBE thing on a regular basis...as is obvious, it's not second nature to me.
    Among other issues, I get confused by just how it relates to my workbook(s).

    Does it attach itself to one workbook or is it available to all?

Page 1 of 2 12 LastLast

Posting Permissions

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