Using VBA scripts to Combine multiple workbooks of single worksheet to a single workbook of multiple worksheets

[Excel_beginner]

I am a beginner to Excel and VBA, can somebody show me a few lines of scripts and instructions how to use VBA scripts to combine multiple Excel xls Files (which contain single worksheet) into a single Excel file of multiple worksheets?

Can somebody also suggest a good book with examples I can start to learn to solve these kinds of problems?

Thanks very much

Excel_beginner

 

[Norie]

Danny

There is nothing in the code I posted that deletes anything.

 

[xenou]

Norie, I find this in your code posted Jan 18, 2008 6:38PM

wbDst.Worksheets(1).Delete

I am assuming you want to remove the blank Sheet1 that is going to stand in front of all the new sheets brought in....

is it possible that this is happening more than once?

Regards.

 

[Slomaro2000]

I was on the hunt for a macro that will take all the .xls files in a folder and copy them into one "master" .xls file and place that file into a different folder.


I think this might work though.

But when I use the macro

'Description: Combines all files in a folder to a master file.
Sub MergeFiles()
    Dim path As String, ThisWB As String, lngFilecounter As Long
    Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
    Dim Filename As String, Wkb As Workbook
    Dim CopyRng As Range, Dest As Range
    Dim RowofCopySheet As Integer
 
    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from
 
    ThisWB = ActiveWorkbook.Name
 
    path = GetDirectory("Select a folder containing Excel files you want to merge")
 
    Application.EnableEvents = False
    Application.ScreenUpdating = False
 
    Set shtDest = ActiveWorkbook.Sheets(1)
    Filename = Dir(path & "\*.xls", vbNormal)
    If Len(Filename) = 0 Then Exit Sub
    Do Until Filename = vbNullString
        If Not Filename = ThisWB Then
            Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
            Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
            Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
            CopyRng.Copy Dest
            Wkb.Close False
        End If
 
        Filename = Dir()
    Loop
 
    Range("A1").Select
 
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
    MsgBox "Done!"
End Sub

It errors out on the

Path = Getdirectory

It says "sub or function not defined"

 

[schielrn]

Try putting this in the very top of the module as well:

Option Explicit
Public strPath As String
Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type
'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
                                     Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Declare Function SHBrowseForFolder Lib "shell32.dll" _
                                   Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer
    '   Root folder = Desktop
    bInfo.pidlRoot = 0&
    '   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    '   Type of directory to return
    bInfo.ulFlags = &H1
    '   Display the dialog
    x = SHBrowseForFolder(bInfo)
    '   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function

It is missing this function to pull the directory.

Hope that helps.

 

[Slomaro2000]

Thanks for the quick reply..

I was trying some other things but didn't have any luck. It would be great if I could just call the folder in the code

C:\Documents and Settings\Test

Still having a problem

Error is on this line

Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))

Here is what I have.

Option Explicit
Public strPath As String
Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type
'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
                                     Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Declare Function SHBrowseForFolder Lib "shell32.dll" _
                                   Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long
Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer
    '   Root folder = Desktop
    bInfo.pidlRoot = 0&
    '   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    '   Type of directory to return
    bInfo.ulFlags = &H1
    '   Display the dialog
    x = SHBrowseForFolder(bInfo)
    '   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function
'Description: Combines all files in a folder to a master file.
Sub MergeFiles()
    Dim path As String, ThisWB As String, lngFilecounter As Long
    Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
    Dim Filename As String, Wkb As Workbook
    Dim CopyRng As Range, Dest As Range
    Dim RowofCopySheet As Integer
 
    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from
 
    ThisWB = ActiveWorkbook.Name
 
    path = GetDirectory("Select a folder containing Excel files you want to merge")
 
    Application.EnableEvents = False
    Application.ScreenUpdating = False
 
    Set shtDest = ActiveWorkbook.Sheets(1)
    Filename = Dir(path & "\*.xls", vbNormal)
    If Len(Filename) = 0 Then Exit Sub
    Do Until Filename = vbNullString
        If Not Filename = ThisWB Then
            Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
            Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
            Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
            CopyRng.Copy Dest
            Wkb.Close False
        End If
 
        Filename = Dir()
    Loop
 
    Range("A1").Select
 
    Application.EnableEvents = True
    Application.ScreenUpdating = True
 
    MsgBox "Done!"
