Multiple Excel tables with multiple same name sheets to one Excel table

WW-6729 371 Reputation points
2021-09-14T22:20:14.707+00:00

Hello! Does anybody knows how to append multiple excel files with multiple sheets (every excel file has the same sheet name) to single excel file? The best example is esx reports in excel from RV tools. I have multiple reports for multiple esx servers that I wish to append to one excel file so I can have all the servers in the same file appended one below other. Same goes for the information that sheets contains, append one below other. Thank you!

I've managed to see some examples that include multiple excel files but with one sheet in every excel file. I need guidance how to do it with multiple excel files that contain multiple sheets (with the same name in every excel file).

Best regards!

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,532 questions
Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,251 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,649 questions
0 comments
Accepted answer
  1. Viki Ji_MSFT 4,421 Reputation points
    2021-09-15T05:53:13.687+00:00

    Hi @WW-6729 ,
    Welcome to Q&A forum!

    Which version of Office are you using? May be you could try using the Power Query.

    The following is my test using Excel 365 on Win 10. The three files in the Test folder contain two worksheets with the same name, namely Data1 and Data2.

    • Please put all the Excel files that need to be merged into the same folder.
    • And rename the files according to the order of the file contents. This is because Power Query will sort the content according to the
      order of the files in the folder.

    132206-image.png 132245-image.png

    132198-1.gif

    Reference: Import data from a folder with multiple files (Power Query)

    Hope the information could help you. Any misunderstandings, please let me know.

    Update:
    After loading the files into Power Query, please refer to the following steps:
    132517-test2.gif

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. WW-6729 371 Reputation points
    2021-09-15T14:00:33.81+00:00

    VikiJi thank you!

    I have 365 ProPlus version.

    I want something else, I don't know is it possible. I'll try to explain using your pictures above. I want to append information from sheet Data1 in excel file Test2.xlsx and Data1 from excel file Test3.xlsx to Data1 in excel file Test1.xlsx. And also do that for Data2 sheets and so on...Data3, Data4...As per your excel files it would look like this132290-xlsx3.jpg