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

 

[Joe4]

I think you are getting confused as to what that line of code is doing (I am guessing that maybe this is code that you did NOT write yourself).
That is deleting existing sheets in your workbook, it is NOT importing anything at that point.
The intention was probably to delete those sheets before trying to import any file that has the same name.

If you look at your VB Explorer screen print that you posted, you can see that it has sheets named:
- FG Store
- FG Ship

Neither of those match what is in your array.
I showed you how to ignore those errors, by surrounding that line of code with the two lines to temporarily ignore those errors.

 

[Kenor]

Sorry, I'm still confused about what I should do.

Let me explain a little how I do to import data from csv files.

I will click on Open CSV File ...then all the csv files will open. This part works well.

After that, I will click on Copy Data Sheet ...this part becomes an error as I mentioned in the posts above.

The FG STORE sheet & the FG SHIP sheet are for me to use as a place to collect all the data from the csv file after all the csv files have been transferred into the FG Database Bundle workbook.

While the Database sheet is a summary of all the IN data that is in the FG STORE sheet and the OUT data that is in the FG SHIP sheet.

 

[Joe4]

Slow down, and go back and carefully read what Alex and I wrote.
We very clearly stated what the issue is - your code is trying to delete sheets that do not exist in that workbook at that time.
Do you understand that?

Did you try replacing your line of VBA code that does the deletion with the three lines I posted, and see if that resolves your problem?

 

[Kenor]

Slow down, and go back and carefully read what Alex and I wrote.
We very clearly stated what the issue is - your code is trying to delete sheets that do not exist in that workbook at that time.
Do you understand that?

Did you try replacing your line of VBA code that does the deletion with the three lines I posted, and see if that resolves your problem?

After I tried it :-

 

[Joe4]

After I tried it :-

View attachment 60031

That does not look anything like the code I posted in post 8.

You originally had this line:

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

I am telling you to replace that line with 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]

That does not look anything like the code I posted in post 8.

You originally had this line:

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

I am telling you to replace that line with 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

An error has disappeared. I can import all the csv files.

But when I try to import for 2nd time.

The number of csv sheets increases in the FG Database Bundle workbook instead of deleting the previous one and replacing it with a new csv sheet.

 

[Joe4]

OK, then I think we will need to do what Alex recommend, and loop through each sheet individually, so it won't cancel out and not delete anything is it cannot find any one certain sheet.
So that loop to delete the sheets would look something like this:

    Dim arr()
    Dim ws

'   Set array of sheets to delete
    arr = Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")

'   Loop through array and delete sheets
    On Error Resume Next
    For ws = LBound(arr) To UBound(arr)
        Worksheets(arr(ws)).Delete
    Next ws
    On Error GoTo 0

You would just place that in place of the delete line you had originally.

 

[Kenor]

OK, then I think we will need to do what Alex recommend, and loop through each sheet individually, so it won't cancel out and not delete anything is it cannot find any one certain sheet.
So that loop to delete the sheets would look something like this:

    Dim arr()
    Dim ws

'   Set array of sheets to delete
    arr = Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")

'   Loop through array and delete sheets
    On Error Resume Next
    For ws = LBound(arr) To UBound(arr)
        Worksheets(arr(ws)).Delete
    Next ws
    On Error GoTo 0

You would just place that in place of the delete line you had originally.

Sorry, it still same.

Sub datasheet()

Application.DisplayAlerts = False
Dim arr()
Dim ws

' Set array of sheets to delete
arr = Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")

' Loop through array and delete sheets
On Error Resume Next
For ws = LBound(arr) To UBound(arr)
Worksheets(arr(ws)).Delete
Next ws
On Error GoTo 0
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

 

[Joe4]

It is up to you to list in the array whatever sheet names you want deleted.
Currently, it is the following (gotten from your original code):

    arr = Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")

Note that it does NOT include "FG STORE" or "FG SHIP".

If you want those sheets deleted, you will need to add them to that array (and remove any ones that you do not need in there, if any).

That is why it is important to not just use the code, but also have some sort of understanding what each step does, so you can maintain the code as situations change.
I typically add documentation to my code to explain what each step is doing (i.e. "' Set array of sheets to delete").

 

[rlv01]

What if instead of trying to delete the import worksheets by naming them, you define the worksheets you want to keep and delete anything else?
Example:

Sub datasheet()
    
    Dim WS As Worksheet
    Dim WB As Workbook
    Dim WName As Variant
    
    Set WB = ThisWorkbook 'presumably that is 'FG_Database.xlsm'
    
    'Application.DisplayAlerts = False
    For Each WS In WB.Worksheets
        Select Case WS.Name
            Case "Cover Sheet", "DataBase", "FG STORE", "FG SHIP" 'don't delete
            Case Else 'delete everything else
                WS.Delete
        End Select
    Next WS
    'Application.DisplayAlerts = True
    
    For Each WName In Array("FG_Pack_Bundle", "FG_Store_Bundle", "FG_Ship_Bundle", "FG_Pack_Bag", "FG_Store_Bag", "FG_Ship_Bag")
        WB.Workbooks(WName & ".csv").Worksheets(WName).Copy After:=WB.Worksheets(WB.Worksheets.Count)
    Next WName
    
    For Each WB In Workbooks
        If Not (WB Is ThisWorkbook) Then
            WB.Close SaveChanges:=False
        End If
    Next
    
    ThisWorkbook.Activate
    Worksheets("Cover Sheet").Activate
End Sub