I have tables with data in multiple Excel workbooks, all in one folder. I am trying to use the MS Query to access the data from another workbook.  The MS Query Help leads me to believe that the MS Excel ODBC driver can connect to a data source consisting of multiple workbooks. However, when I try to set up a data source using the MS Excel ODBC driver, it allows me to select only one workbook as a source.

Can the MS Excel ODBC driver connect to a data source consisting of multiple workbooks?

Is there an alternative driver that can do that?

I see that the driver offers several versions: 12.0, 3.0, 4.0, 5.0/95, 97-2000. Out of which, 3.0 and 4.0 offer to select a directory (no individual excel files selection is allowed), and the other versions offer to select only a single workbook. If versions 3.0 or 4.0 are the answer to my question, can they work with .xlsx files created by Excel 2010?

Thanks,

Konstantin Louganski

Hi Konstantin Louganski,

With your question, I suppose you can use Power Query or you can merge data from multiple workbooks to a summary workbook first.

For using Power Query with multiple workbooks you can refer to this link:

http://www.modeloff.com/thetechnicians/combining-and-cleaning-data-from-multiple-excel-workbooks-with-power-query/

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

And The following Office versions are supported:

Microsoft Office 2010 Professional Plus with Software Assurance

Microsoft Office 2013:

Power Query Premium: All Power Query features available for: Professional Plus, Office 365 ProPlus or Excel 2013 Standalone

Power Query Public: Available for all other Office 2013 Desktop SKUs. Includes all Power Query features, except the following ones: Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects, Salesforce.

Microsoft Power Query for Excel requires Internet Explorer 9 or greater.

Microsoft Power Query for Excel is available for 32-bit (x86) and 64-bit (x64) platforms, your selection must match architecture of the installed version of Office.

If you want to get more information about Power Query, I recommend posting your issue to Power Query forum, I think that forum can offer more professional answers about Power Query. Thank you for your understanding.

https://social.technet.microsoft.com/Forums/office/en-US/home?forum=powerquery&filter=alltypes&sort=lastpostdesc

For merging data from multiple workbooks to a summary workbook, you can refer to this link:

https://msdn.microsoft.com/en-us/library/office/gg549168(v=office.14).aspx

Hope its helpful.

Regards,