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

 

[Kenor]

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

This formula can delete the csv file but after delete, appear this error.

Actually I have used the same vba code before for 'RM Database' and it works very well.

And this time try to create for 'FG Database' with the same function. I only change the sheet name.

Below is the original code that I used for 'RM Database' :-

Sub datasheet()

Application.DisplayAlerts = False
Worksheets(Array("RM Register", "RM Picking")).Delete
Application.DisplayAlerts = True

Workbooks("RM Register.csv").Worksheets("RM Register").Copy After:=Workbooks("RM_Database.xlsm").Worksheets("Database")
Workbooks("RM Picking.csv").Worksheets("RM Picking").Copy After:=Workbooks("RM_Database.xlsm").Worksheets("RM Register")

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


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

End Sub


--> The RM Register and RM Picking csv sheets will be replaced with new ones every time I copy and transfer to the 'RM_Database' sheet.

 

[Alex Blakenburg]

Yes but you need to think about what you are doing. You are creating and deleting sheets by name.
Those names to be right (exactly to the letter).

Both the create and delete names should be the same.
All indications are that your names do not have dashes in them, so the names you are using on the create line are correct but the names on delete line are not since they have dashes in them instead of spaces.

@rlv01's code will give you an error on the create lines because he referred to the delete line in a previous post that has the names with dashes in them which means they don't exist.
This really is up to you to check that the sheet names are correct, you have all ther information in front of you.

Screenshots showing names have spaces not dashes

 

[rlv01]

@rlv01's code will give you an error on the create lines because he referred to the delete line in a previous post that has the names with dashes in them which means they don't exist.
This really is up to you to check that the sheet names are correct, you have all ther information in front of you.

Nice catch.

 

[Kenor]

Yes but you need to think about what you are doing. You are creating and deleting sheets by name.
Those names to be right (exactly to the letter).

View attachment 60082

Both the create and delete names should be the same.
All indications are that your names do not have dashes in them, so the names you are using on the create line are correct but the names on delete line are not since they have dashes in them instead of spaces.

@rlv01's code will give you an error on the create lines because he referred to the delete line in a previous post that has the names with dashes in them which means they don't exist.
This really is up to you to check that the sheet names are correct, you have all ther information in front of you.

Screenshots showing names have spaces not dashes

View attachment 60083

Yeahh..I got it!

Thanks @Alex Blakenburg

 

[Alex Blakenburg]

Thanks for letting us know. Glad we were able to help.