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.