Merging workbooks where the source files don't change paths or names

Chetter

New Member
Joined
Sep 1, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon,

I did some digging and kept finding code where the user chooses files to merge then it loops through and merges them. Thought I'd throw this question out there.

I currently have code that allows the user to choose which files (workbooks) to merge into one sheet in a master workbook, which further manipulates the data. There are 8 individual source files that get merged together, minus the header in each. These files reside in the same file path each week and always have the same file names, they just get overwritten each week. For example C:\Users\MergeIL.csv, C:\Users\MergeNC.csv, C:\Users\MergeTN.csv etc.......

Since these files are always in the same place and always have the same name, how could I hard code that in the macro, to remove the step of having the user choose the files?
Here's the current code I'm using. Thank you in advance.


VBA Code:
Sub MergePivot()
Dim wsMaster As Workbook, xlsFiles As Workbook
Dim Filename As String
Dim File As Integer
Dim r As Long

Dim StartTime As Double
Dim MinutesElapsed As String
 
StartTime = Timer

'Section above combines multiple markets on one sheet

MsgBox "Select the MERGE files"

 
r = 0
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    
' below checks if theres leftover data and clears it before continuing
Worksheets("Sheet1").Activate
If Application.ActiveSheet.UsedRange.Rows.Count > 1 Then
    
    ActiveSheet.Rows("2:" & Application.ActiveSheet.UsedRange.Rows.Count).ClearContents
    
End If
    
    
' Below continues with selecting and merging files
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .Title = "Select files to process"
        .Show
        
        If .SelectedItems.Count = 0 Then Exit Sub
        
        Set wsMaster = ActiveWorkbook
        
        For File = 1 To .SelectedItems.Count
            
            Filename = .SelectedItems.Item(File)
            
            If Right(Filename, 4) = ".csv" Or Right(Filename, 5) = ".xlsx" Then
                
                Workbooks.Open Filename, 0, True
                
                Set xlsFiles = ActiveWorkbook
                
                r = wsMaster.Sheets("Sheet1").UsedRange.Rows.Count
                
                xlsFiles.ActiveSheet.Range("A3:E2000").Copy Destination:=wsMaster.Sheets("Sheet1").Range("A" & r).Offset(1, 0)
               'xlsfiles.Sheets("Sheet1").Range and so on
                xlsFiles.Close SaveChanges:=False 'close without saving
                
            End If
            
        Next File 'go to the next file and repeat the process
        
    End With
    
    Set wsMaster = Nothing
    Set xlsFiles = Nothing
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Try this (not fully tested):

VBA Code:
Sub MergePivot()
Dim wsMaster As Workbook, xlsFiles As Workbook
Dim Filename As String
Dim r As Long
Dim Getfiles as variant
r = 0
With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

'put the hardcoded path and type in an array.  Add more as necessary
Getfiles = Array("C:\Users\MergeIL.csv", "C:\Users\MergeNC.csv", "C:\Users\MergeTN.csv")

Set wsMaster = ActiveWorkbook

'loop thru all the books
For i = 1 To UBound(Getfiles)
            
Filename = Getfiles(i)

    Workbooks.Open Filename, 0, True
    
    Set xlsFiles = ActiveWorkbook
    
    r = wsMaster.Sheets("Sheet1").UsedRange.Rows.Count
    
    xlsFiles.ActiveSheet.Range("A3:E2000").Copy Destination:=wsMaster.Sheets("Sheet1").Range("A" & r).Offset(1, 0)
    'xlsfiles.Sheets("Sheet1").Range and so on
    xlsFiles.Close SaveChanges:=False 'close without saving

Next 'go to the next file and repeat the process

With Application
    .ScreenUpdating = True
    .EnableEvents = True
End With

End Sub
 
Upvote 0
Solution
Thank you. Works well. One question though, how come it skips the first file listed in the array and merges the last two?
 
Upvote 0
Because I messed up (how unusual). Arrays start from zero, not one.
The line should be:
VBA Code:
For i = 0 To UBound(Getfiles)
 
Last edited:
Upvote 0
Because I messed up (how unusual). Arrays start from zero, not one.
The line should be:
VBA Code:
For i = 0 To UBound(Getfiles)-1
Was just about to reply that I played with that line of code, changed it to 0 and it worked perfect. Thank you.
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

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