How to import multiple csv files into one excel file ?

[Kenor]

Hi All,

Anyone can help to confirm below VBA code for above matter?

Sub datasheet()

Application.DisplayAlerts = False
Worksheets(Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")).Delete
Application.DisplayAlerts = True




Workbooks("FG_Pack_Bundle.csv").Worksheets("FG_Pack_Bundle").Copy After:=Workbooks("FG_Database.xlsm").Worksheets("Database")
Workbooks("FG_Store_Bundle.csv").Worksheets("FG_Store_Bundle").Copy After:=Workbooks("FG_Database.xlsm").Worksheets("FG_Pack_Bundle")
Workbooks("FG_Ship_Bundle.csv").Worksheets("FG_Ship_Bundle").Copy After:=Workbooks("FG_Database.xlsm").Worksheets("FG_Store_Bundle")
Workbooks("FG_Pack_Bag.csv").Worksheets("FG_Pack_Bag").Copy After:=Workbooks("FG_Database.xlsm").Worksheets("FG_Ship_Bundle")
Workbooks("FG_Store_Bag.csv").Worksheets("FG_Store_Bag").Copy After:=Workbooks("FG_Database.xlsm").Worksheets("FG_Pack_Bag")
Workbooks("FG_Ship_Bag.csv").Worksheets("FG_Ship_Bag").Copy After:=Workbooks("FG_Database.xlsm").Worksheets("FG_Store_Bag")





Dim bk As Workbook
For Each bk In Workbooks
If Not (bk Is ThisWorkbook) Then
bk.Close SaveChanges:=False
End If
Next


Workbooks("FG_Database.xlsm").Activate
Worksheets("cover sheet").Activate

End Sub


Before this, I create another Database using same VBA code. So far, no problem.

Only this time, it shows this error.

--> Run-time error '9':
Subscript out of range

And its refer to this sentences.


Sub datasheet()

Application.DisplayAlerts = False
--> Worksheets(Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")).Delete
Application.DisplayAlerts = True

Hope anyone can help.

Thanks

 

[severynm]

Worksheets(Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")).Delete

Are these worksheets actually in your workbook when you try to delete them? If you try to delete a worksheet that is not present excel will throw the error you described.

 

[Kenor]

Are these worksheets actually in your workbook when you try to delete them? If you try to delete a worksheet that is not present excel will throw the error you described.

Actually no.

But every time I click on one Button (Open CSV file), all CSV files will come out and I will click on another button (Copy Datasheet).
Then, all CSV files will be moved into the workbook.

That is the function when I use the VBA code above on another Database before.

Suddenly an error came out when I used the same code to design a new Database.

 

[severynm]

Actually no.

But every time I click on one Button (Open CSV file), all CSV files will come out and I will click on another button (Copy Datasheet).
Then, all CSV files will be moved into the workbook.

That is the function when I use the VBA code above on another Database before.

Suddenly an error came out when I used the same code to design a new Database.

Not sure what else to say. You cannot delete a sheet that does not exist in the workbook. Consider checking if the worksheet exists before deleting.

Are those worksheets located in the same workbook that contains this code? It sounds like no?

Worksheets(Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")).Delete

Whenever you do not explicitly state which workbook you are operating on, excel assumes an implicit ActiveWorkbook.Worksheets(...). If the workbook that contains the worksheets is not currently selected (active) when the code is run the error could be triggered.

 

[Alex Blakenburg]

Recap:
Worksheets(Array("FG_Store Bundle", "FG _Ship Bundle")).Delete --> Is there something wrong here ??

As I requested in your other now closed thread, take a screenshot of your VBA project window and show us all your sheet names eg.

 

[Kenor]

Can see below :-

 

[Kenor]

I just want to import csv files from this folder into FG Database Bundle workbook.

 

[Joe4]

The issue seems to be that the line returning the error is trying to delete worksheets that don't actually exist in your workbook!
You can see this in your last screen print, which show the names of the existing sheets in your workbook.

If you have that line of code in there due to the possibility that those sheets MAY or MAY NOT exist in the worksheet (and you want to delete them if they do), then you can just elect to ignore those errors like this:

On Error Resume Next
Worksheets(Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")).Delete
On Error GoTo 0

 

[Kenor]

The issue seems to be that the line returning the error is trying to delete worksheets that don't actually exist in your workbook!
You can see this in your last screen print, which show the names of the existing sheets in your workbook.

If you have that line of code in there due to the possibility that those sheets MAY or MAY NOT exist in the worksheet (and you want to delete them if they do), then you can just elect to ignore those errors like this:

On Error Resume Next
Worksheets(Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")).Delete
On Error GoTo 0

I have amended the code as below =>

Sub datasheet()

Application.DisplayAlerts = False
Worksheets(Array("FG Store Bundle", "FG Ship Bundle")).Delete
Application.DisplayAlerts = True


Workbooks("FG Store Bundle.csv").Worksheets("FG Store Bundle").Copy After:=Workbooks("FG Database Bundle.xlsm").Worksheets("Database")
Workbooks("FG Ship Bundle.csv").Worksheets("FG Ship Bundle").Copy After:=Workbooks("FG Database Bundle.xlsm").Worksheets("FG Store Bundle")


Dim bk As Workbook
For Each bk In Workbooks
If Not (bk Is ThisWorkbook) Then
bk.Close SaveChanges:=False
End If
Next


Workbooks("FG Database Bundle.xlsm").Activate
Worksheets("cover sheet").Activate

End Sub


and still have error..

So, on which line of code do I need to change or add new code to eliminate the error ?

 

[Alex Blakenburg]

Your current code does not open the csv files, so either you have more code or you are manually opening the csv files.

As @Joe4 has pointed out, your delete line will give a Run-time error 9 subscript out of range message if any of the sheet names in your array do not exist.
He has given you one option for by-passing that error. I think you are better off looping through your array and having on error resume next inside the loop so it will delete the sheets that do exist while skipping over the ones that don't exist. Doing it in one line means it won't delete any of the sheets if even one of the sheets in the array does not exist.

Worksheets(Array("FG Store Bundle", "FG Ship Bundle")).Delete

You are still trying to delete sheets that are clearly not in your workbook.

Can see below :-

View attachment 60019

PS: I am in Australia and login off for the night.