End Sub

 

[schielrn]

What is the error you are receiving?

 

[Slomaro2000]

Schielrn,
I think I got it..


Thanks so much for the quick replys and offering the help..


Jayson

 

[TM74]

Are these workbooks all stored in the same folder? Are there other workbooks in this folder what will not be combined into one worksheet? If so, you could try this, but I would highly recommend making a complete copy of the folder you are trying to use this one. They both are very similiar and will ask for the directory to copy from. Just copy this into your master workbook and then choose the directory and off you should go.

Version 1 takes all 1st sheets (can have blank rows) and puts into a master workbook.

'Description: Combines all files in a folder to a master file.
Sub MergeFiles()
    Dim path As String, ThisWB As String, lngFilecounter As Long
    Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
    Dim Filename As String, Wkb As Workbook
    Dim CopyRng As Range, Dest As Range
    Dim RowofCopySheet As Integer

    RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

    ThisWB = ActiveWorkbook.Name
    
    path = GetDirectory("Select a folder containing Excel files you want to merge")

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set shtDest = ActiveWorkbook.Sheets(1)
    Filename = Dir(path & "\*.xls", vbNormal)
    If Len(Filename) = 0 Then Exit Sub
    Do Until Filename = vbNullString
        If Not Filename = ThisWB Then
            Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
            Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
            Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
            CopyRng.Copy Dest
            Wkb.Close False
        End If
        
        Filename = Dir()
    Loop

    Range("A1").Select
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
    MsgBox "Done!"
End Sub

Hope that helps. Sorry I don't know of any books to recommend as I have not read any myself. My learning has been through a visual basic class in college and this board and then trial and error. But the class was by far my most help at getting started.

Hi,

I have succesfully manged to adapt this great piece of VBA for my own purposes, but have one small problem.

The workbooks that I combine have two sheets. I am only ever interested in the first, which always has the same name. If the workbooks are saved with the first sheet diaplayed, the VBA works perfectly. If one is saved on the second sheet, the macro stops working. I have tried adapting the VBA to select the first sheet before copying and pasting, but can't get it to work.

Could someone please show me how I would be able to do this?

Thanks

 

[d_creations_sl]

Hey guys im new to Excel & VBA. I have an assignment to create a program through excel using VBA to open a particular file. The folder includes around 450 word documents & MDI files. I need to create the programe where you enter the first name of the file & then the programe will search & open the file for u. is this possible? if so could u help me on this?

Thanks & Regards,
Dinesh

 

[aelliottmo]

This worked great...EXCEPT I do not have anything in Column A on some of my spreadsheets on the last row, so it is excluding that entire row. Is there a way to change part of the code to loop through a different column until it is blank or a way around this? ex. Column C has data in all rows that are eligible to be copied.

Anything would help thanks! I'm using the code below:

'Description: Combines all files in a folder to a master file.
Sub MergeFiles()
Dim path As String, ThisWB As String, lngFilecounter As Long
Dim wbDest As Workbook, shtDest As Worksheet, ws As Worksheet
Dim Filename As String, Wkb As Workbook
Dim CopyRng As Range, Dest As Range
Dim RowofCopySheet As Integer

RowofCopySheet = 2 ' Row to start on in the sheets you are copying from

ThisWB = ActiveWorkbook.Name

path = GetDirectory("Select a folder containing Excel files you want to merge")

Application.EnableEvents = False
Application.ScreenUpdating = False

Set shtDest = ActiveWorkbook.Sheets(1)
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
Set CopyRng = Wkb.Sheets(1).Range(Cells(RowofCopySheet, 1), Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))
Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
CopyRng.Copy Dest
Wkb.Close False
End If

Filename = Dir()
Loop

Range("A1").Select

Application.EnableEvents = True
Application.ScreenUpdating = True

MsgBox "Done!"
End Sub


Thanks, aelliottmo