Excel VBA Code to combine multiple Excel files

T

Tommy1370

New Member
Joined
Jul 23, 2013
Messages
13
The following code opens a dialog box for the user to select files. I want it to determine which files to consolidate (using the loop).


Code: 
'The following code will combine all data into one excel workbook.
Sub CombineFiles_Step1()
'Declare Variables
Dim WorkbookDestination As Workbook
Dim WorkbookSource As Workbook
Dim WorksheetSource As Worksheet
Dim FolderLocation As String
Dim strFilename As String
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    'This line will need to be modified depending on location of source folder
    FolderLocation = "C:\Users\Location"
    
    'Set the current directory to the the folder path.
    ChDrive FolderLocation
    ChDir FolderLocation
    
    'Dialog box to determine which files to use. Use ctrl+a to select all files in folder.
    SelectedFiles = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=True)
    
    'Create a new workbook
    Set WorkbookDestination = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(FolderLocation & "\*.xls", vbNormal)
    
    'Iterate for each file in folder
    If Len(strFilename) = 0 Then Exit Sub
    
    
    Do Until strFilename = ""
        
            Set WorkbookSource = Workbooks.Open(FileName:=FolderLocation & "\" & strFilename)
            Set WorksheetSource = WorkbookSource.Worksheets(1)
            WorksheetSource.Copy After:=WorkbookDestination.Worksheets(WorkbookDestination.Worksheets.Count)
            WorkbookSource.Close False
        strFilename = Dir()
        
    Loop
    WorkbookDestination.Worksheets(1).Delete
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

The problems:

1. If I click 'Cancel' in the dialog box it still runs the loop.
2. No matter which files I select it runs the program on all of the files in the folder.

Thanks!
 
Sort by date Sort by votes

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi,

The script seems to work perfectly fine on my Windows Excel. However, when I try running the same script on a Mac Excel file it throws an error, and doesn't allow me to open the folder for selecting multiple files. Is there an alt method for selecting folder for Mac excel ?

Thanks,
 
Upvote 0
Tommy1370 said:
Andrew, thanks! Updated below (seems to be working fine).

Code: 
'The following code will combine all data into one excel workbook.

Sub CombineFiles()

'Declare Variables
Dim WorkbookDestination As Workbook
Dim WorkbookSource As Workbook
Dim WorksheetSource As Worksheet
Dim FolderLocation As String
Dim strFilename As String
Dim i As Long
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    'This line will need to be modified depending on location of source folder
    FolderLocation = "C:\Users\"
    
    'Set the current directory to the the folder path.
    ChDrive FolderLocation
    ChDir FolderLocation
    
    'Dialog box to determine which files to use. Use ctrl+a to select all files in folder.
    SelectedFiles = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=True)
    
    'Create a new workbook
    Set WorkbookDestination = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(FolderLocation & "\*.xls", vbNormal)
    
        If IsArray(SelectedFiles) Then
        
        For i = LBound(SelectedFiles) To UBound(SelectedFiles)
            Set WorkbookSource = Workbooks.Open(SelectedFiles(i))
            Set WorksheetSource = WorkbookSource.Worksheets(1)
            WorksheetSource.Copy After:=WorkbookDestination.Worksheets(WorkbookDestination.Worksheets.Count)
            WorkbookSource.Close False
            Next i
            
            End If
            
    WorkbookDestination.Worksheets(1).Delete
       
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub
Click to expand...

How would I adapt this so the user only has to select a folder location and all files within that folder would be combined into one?
 
Upvote 0
Hi Everyone. The code quoted by n3wpv above seems to only combine the first sheet of the workbook. Is there a way of either:
1 - select all worksheets in the workbooks or
2 - to specify which worksheet to be combined as I have multiple worksheets in the workbooks and would not want to combine all the other unneccessary workbooks.

Cheers :)
 
Upvote 0
cnrolly said:
Hi Everyone. The code quoted by n3wpv above seems to only combine the first sheet of the workbook. Is there a way of either:
1 - select all worksheets in the workbooks or
2 - to specify which worksheet to be combined as I have multiple worksheets in the workbooks and would not want to combine all the other unneccessary workbooks.

Cheers :)
Click to expand...

Look at post #7. That code copies all sheets. I don't know how to solve your second question.
 
Upvote 0
Tommy1370 said:
Andrew, thanks! Updated below (seems to be working fine).

Code: 
'The following code will combine all data into one excel workbook.

Sub CombineFiles()

