Combine Several Files Into One

vickyho1008 · Oct 12th 2006

I have several files saved under the same folder (which each has one worksheet only), I would like to single click of a button to combine all these files into one (by entering the location of the folder where all these files are saved), so that I can have just 1 file that contains different worksheets. thanks

is it also possible to extract the name from a single cell from each file (same location on all files), and use that to rename the tab in the combined file?

gollem · Oct 13th 2006

Re: Combine Several Files Into One

Hi,

this macro opens every file in a directory and copies a certain sheet to the main workbook:

Code

Dim objFso      As Object
    Dim objFolder   As Object
    Dim objFile     As Object
    Dim objFiles    As Object
    Dim strFolder   As String
    Dim strWorkbook As String
    
    strWorkbook = "ozz.xls" 'Main workbook
    
    'Create objects
    Set objFso = CreateObject("Scripting.FileSystemObject")
    strFolder = "c:\tmp\test" 'Folder directory
    Set objFolder = objFso.getfolder(strFolder)
    Set objFiles = objFolder.Files
    
    'Loop through files
    For Each objFile In objFiles
        Workbooks.Open strFolder & "\" & objFile.Name
        'Copy sheet
        ActiveWorkbook.Sheets(1).Copy Before:=Workbooks(strWorkbook).Sheets(1) 'Copy sheet
        Windows(objFile.Name).Activate
        ActiveWorkbook.Close
    Next objFile
    
        
    'Clear memory
    Set objFile = Nothing
    Set objFiles = Nothing
    Set objFolder = Nothing
    Set objFso = Nothing

Display More

Hope this gives you a start.

Gollem

Combine Several Files Into One

Participate now!

Share