Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Multiple workbooks into one workbook (Excel xp)

    I need to merge 100 excel workbooks into one worksheet in one workbook. Each workbook only has 10 or 20 rows and i need to append them all together in one workbook. All the files will be in one folder. Can someone please direct me to code that will do this? Thank you very much.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple workbooks into one workbook (Excel xp)

    A few questions:

    1- Do you want to include all of the .xls files in that folder?

    2- Does the folder have a specific name, or do you want to choose the first file from a File Open type dialog and then include all of the other files in the same directory?

    3- What is the sheet name or position in the workbooks?

    4- Are all rows in those worksheets to be copied to the new workbook.

    5- Do you want a new workbook created to hold the merged workbooks, if so where should this workbook be saved? If an existing workbook is to be used, what is its name, where is it located, or do you also want to select this from a File Open type dialog?

    6- If an existing workbook is to be used, should the merged rows be added to anything that is already in the workbook, or should any existing data be deleted?

    7- What name do you want to use for a newly created workbook if one is created, and what name for the target worksheet?
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Multiple workbooks into one workbook (Excel xp)

    Hi Legare,

    thanks for the help.

    1) yes, all the .xls files in the folder

    2) i need to choose the first file with a file open dialog

    3) sheet1, a1

    4) yes

    5) the new workbook can be saved in the same folder with a new name, it will be called main.xls with 1 sheet called total that has all the rows from all the excel files in it.

    6) the rows are appended to the bottom of the main one

    7) main.xls

    The main.xls will be merged into access later on. thank you very much for the help.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple workbooks into one workbook (Excel xp)

    See if this will do what you want:

    <pre>Option Explicit
    Public Sub WBMerge()
    Dim oNewWB As Workbook, oSH As Worksheet, oSrcWB As Workbook
    Dim I As Long, lLastRow As Long
    Dim strPath As Variant, strFileName As String
    Application.ScreenUpdating = False
    strPath = Application.GetOpenFilename("Excel (*.xls), *.xls", , "File to merge")
    If strPath = False Then Exit Sub
    strPath = Left(strPath, InStrRev(strPath, ""))
    strFileName = Dir(strPath & "*.xls", vbNormal)
    Set oNewWB = Workbooks.Add
    Set oSH = oNewWB.Worksheets("Sheet1")
    I = 0
    Do While strFileName <> ""
    Set oSrcWB = Nothing
    On Error Resume Next
    Set oSrcWB = Workbooks.Open(strPath & strFileName)
    On Error GoTo 0
    If oSrcWB Is Nothing Then
    MsgBox ("Could not open " & strPath & strFileName)
    Else
    lLastRow = oSrcWB.Worksheets("Sheet1").Range("A65536").End(xl Up).Row - 1
    Range(oSrcWB.Worksheets("Sheet1").Range("A1"), _
    oSrcWB.Worksheets("Sheet1").Range("A1").Offset(lLa stRow, 255)).Copy
    oSH.Paste Destination:=oNewWB.Worksheets("Sheet1").Range("A1 ").Offset(I, 0)
    I = I + lLastRow + 1
    Application.CutCopyMode = xlCopy
    oSrcWB.Close
    End If
    strFileName = Dir
    Loop
    oSH.Name = "Total"
    oNewWB.SaveAs Filename:=strPath & "Main.xls"
    oNewWB.Close
    Application.ScreenUpdating = True
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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