Coding solution using  Excel VBA, copying multiple workbooks into a single one without know the name of each workbook

Posted on 2005-05-13
Last Modified: 2008-01-09

Each month 50  workbooks are downloaded into a folder, say C:\download\, the number of workbooks vary as does the their names , however the names are always in the same format - batch no. xxxx.xls, so for example

one month you might get
batch no.1234
batch no 1235
batch no. 1254

and the following month it might be

batch no. 2245
batch no. 2345
batch no. 3456

Each batch no workbook only contains 1 worksheet.

I've got my hands on a program which transfers all the sheets into one workbook and then transfers it onto a second workbook and into a single sheet.

However for this to work the batch no. xxxx.xls files must be opened.

Since having 50 open workbooks makes the process extremely slow is there a way of not having to open the work books

but still transfer them onto a single work book.

Here's the code I'm currently using:
Sub CombineAllOpenWorkbooks_1()

Dim NewFileName As String
Dim c As Integer
Dim SheetCount As Integer
  Dim J As Integer
    NewFileName = ActiveWorkbook.Name
    c = 1
    Do Until c = 0
        If Windows(c).Visible = True Then
            MsgBox ("New file to be created")
            NewFileName = Application.GetSaveAsFilename _
                (, "Microsoft Excel Workbook (*.xls),*.xls")
            ActiveWorkbook.SaveAs Filename:=NewFileName, _
            NewFileName = ActiveWorkbook.Name
            c = 0
            SheetCount = ActiveWorkbook.Sheets.Count
            c = c + 1
        End If
    For c = 1 To Workbooks.Count
        If Windows(c).Parent.Name <> NewFileName And Windows(c).Visible = True Then
            ActiveWorkbook.Sheets.Copy after:=Workbooks(NewFileName).Sheets(SheetCount)
        End If
    Next c
    On Error Resume Next
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"

    ' copy headings
    Selection.Copy Destination:=Sheets(1).Range("A1")

    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Selection.CurrentRegion.Select ' select all cells in this sheets

        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
        Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

    Workbooks.Open Filename:= _
        "U:\Transfer\Management fee batch generation DEC 04\breakdown1.xls"
End Sub

 Any ideas as to how I might do this?
Question by:LINNANDA
    LVL 1

    Accepted Solution

    This might help you combine the workbooks into one workbook more quickly, but the quickest way to do this is probably to open up each workbook one at a time then copy the information from that workbook to the master worksheet.  That way you are not doing two copies.

    For this example 'master.xls' is your master workbook that was open when I ran the macro.  Also, I added a reference to the Microsoft Scripting Runtime object.  This example only has at most two workbooks open at a time which might make things run more quickly.  

    Sub CombineWorkbooks()

        Dim fso As New FileSystemObject
        Dim Folder As Folder
        Dim File As File
        Set Folder = fso.GetFolder("C:\Download\")
        For Each File In Folder.Files
            If UCase$(Right(File.ShortName, 3)) = "XLS" Then
                Workbooks.Open FileName:=File.Name
                Sheets(1).Copy After:=Workbooks("master.xls").Sheets(Workbooks("master.xls").Sheets.Count)
            End If
        Next File
        Set File = Nothing
        Set Folder = Nothing
        Set fso = Nothing
    End Sub

    Author Comment


    Thanks for that miswmf.

    It's working fine now.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now