none
VBA - add multiple Excel in one Excel file RRS feed

  • Question

  • Hi,

    I have 3 Excel files in a folder, each file contains 1 sheet. At some point, there will be over 100 files in this folder.

    What I want is that the data in each file is added to one main Excel file, where every file gets its own sheet (so 3 sheets in this case + sheet1 where I will run the code).

    The code that I used for that is:

    Option Explicit
    Const path As String = "FOLDER NAME"
    Sub GetSheets()
    Dim FileName As String
    Dim wb As Workbook
    Dim sheet As Worksheet
    
    FileName = Dir(path & "*.xls*")
    Do While FileName <> ""
        Set wb = Workbooks.Open(FileName:=path & FileName, ReadOnly:=True)
        For Each sheet In wb.Sheets
            sheet.Copy After:=ThisWorkbook.Sheets(1)
        Next sheet
        wb.Close
        FileName = Dir()
    Loop
    End Sub
    

    However, when I run this code on the first added sheet (sheet2) it will add two empty sheets followed by one filled sheet containing the data of one Excel file in that folder. Since I have three Excel files, the final result will be that I end up with 9 added sheets (3 are filled and 6 are empy). So:

    sheet1 = start-sheet where I run the code (must stay)

    sheet2 = Empty

    sheet3 = Empty

    sheet4 = Filled (first file)

    sheet5 = Empty

    sheet6 = Empty

    sheet7 = Filled (second file)

    sheet8 = Empty

    sheet9 = Empty

    sheet10 = Filled (third file)

    What I want is:

    sheet1 = start-sheet where I run the code (must stay)

    sheet2 = Filled (first file)

    sheet3 = Filled (second file)

    sheet4 = Filled (third file)

    Does someone know how to solve this? I'm using Excel 2013

    Thanks in advance.

    Ganesh

    Friday, March 23, 2018 8:34 AM

Answers

  • Hi ganeshgebhard,

    You had mentioned that,"when I run this code on the first added sheet (sheet2) it will add two empty sheets followed by one filled sheet containing the data of one Excel file in that folder. Since I have three Excel files, the final result will be that I end up with 9 added sheets (3 are filled and 6 are empy)."

    I try to make a test with your code.

    I create 3 Excel file with 1 sheet in each Workbook.

    Then I try to run your code.

    I find that your code is working correctly on my side and it creates only 3 Sheets.

    Below is my testing result.

    You can see 3 Excel files in Folder.

    Call your code button click.

    You can see that , You are looping through all the sheets in your workbook.

    So it is possible that your workbook contains any extra hidden sheets that get copied when you run the code.

    It is possible that you make copies of that same Excel files in your folder. So all your Excel files contains that sheets.

    So as a result, You are getting 6 empty sheets.

    I suggest you to create a new Excel file with only one sheet and then you can make a several copies of it for testing purpose.

    Then try to make a test and let us know about the result.

    If issue persist, We will try to provide you further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ganeshgebhard Monday, March 26, 2018 8:32 AM
    Monday, March 26, 2018 5:21 AM
    Moderator

All replies

  • Hi Ganesh,

    I remember that I provided an answer to the similar (or the same) question as yours.

    Please refer to the following thread:
    macro Copying worksheets from multiple workbooks into current workbook
    https://social.msdn.microsoft.com/Forums/en-US/a54e595b-f0fe-4510-94c2-6584961a25ac/macro-copying-worksheets-from-multiple-workbooks-into-current-workbook?forum=isvvba

    I've shared my sample file via OneDrive as "Jean_7_CollectSheets.zip".
    https://1drv.ms/u/s!AhzOJeY5F3-fj9IawAqlWHCmxjE4ug

    Please download, unzip, and check if it can satisfy your requirements.

    Regards,

    Ashidacchi >> http://hokusosha.com/

    Friday, March 23, 2018 8:48 AM
  • Hi Ashidacchi,

    Thanks for the resposne.

    However, this does not work for me, I still have the same problem.

    Regards, Ganesh

    Friday, March 23, 2018 11:03 AM
  • Hi,

    It would be the best way, I suppose, that you share your File (maybe, all Files in your Folder) via cloud storage (e.g. OneDrive, Dropbox, etc.). It will help me who want to help you.

    Remember to modify/edit your vital/personal data before sharing.

    Regards,

    Ashidacchi &gt;&gt; http://hokusosha.com/

    Friday, March 23, 2018 11:37 AM
  • Hi ganeshgebhard,

    You had mentioned that,"when I run this code on the first added sheet (sheet2) it will add two empty sheets followed by one filled sheet containing the data of one Excel file in that folder. Since I have three Excel files, the final result will be that I end up with 9 added sheets (3 are filled and 6 are empy)."

    I try to make a test with your code.

    I create 3 Excel file with 1 sheet in each Workbook.

    Then I try to run your code.

    I find that your code is working correctly on my side and it creates only 3 Sheets.

    Below is my testing result.

    You can see 3 Excel files in Folder.

    Call your code button click.

    You can see that , You are looping through all the sheets in your workbook.

    So it is possible that your workbook contains any extra hidden sheets that get copied when you run the code.

    It is possible that you make copies of that same Excel files in your folder. So all your Excel files contains that sheets.

    So as a result, You are getting 6 empty sheets.

    I suggest you to create a new Excel file with only one sheet and then you can make a several copies of it for testing purpose.

    Then try to make a test and let us know about the result.

    If issue persist, We will try to provide you further suggestions to solve the issue.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by ganeshgebhard Monday, March 26, 2018 8:32 AM
    Monday, March 26, 2018 5:21 AM
    Moderator