Combine 100+ excel csv files with one sheet each into one sheet

jasonleewkd

New Member
Joined
Apr 3, 2012
Messages
10
Hi all,

I have 100+ excel csv files with one sheet each with 1000 records in each sheet. First row headers are the same.

How can I combine them all into one master excel sheet with about 100,000 records?

These files are all in one folder, distinctly named (11012012180108_1_jllspore, 11012012180108_2_jllspore 11012012180108_3_jllspore) on my C:.

I can rename them, albeit manually to 1.csv, 2.csv also if required.

Thanks for all the help.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
This is untested so may need some tweaking. Note the comments in the code regarding the assumptions that were made. This code should be installed in and run from the master workbook, which should reside in the same folder as the csv files.
Code:
Sub OneHundredPlusToOne()
'Assumes this macro is in the consolidation workbook
'Assumes consolidation workbook has headers in row 1, Sheet1 when macro is run
'Assumes data in csv files is in Sheet1 with headers starting in A1
Dim nR As Long, fP As String, fNam As String, cBk As Workbook, Sht _
    As Worksheet, Rws As Long
Const str1 As String = "11012012180108_"
Const str2 As String = "_jllspore"

Set cBk = ThisWorkbook
Set Sht = cBk.Sheets("Sheet1")
fP = ThisWorkbook.Path & Application.PathSeparator
For i = 1 To 100  'Change upper limit to match # of csv files in folder
    fNam = fP & str1 & i & str2 & ".csv"
    Workbooks.Open Filename:=fNam
    With ActiveWorkbook.Sheets("Sheet1")
        Rws = .UsedRange.Rows.Count
        .UsedRange.Offset(1, 0).Resize(Rws - 1).Copy
    End With
    With cBk.Sht
        nR = Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & nR).PasteSpecial Paste:=xlValues
        Application.CutCopyMode = False
    End With
    ActiveWorkbook.Close savechanges:=False
Next i
End Sub
 
Last edited:
Upvote 0
Here's my attempt:

Code:
Option Explicit
Sub ImportCSVFiles()

    Dim strFolderName As String, _
        strFileName As String
    Dim wbkThisWbk As Workbook, _
        wbkMyCSVFile As Workbook
    Dim shtMyTab As Worksheet
    Dim lngMyRow As Long, _
        lngMyCol As Long, _
        lngPasteRow As Long
    Dim strMyCol As String
    Dim blnIncludeHeader As Boolean
    Dim xlnCalcMethod As XlCalculation
    
    Set wbkThisWbk = ThisWorkbook
    Set shtMyTab = ActiveSheet 'Imports the data into the activesheet. Change to suit i.e. to import into Sheet1 use this: Set shtMyTab = Sheets("Sheet1")
    blnIncludeHeader = True
    
    With Application
        xlnCalcMethod = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .StatusBar = "Please wait while the files are imported..."
    End With
 
    'Initialise the following varibales to the first *.csv file in the designated folder
    strFolderName = "C:\CSVImportTest\" 'Folder name containing the files. Change to suit, but don't forget the trailing backslash!!
    strFileName = Dir(strFolderName & "*.csv") 'File types to import
   
    Do Until strFileName = ""
    
        Set wbkMyCSVFile = Workbooks.Open(strFolderName & strFileName)
        
        'As a comma separated value file can only have one tab, it's OK to simply use the first sheet via index 1
        With wbkMyCSVFile.Sheets(1)
            
            If WorksheetFunction.CountA(.Cells) > 0 Then
            
                lngMyRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                lngMyCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
                strMyCol = Left(Cells(1, lngMyCol).Address(True, False), Application.WorksheetFunction.Search("$", Cells(1, lngMyCol).Address(True, False)) - 1)
                
                If blnIncludeHeader = True Then
                    .Range("A1:" & strMyCol & lngMyRow).Copy
                Else
                    .Range("A2:" & strMyCol & lngMyRow).Copy
                End If
                
                Workbooks(wbkThisWbk.Name).Activate
                Sheets(shtMyTab.Name).Select
                
                If blnIncludeHeader = True Then
                    Range("A1").PasteSpecial xlPasteValues
                Else
                    lngPasteRow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
                    Range("A" & lngPasteRow).PasteSpecial xlPasteValues
                End If
            
            End If
            
        End With
      
        Application.DisplayAlerts = False
            wbkMyCSVFile.Close SaveChanges:=False
        Application.DisplayAlerts = True
      
        strFileName = Dir()
      
        blnIncludeHeader = False
    
    Loop
   
    With Application
        .Calculation = xlnCalcMethod
        .StatusBar = ""
        .ScreenUpdating = True
    End With
   
   MsgBox "Files have now been imported."

End Sub

Regards,

Robert
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top