2

I'm trying to run SQL queries in Excel.

The only option that allows me to do so is inserting data from an external source with Microsoft Query, selecting Excel Files as the data source and pointing at the current workbook.

Stupid as it is, it actually works, however it burns the path of the file into the connection that it defines in the workbook, meaning I can't move or redistribute the file.

The connection will have the following definition:

connection string:

DSN=Excel Files;DBQ=C:\Temp\Alma.xlsx;DefaultDir=C:\Temp;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

query:

select e.bizszam, e.bevsor, e.afa, u.afa
from this.EredetiAdat e
inner join this.UjAdat u on e.bizszam = u.bizszam and e.bevsor = u.bevsor and e.afa <> u.afa
where e.afa NOT IN (select u2.afa from `C:\Temp\Alma.xlsx`.UjAdat u2 where u2.bizszam = u.bizszam and u2.bevsor = u.bevsor)
OR u.afa NOT IN (select e2.afa from `C:\Temp\Alma.xlsx`.EredetiAdat e2 where e2.bizszam = e.bizszam and e2.bevsor = e.bevsor)

As you can see there is a path (C:\Temp\Alma.xlsx) in both of the above. I want to get rid of that. How can I make the connection point at the workbook itself, at the running instance, regardless of wherever it is??

If there is any other way (native Excel, no plug-ins!) to run SQL queries on the tables of a workbook I'd also be happy with that. I've tried using MS Power Query but as far as I can tell it's completely useless.

Tyrel
  • 81
  • 5
  • P.S.: Yeah I know that query is stupid, believe me, the source data is as well... – Tyrel Apr 28 '16 at 16:04
  • One option might be to put the source data in a separate workbook and put it in an accessible location. Otherwise, you'll need to write some VBA to update the connection when the file is opened. – Doug Glancy Apr 28 '16 at 18:34
  • Sadly that wouldn't work, they just want to toss this Excel file around and use ad-hoc. Can't make a centralized datasource. However the VBA idea sounds feasible, though I have no idea where to start. :/ – Tyrel Apr 28 '16 at 21:20

0 Answers0