title: how to combine multiple tables across multiple excel files and sheets in Python/VBA to form a consolidated table
body: <p>Is it possible to combine multiple tables across multiple excel files and sheets in Python/VBA to form a consolidated table? I need to create a pivot table on this consolidated table for further analysis. </p>
  
  <p>This is for analysis of monthly status reported by a team. Suppose that the team reports their status as a weekly excel file. So as the input I have 4 excel files (one excel file per week of the month), each excel file has 2 sheets ( one for each product that the team is working on) and each sheet has 2 tables ( 1 table for the development team and one table for the testing team)</p>
  
  <p>How do I parse each of these excel files and combine all the T1 tables, T2 tables, T3 tables and T4 tables?</p>
  
  <p>I have accomplished this manually currently in Excel 2016 by the following steps:
  1. Open a new excel file
  2. Load all the tables(from the 4 excel files) into this file by "Data -> Get Data -> From File -> From Workbook -> Select the file that you want(browse dialog box) -> Select all the tables (Navigator dialog box ) -> Click on Load button"
  3. Combine tables by "Data -> Get Data -> Combine Queries -> Append"</p>
  
  <p>I want to accomplish this via Python/VBA but I am not able to find any source online where I can read excel tables into memory. Pandas read_excel() method, reads the entire excel sheet into a data frame and that doesn't really solve my purpose. I couldn't find anything in the xlrd, XlsxWriter modules either.</p>
  
  <p>Please help. </p>