• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Import multiple csv/xlsx/xlsm files into excel

HSA1

New Member
Hi,

I have hundreds of csv/xlsx/xlsm files that contain data which i need to have in one excel sheet. I know I can insert one at a time but i am looking for an easier way that is only by VBA.
If code would have option as well to select the folder it will be useful.
I would greatly appreciate your help,

Kind regards,
 
HSA1
Have You tried to 'Search' same kind of thread eg from this Forum?
Right - top - corner of page, You'll find Screenshot 2020-12-16 at 14.07.23.png - option.
 
vletm
Hi, I have found some posts where the codes are available according to the needs of particular person.

https://chandoo.org/forum/threads/import-text-file-kml-…-xml.37429
https://chandoo.org/forum/threads/i...les-to-excel-with-semi-colon-delimiter.37514/
https://chandoo.org/forum/threads/h...me-in-the-active-worksheet-using-macro.37896/

and I've found this code useful which works great but it paste the each file into separate sheet. All data should be paste into single sheet by sequence.
and header column shouldn't repeated because all files 1st row is same.


Code:
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
 
Back
Top