How to read multiple csv files, select rows and import into one Excel file using VBA?

[qwertyuiop2021]

Hi everyone, I have a project need to read multiple csv files. I have around 16 csv files, each file have around 11 rows and 4696 cols. In the 8th row is my title and 9th row is my values (same format across all csv files).
What I want to do
Run a macro and import values from all the csv files into one sheet (the first row will contain the titles, the next rows will contain the values of each csv file.).
p/s: I have never used VBA before. Due to my work need me to use this.

 

[Joe4]

Welcome to the Board!

We need a bit more details in order to help you.

Are all these CSV files stored in the same folder? If not, what exactly is the structure?
Are they the only CSV files in those folders (if there are any CSV files you do NOT want to include, how can we differentiate between the ones we want and the ones that we don't)?

Are you copying to an existing Excel file/template, or should the macro be creating this new file?
If the macro is creating the file, where should it create it and how should it name it?

 

[jkpieterse]

Have you tried to use Data, Get Data, From File, From Folder?

 

[qwertyuiop2021]

Welcome to the Board!

We need a bit more details in order to help you.

Are all these CSV files stored in the same folder? If not, what exactly is the structure?
Are they the only CSV files in those folders (if there are any CSV files you do NOT want to include, how can we differentiate between the ones we want and the ones that we don't)?

Are you copying to an existing Excel file/template, or should the macro be creating this new file?
If the macro is creating the file, where should it create it and how should it name it?

All CSV files are in the same folder with the same format as the attached image. And located in the path:
Path: D:\CT_Test\TASK\TestLog
I need them to be merged and only get 1 single header taken from row 9 as shown in the image. And the remaining rows will be row 10 of each input file.
I already know how to save files and name them. The way I name it would be:
Path_result & "log" & "<datetime>" & ".csv"
and the output file will have the format as csv file saved in the path:
Path_result: D:\CT_Test\TASK\TestResult

 

[Joe4]

OK, I came up with some code that I think will do what you want.
You will just need to update the folder references I used in the code to match your folders (note the two sections where it says ("***UPDATE TO MATCH YOUR STRUCTURE***").

Sub MyCombineMacro()

    Dim myDataFolder As String
    Dim myMacroFile As Workbook
    Dim myNewFile As Workbook
    Dim myDataFile As Workbook
    Dim myFileName As String
    Dim counter As Long
    Dim myNewFileName As String
    
    Application.ScreenUpdating = False
    
'   Designate data folder (***UPDATE TO MATCH YOUR STRUCTURE***)
    myDataFolder = "C:\Temp\Test\"
    
'   Capture current macro workbook object
    Set myMacroFile = ActiveWorkbook
    
'   Create new workbook
    Workbooks.Add
'   Capture it in workbook object
    Set myNewFile = ActiveWorkbook
    
'   Loop through all files in folder
    myFileName = Dir(myDataFolder & "*.csv")
    Do While Len(myFileName) > 0
'       Increment counter
        counter = counter + 1
'       Open file
        Set myDataFile = Workbooks.Open(fileName:=myDataFolder & myFileName)
'       Check to see if this is the first file, so we can copy title row
        If counter = 1 Then
            Range(Cells(9, 1), Cells(10, 4696)).Copy
            myNewFile.Activate
            Range("A1").Select
        Else
'           Copy data in row 10
            Range(Cells(10, 1), Cells(10, 4696)).Copy
            myNewFile.Activate
            Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
        End If
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Close data file
        myDataFile.Close
'       Set file name for next available file
        myFileName = Dir
    Loop

'   Build new file name (***UPDATE TO MATCH YOUR STRUCTURE***)
    myNewFileName = "C:\Temp\TestResult\log" & Format(Now, "yyyymmddhhmmss") & ".csv"

'   Save final file
    myNewFile.Activate
    ActiveWorkbook.SaveAs fileName:=myNewFileName, FileFormat:=xlCSV, CreateBackup:=False
    myNewFile.Close

    MsgBox "Macro complete!"
    
    Application.ScreenUpdating = True
    
End Sub

 

[qwertyuiop2021]

OK, I came up with some code that I think will do what you want.
You will just need to update the folder references I used in the code to match your folders (note the two sections where it says ("***UPDATE TO MATCH YOUR STRUCTURE***").

Sub MyCombineMacro()

    Dim myDataFolder As String
    Dim myMacroFile As Workbook
    Dim myNewFile As Workbook
    Dim myDataFile As Workbook
    Dim myFileName As String
    Dim counter As Long
    Dim myNewFileName As String
   
    Application.ScreenUpdating = False
   
'   Designate data folder (***UPDATE TO MATCH YOUR STRUCTURE***)
    myDataFolder = "C:\Temp\Test\"
   
'   Capture current macro workbook object
    Set myMacroFile = ActiveWorkbook
   
'   Create new workbook
    Workbooks.Add
'   Capture it in workbook object
    Set myNewFile = ActiveWorkbook
   
'   Loop through all files in folder
    myFileName = Dir(myDataFolder & "*.csv")
    Do While Len(myFileName) > 0
'       Increment counter
        counter = counter + 1
'       Open file
        Set myDataFile = Workbooks.Open(fileName:=myDataFolder & myFileName)
'       Check to see if this is the first file, so we can copy title row
        If counter = 1 Then
            Range(Cells(9, 1), Cells(10, 4696)).Copy
            myNewFile.Activate
            Range("A1").Select
        Else
'           Copy data in row 10
            Range(Cells(10, 1), Cells(10, 4696)).Copy
            myNewFile.Activate
            Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
        End If
        ActiveSheet.Paste
        Application.CutCopyMode = False
'       Close data file
        myDataFile.Close
'       Set file name for next available file
        myFileName = Dir
    Loop

'   Build new file name (***UPDATE TO MATCH YOUR STRUCTURE***)
    myNewFileName = "C:\Temp\TestResult\log" & Format(Now, "yyyymmddhhmmss") & ".csv"

'   Save final file
    myNewFile.Activate
    ActiveWorkbook.SaveAs fileName:=myNewFileName, FileFormat:=xlCSV, CreateBackup:=False
    myNewFile.Close

    MsgBox "Macro complete!"
   
    Application.ScreenUpdating = True
   
End Sub

It's worked. Thank you so much!

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[qwertyuiop2021]

You are welcome.
Glad I was able to help!

Sorry to bother you but what if I want to filter out the columns I select by the column header, how do I do that?
Can you help me? I tried with for loop and autofilter but no success.