Merging .csv files into multiple worksheets within one workbook

Question

I am trying to pull in a selected set of .csv files, then add each file to a workbook in its own worksheet to merge the data all into one excel workbook. I am having trouble naming the sheets the name of the file as each sheet is being pulled in. I have searched a lot and have various commented ways I have tried that did not work. Here is what I have so far:

Sub R_AnalysisMerger()
Dim WSA As Object
Dim bookList As Workbook
Dim SelectedFiles() As Variant
Dim NFile As Long
Dim FileName As String

Application.ScreenUpdating = False

'change folder path of excel files here
SelectedFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.csv*), *.csv*", MultiSelect:=True)

For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)


    FileName = SelectedFiles(NFile)
    Set bookList = Workbooks.Open(FileName)
    Set WSA = ThisWorkbook.Worksheets.Add
    'ActiveSheet.Name = Left(FileName, 31)
    'ActiveWorksheet.Name.Add Name:= FileName
    'ActiveWorkbook.Name Name:=FileName
    'ThisWorkbook.Sheets.Name.Add (FileName)

    'Change " A1" to the starting point for each file.
    'Also change "A" column on "A10000" to the same column as start point
    Range("A1:IV" & Range("A100000").End(xlUp).Row).Copy
    ThisWorkbook.Worksheets(1).Activate

    'Column
    Range("A100000").End(xlUp).Offset(0, 0).PasteSpecial
    Application.CutCopyMode = False
    Cells.EntireColumn.AutoFit
    bookList.Close
    'ActiveWorkbook.Close

Next
Sheets("Sheet1").Select
Range("A1").Select

End Sub

Simply rename the new worksheet object : WSA.Name = Left(FileName, 31) — Parfait

Dy.Lee Dy.Lee · Accepted Answer · 2017-07-15T04:40:37

Using variant is easy.

Sub R_AnalysisMerger()
    Dim WSA As Worksheet
    Dim bookList As Workbook
    Dim SelectedFiles() As Variant
    Dim NFile As Long
    Dim FileName As String
    Dim Ws As Worksheet, vDB As Variant, rngT As Range

    Application.ScreenUpdating = False


    Set Ws = ThisWorkbook.Sheets(1)
    Ws.UsedRange.Clear
    'change folder path of excel files here
    SelectedFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.csv*), *.csv*", MultiSelect:=True)


    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        FileName = SelectedFiles(NFile)
        Set bookList = Workbooks.Open(FileName, Format:=2)
        Set WSA = bookList.Sheets(1)
        With WSA
            vDB = .UsedRange
            Set rngT = Ws.Range("a" & Rows.Count).End(xlUp)(2)
            If rngT.Row = 2 Then Set rngT = Ws.Range("a1")
            rngT.Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB

            bookList.Close (0)
        End With
    Next
    Application.ScreenUpdating = True
    Ws.Range("A1").Select

End Sub

The Other is

Sub R_AnalysisMerger2()
    Dim WSA As Worksheet
    Dim bookList As Workbook
    Dim SelectedFiles As Variant
    Dim NFile As Long
    Dim FileName As String
    Dim Ws As Worksheet, vDB As Variant, rngT As Range
    Dim vFn, myFn As String

    Application.ScreenUpdating = False

    SelectedFiles = Application.GetOpenFilename(filefilter:="Excel Files (*.csv*), *.csv*", MultiSelect:=True)
    If IsEmpty(SelectedFilesL) Then Exit Sub

    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        FileName = SelectedFiles(NFile)
        vFn = Split(FileName, "\")
        myFn = vFn(UBound(vFn))
        myFn = Replace(myFn, ".csv", "")
        Set bookList = Workbooks.Open(FileName, Format:=2)
        Set WSA = bookList.Sheets(1)
        vDB = WSA.UsedRange
        bookList.Close (0)
        Set Ws = Sheets.Add(after:=Sheets(Sheets.Count))
        ActiveSheet.Name = myFn
        Ws.Range("a1").Resize(UBound(vDB, 1), UBound(vDB, 2)) = vDB
    Next
    Application.ScreenUpdating = True


End Sub

Merging .csv files into multiple worksheets within one workbook

1 Answers