Add Sheets

[ryan8200]

I try to loop all csv files in folder and add them to current workbook but hit sheet not defined error. After I declare sheet, it hits run time error 1004. Method of open of object Workbooks failed. Anyone can help me on this error ?

Sub AddCSV()
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\My\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

End Sub

 

[LazyBug]

Hello!

Does your macro in Excel 97-2004 Workbook (.xls)? If yes, try to save it as Macro-enabled Workbook (.xlsm), close and open again.
A few minor changes:

Sub AddCSV()
Dim wb As Workbook, sheet As Worksheet
Dim FSO As Object, Folder As Object, file As Object
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
  
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Folder = FSO.GetFolder("C:\Users\My\Downloads\")
  
    For Each file In Folder.Files
        If Right(file.Name, 4) = ".csv" Then
            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 sheet
            wb.Close False
        End If
    Next file
End Sub

 

[Alex Blakenburg]

@ryan8200 - if you are really only loading csv / text files you can do away with the For Each sheet In wb.Sheets loop.
By definition those files can only have 1 sheet.

 

[ryan8200]

@ryan8200 - if you are really only loading csv / text files you can do away with the For Each sheet In wb.Sheets loop.
By definition those files can only have 1 sheet.

Hi @Alex Blakenburg , I have checked and confirm that each file only has 1 sheet. However, the filename for one of csv is in chinese character. Could this be the possible root cause ?

 

[Alex Blakenburg]

Can I clarify, are you saying that you have tried @LazyBug's code and you are still getting an error ?
Can you show us an image of the error dialogue box.
An image of the line that is highlighted when you click on Debug in the error dialogue box
If you hover over the word "file" when it errors out what file name do you see ? If it is too hard to see it there, then in the immediate window (Ctrl+G if its not visible) type
? file it will show the file name in the immediate box. Is that the file with the chinese characters if not is there anything unusual about the file name you see ?