SQL using Multiple Excel Workbooks as source

[mdmilner]

I'm fishing for an improv solution idea.

What I think I'd like to do is create connections that allow me to use SQL to extract data from multiple workbooks using a single statement/joins/etc. The intent is this is an interim solution - more of a proof of concept/demo - before I have the opportunity to migrate my sources into SQL Server. Ultimately the users will have a few different options to see data including excel, power bi and ssrs.

I was able to pull data in from multiple workbooks using power pivot and using multiple steps, and eventually transform it to what I needed. However, I didn't like the drill-down behavior. If I had three source connections and I double-clicked for a drill down, it would only give me a new tab showing the relevant source from the column I picked. If there's an opportunity to change that drill down behavior, this might fit my needs.

I tried using a SQL approach (against excel directly) and that works great as long as I'm only targeting worksheets within a single workbook. I demo'd the approach of using a single workbook as a staging point, but from what I've read online, it's supposed to be possible to use SQL (excel to excel) to join source data from multiple workbooks, the hint I saw made me think it's all in how you setup the connections.

I'd appreciate any suggestions. I wasn't really exploring a vba based option but if that's the right way, I'd just appreciate that hint so that I know to stop poking around with other options.

Mike

 

[ranman256]

I you use Msoft Access, you can connect the worksheets as external tables and query all you want.

 

[mdmilner]

This particular organization doesn't want to use MS Access. I'd love to go that way; if you look at my message posting history, about 95%+ is MS Access/VBA.
Right now, what I've built as a working demo looks simple and it's almost close enough to the ask that it would work as an interim solution if it had that one tiny addition.

To be honest, I feel like I'm being lazy and stubborn that there should be a non-vba solution. What I'm hoping to get is an expert opinion to shortcut my search.

 

[JEC]

Use Power Query