SQL Select From a Sheet in an External Excel Workbook

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)
Loading...
Go back to All Questions Login or Register
72 views
0

Hello;

I create some pretty complex Excel VBA Enterprise applications and I’ve been using your SQL Addin now for almost two years and it’s saved me TONS of development and processing time. Up until now I’ve been able to work with either data within the current workbook or data imported from SQL Server at will. Now, however, I’m being sent other workbooks that I need to extract data from and I’m having trouble with the SQL syntax needed to make it work.

I’m aware that I should simply be able to open the external file, copy the needed sheet(s) or even just the data into my workbook and go from there. The problem is these sheets are literally RIDDLED with Links to files deep in the customers corporate network that I have no access to. Many of the formulas contain references to these external links that go unresolved.

I can read the answer in each cell that was there the last time they saved the file but it takes forever to open it as it tries to resolve a blue-million links. Then to add insult to injury those pesky Named Ranges and unresolved Links get into my workbook during the copy and I have an unholy mess that takes way too much time to fix. My solution is to use SQL to extract the data from the sheets I need into my workbook and go from there insulating me from the mess.

I’m following the example I found on the SQL Addin page to the letter:

Example from site: C:Data.xlsx.Sheet1$
Real Sample: “SELECT * FROM C:\MBSS\Projects\Kaiser\MYP Scheduler\WLAN MYP&FBI 10-20-17 MJC.xlsx.Scheduler$

I get “Error in FROM clause” when I execute.

Other tidbits:

O/S: Win7 Ultimate
Excel: Office Pro+ 2013
External Workbook: The workbook was selected using GetOpenFilename so I know the file is there because I open it within my VBA to get the sheet names.

I’m very good with SQL across a number of platforms but it wouldn’t surprise me to learn I’ve just missed something stupid.

Questions:
– Do I need to close this the external file before executing my SQL statement?
– Am I being too literal with the example and the HTML-Like tags (code and /code) actually mean something else?
– When I do (with your help) get this working can I use it to SELECT from two or more external sheets (from the same workbook) with a JOIN?

Thank you in advance for all your help…

Mike

0

Hi Mike
did you try parenthesis with the file path?

SELECT * FROM C:\MBSS\Projects\Kaiser\MYP Scheduler\WLAN MYP&FBI 10-20-17 MJC.xlsx.[Scheduler$]

If it doesn’t work try different parenthesis (see the ? button on the window of the Addin).

You other Questions:
– Do I need to close this the external file before executing my SQL statement?
Maybe :). Best to save and close at least to see it works first time.

– When I do (with your help) get this working can I use it to SELECT from two or more external sheets (from the same workbook) with a JOIN?
Yes. Simply specify the path to the other worksheet using same file path.

Simply the best place to learn VBA!