Loop through folder and copy data into master spreadsheet

[TaskMaster]

Hi all,

I am wondering if you'd be able to help me speed up a process of mine. I currently have a folder full of .csv files. I want to combine them all into a master spreadsheet where the filename is the same name as the tab in the master spreadsheet. To do this im currently converting the .csv files to .xlsx renaming the tabs to rates and then copying them into the spreadsheet, im sure this can be done directly from the .csv files but kept falling over on the tab names as the .csv tab names were all different. Below is the vba for the final step which is copying the individual .xlsx files into the master workbook. Any help would be appreciated.

Sub RateData()
    Dim Folder As String, FilePath As String
    Dim WBMain As Workbook, WBRate As Workbook
    Dim RateWS As Worksheet
    Dim Rates() As Variant
    Dim Rate As Variant
    Dim FileError As Boolean
    Dim MissingFiles As String
    
    Set WBMain = ThisWorkbook
    
    With Application.FileDialog(4)
   
      .AllowMultiSelect = False
      If .Show <> -1 Then Exit Sub
      Folder = .SelectedItems(1)
      
    End With
    
    With CreateObject("Scripting.FileSystemObject")
        For Each RateWS In WBMain.Worksheets
            Rate = RateWS.Name
            FilePath = Folder & "\" & Rate & ".xlsx"
            FileError = Not .FileExists(FilePath)
            Select Case RateWS.Name
                Case "Summary" 'ignore list
                Case Else
                    If FileError Then
                        MissingFiles = MissingFiles & FilePath & vbCr
                    End If
                    
                    If Not FileError Then
                        Application.StatusBar = "Processing Rates: " & Rate
                        Set WBRate = Application.Workbooks.Open(Filename:=FilePath)
                        WBRate.Worksheets("Rates").Range("A1:BF2000").Copy    'or whatever
                        RateWS.Range("A2").PasteSpecial Paste:=xlPasteAll
                        Application.CutCopyMode = False
                        WBRate.Close False
                        DoEvents
                    End If
            End Select
        Next RateWS
    End With
    WBMain.Activate
    
    If MissingFiles <> "" Then
        MissingFiles = "Missing Rates workbook files:" & vbCr & vbCr & MissingFiles
        MsgBox MissingFiles, vbExclamation
    End If
End Sub

 

[Kerryx]

You could just import all the csv files into Power query and merge them

 

[TaskMaster]

You could just import all the csv files into Power query and merge them

Hi thank you for your suggestion. I’m not too familiar with power query, this is a daily task and the vba works as is but takes a long time to run due to the various steps, is there a way to make the macro work with the csv files in your view?