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

[Chetter]

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.

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

 

[portews]

Try this (not fully tested):

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

 

[Chetter]

Thank you. Works well. One question though, how come it skips the first file listed in the array and merges the last two?

 

[portews]

Because I messed up (how unusual). Arrays start from zero, not one.
The line should be:

For i = 0 To UBound(Getfiles)

 

[Chetter]

Because I messed up (how unusual). Arrays start from zero, not one.
The line should be:

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.