excel - How can I open multiple files to copy data into a master workbook in new tab?

Question

I have a master Excel file that needs to inherit data from .csv files.

When I run the VBA, it will pop up the file explorer, let me select multiple files and loop over them and create new sheets.

When I try to copy the data into the sheet that it created, it gives me a type mismatch error.

Sub OpenLMSFiles()

Dim fd As FileDialog
Dim FileChosen As Integer
Dim FileName As String
Dim tempWB As Workbook
Dim i As Integer

Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.InitialFileName = "Libraries\Documents"
fd.InitialView = msoFileDialogViewList
fd.AllowMultiSelect = True

FileChosen = fd.Show
If FileChosen = -1 Then
    For i = 1 To fd.SelectedItems.Count
        Set tempWB = Workbooks.Open(fd.SelectedItems(i))
        Call ReadDataFromSourceFile(tempWB)
    Next i
End If
End Sub


Private Sub ReadDataFromSourceFile(src As Workbook)

Application.ScreenUpdating = False

ThisWorkbook.Sheets.Add

Workbooks(src).Worksheets(src.ActiveSheet).Range("A1:Z500").Copy _
Workbooks(ThisWorkbook).Worksheets(ThisWorkbook.ActiveSheet).Range("A1:Z500")

End Sub

标签： excelvba

Answer 1

The cause of the error is the way you are referencing workbooks and worksheets, which are collections that take index arguments (integer or string). For example you can reference a workbook as Workbooks(1) (bad idea) or Workbooks("FileName.xlsx") (better). Similarly use Sheets(1) or Sheets("SheetName").

src is a Workbook -> simply use src.Sheets(). Because csv files have only 1 worksheet it is safe to use src.Worksheets(1) (Sheets and Worksheets are equivalent).

Anyway, here is a working code. I rearranged the code as I think ReadDataFromSourceFile should encapsulate opening and closing the csv file as well as reading data from it (only an opinion)

Sub ImportLMSFiles()

    Dim fd As FileDialog
    Dim FileChosen As Long
    Dim FileName As String
    Dim i As Long

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    fd.InitialFileName = "Libraries\Documents"
    fd.InitialView = msoFileDialogViewList
    fd.AllowMultiSelect = True

    FileChosen = fd.Show
    If FileChosen = -1 Then
        For i = 1 To fd.SelectedItems.Count
            Call ReadDataFromSourceFile(fd.SelectedItems(i))
        Next i
    End If
    
End Sub


Private Sub ReadDataFromSourceFile(sSrcFilename As String)
    ' Validate the name
    If Right(sSrcFilename, 4) <> ".csv" Then Exit Sub
    
    Application.ScreenUpdating = False
    
    Dim shtDest As Worksheet: Set shtDest = ThisWorkbook.Sheets.Add
    Dim wbSrc As Workbook: Set wbSrc = Workbooks.Open(sSrcFilename)

    ' csv files have only 1 sheet
    ' UsedRange is exactly what it sounds like
    With wbSrc.Sheets(1)
        .UsedRange.Copy shtDest.Range(.UsedRange.Address)

        ' if you want to rename the new sheet
        ' Bug: another sheet might have the same name -> u need check for that
        ' Here I'm just ducking it: name not changed
        On Error Resume Next
        shtDest.Name = .Name
        On Error GoTo 0
    End With

    wbSrc.Close SaveChanges:=False
    Application.ScreenUpdating = True

End Sub