'Declare Variables
Dim WorkbookDestination As Workbook
Dim WorkbookSource As Workbook
Dim WorksheetSource As Worksheet
Dim FolderLocation As String
Dim strFilename As String
Dim i As Long
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    'This line will need to be modified depending on location of source folder
    FolderLocation = "C:\Users\"
    
    'Set the current directory to the the folder path.
    ChDrive FolderLocation
    ChDir FolderLocation
    
    'Dialog box to determine which files to use. Use ctrl+a to select all files in folder.
    SelectedFiles = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=True)
    
    'Create a new workbook
    Set WorkbookDestination = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(FolderLocation & "\*.xls", vbNormal)
    
        If IsArray(SelectedFiles) Then
        
        For i = LBound(SelectedFiles) To UBound(SelectedFiles)
            Set WorkbookSource = Workbooks.Open(SelectedFiles(i))
            Set WorksheetSource = WorkbookSource.Worksheets(1)
            WorksheetSource.Copy After:=WorkbookDestination.Worksheets(WorkbookDestination.Worksheets.Count)
            WorkbookSource.Close False
            Next i
            
            End If
            
    WorkbookDestination.Worksheets(1).Delete
       
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub
Click to expand...

n3wpv said:
How would I adapt this so the user only has to select a folder location and all files within that folder would be combined into one?
Click to expand...

bump
 
Upvote 0
Tommy1370 said:
The following code opens a dialog box for the user to select files. I want it to determine which files to consolidate (using the loop).


Code: 
'The following code will combine all data into one excel workbook.
Sub CombineFiles_Step1()
'Declare Variables
Dim WorkbookDestination As Workbook
Dim WorkbookSource As Workbook
Dim WorksheetSource As Worksheet
Dim FolderLocation As String
Dim strFilename As String
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    'This line will need to be modified depending on location of source folder
    FolderLocation = "C:\Users\Location"
    
    'Set the current directory to the the folder path.
    ChDrive FolderLocation
    ChDir FolderLocation
    
    'Dialog box to determine which files to use. Use ctrl+a to select all files in folder.
    SelectedFiles = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=True)
    
    'Create a new workbook
    Set WorkbookDestination = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(FolderLocation & "\*.xls", vbNormal)
    
    'Iterate for each file in folder
    If Len(strFilename) = 0 Then Exit Sub
    
    
    Do Until strFilename = ""
        
            Set WorkbookSource = Workbooks.Open(FileName:=FolderLocation & "\" & strFilename)
            Set WorksheetSource = WorkbookSource.Worksheets(1)
            WorksheetSource.Copy After:=WorkbookDestination.Worksheets(WorkbookDestination.Worksheets.Count)
            WorkbookSource.Close False
        strFilename = Dir()
        
    Loop
    WorkbookDestination.Worksheets(1).Delete
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

The problems:

1. If I click 'Cancel' in the dialog box it still runs the loop.
2. No matter which files I select it runs the program on all of the files in the folder.

Thanks!
Click to expand...

Hi, this actually works. But:

1. Instead of putting the excel files to one worksheet, it's under 1 workbook with 3 worksheets.
2. Can I start copying on the row 3 for each file?

Thanks!
 
Upvote 0
Tommy1370 said:
Andrew, thanks! Updated below (seems to be working fine).

Code: 
'The following code will combine all data into one excel workbook.

Sub CombineFiles()

'Declare Variables
Dim WorkbookDestination As Workbook
Dim WorkbookSource As Workbook
Dim WorksheetSource As Worksheet
Dim FolderLocation As String
Dim strFilename As String
Dim i As Long
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    'This line will need to be modified depending on location of source folder
    FolderLocation = "C:\Users\"
    
    'Set the current directory to the the folder path.
    ChDrive FolderLocation
    ChDir FolderLocation
    
    'Dialog box to determine which files to use. Use ctrl+a to select all files in folder.
    SelectedFiles = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xls*), *.xls*", MultiSelect:=True)
    
    'Create a new workbook
    Set WorkbookDestination = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(FolderLocation & "\*.xls", vbNormal)
    
        If IsArray(SelectedFiles) Then
        
        For i = LBound(SelectedFiles) To UBound(SelectedFiles)
            Set WorkbookSource = Workbooks.Open(SelectedFiles(i))
            Set WorksheetSource = WorkbookSource.Worksheets(1)
            WorksheetSource.Copy After:=WorkbookDestination.Worksheets(WorkbookDestination.Worksheets.Count)
            WorkbookSource.Close False
            Next i
            
            End If
            
    WorkbookDestination.Worksheets(1).Delete
       
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub
Click to expand...

How would I adapt this so the user only has to select a folder location and all files within that folder would be combined into one?
 
Upvote 0
Hello,

This code is very useful but I was wondering if it would be possible to have the selected workbooks pasted into one sheet, instead of individual sheets within the new workbook?
i.e. I select my files which i want to combine, and only one sheet inside the new workbook is populated with all the data from the selected files.

Any help would be greatly appreciated,

Alfredo
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
File is allready saved but code should be stopping duplicates being saved
Replies
5
Views
263
ipbr21054
I
I
  • Question Question
err no csv found
Replies
3
Views
151
Trebor76
T
S
  • Question Question
Gathering Data From Multiple Files in Same Folder in Particular Range
3 4 5
Replies
41
Views
1K
kvsrinivasamurthy
K
R
  • Solved
Loop through Rows
Replies
4
Views
295
rollingzep
R
M
  • Question Question
Copy File Names to Destination File
Replies
2
Views
304
metalscuba
M

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top