Importing multiple CSV files into an Excel spreadsheet

Question

I am trying to import a group of csv files from a directory. The number of files will vary, but I would like the names of each file to be the name of the sheet that is imported in.

I have the following code(can't remember where I got it exactly, but I found it on the web), and it does almost what I want, except that it creates a separate workbook instead of adding it to the one that I am running the macro from:

Sub CombineCsvFiles()
'updateby Extendoffice
    Dim xFilesToOpen As Variant
    Dim I As Integer
    Dim xWb As Workbook
    Dim xTempWb As Workbook
    Dim xDelimiter As String
    Dim xScreen As Boolean
    On Error GoTo ErrHandler
    xScreen = Application.ScreenUpdating
    Application.ScreenUpdating = False
    xDelimiter = "|"
    xFilesToOpen = Application.GetOpenFilename("Text Files (*.csv), *.csv", , "Kutools for Excel", , True)
    If TypeName(xFilesToOpen) = "Boolean" Then
        MsgBox "No files were selected", , "Kutools for Excel"
        GoTo ExitHandler
    End If
    I = 1
    Set xTempWb = Workbooks.Open(xFilesToOpen(I))
    xTempWb.Sheets(1).Copy
    Set xWb = Application.ActiveWorkbook
    xTempWb.Close False
    Do While I < UBound(xFilesToOpen)
        I = I + 1
        Set xTempWb = Workbooks.Open(xFilesToOpen(I))
        xTempWb.Sheets(1).Move , xWb.Sheets(xWb.Sheets.Count)
    Loop
ExitHandler:
    Application.ScreenUpdating = xScreen
    Set xWb = Nothing
    Set xTempWb = Nothing
    Exit Sub
ErrHandler:
    MsgBox Err.Description, , "Kutools for Excel"
    Resume ExitHandler
End Sub

I am quite new to VBA in general, so I know it is something that is quite simple. Thank you for any help that you may give in advance.

Answer 1

• Your code is close to finish. Please try.

    ' your code
    I = 0
    Set xWb = ThisWorkbook
    Do While I < UBound(xFilesToOpen)
        I = I + 1
        Set xTempWb = Workbooks.Open(xFilesToOpen(I))
        xTempWb.Sheets(1).Copy After:=xWb.Sheets(xWb.Sheets.Count)
        xTempWb.Close False
    Loop
    xWb.Save
ExitHandler:
    ' your code