Add Each CSV To Current Workbook

[ryan8200]

Hi Member,

I have 2 csv files in download folder. I would like to add each csv file twice to current workbook and rename them accordingly. How should I amend the below coding ?

Sub Add_CSV()

Dim wb As Workbook
Dim FSO As Object, Folder As Object, file As Object

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Folder = FSO.GetFolder("C:\Users\Username\Downloads\")

With Application
    .ScreenUpdating = False
    .EnableEvents = False
End With

For Each file In Folder.Files
    Set wb = Workbooks.Open(Filename:=file, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    For Each sheet In wb.Sheets
        sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next
    wb.Close False
Next

Sheets(2).Name = "File1_A"
Sheets(3).Name = "File1_B"
Sheets(4).Name = "File2_A"
Sheets(5).Name = "File2_B"

EndSub

 

[Alex Blakenburg]

See if this does what you need:

Sub Add_CSV()

    Dim wb As Workbook, wbMstr As Workbook
    Dim FSO As Object, Folder As Object, file As Object
    Dim fileNo As Long
    Dim shtSuffix As Variant
   
    Set wbMstr = ThisWorkbook
   
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Folder = FSO.GetFolder("C:\Users\Username\Downloads\")
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
   
    fileNo = 0
   
    For Each file In Folder.Files
        fileNo = fileNo + 1
        Set wb = Workbooks.Open(Filename:=file, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
             
        For Each shtSuffix In Array("A", "B")
            wb.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            ActiveSheet.Name = "File" & fileNo & "_" & shtSuffix
        Next shtSuffix
        wb.Close False
    Next

End Sub

 

[ryan8200]

Hi Alex,

Thanks for your input. How should I replace "File" & fileNo if the filename is dynamic ?

 

[Alex Blakenburg]

I can see that the thread below and @Fluff's response is a progression of this and aswers your question.
Loop folder, copy csv and assign new filename