Using MS Query in Excel to query itself (and not an external source)

Question

I can connect the excel file to myself as a data source for MS Query to work. But as soon as I move the file around the request, it tries to find it from its previous location on the network and does not work.
I just want him to try to ask himself.
I tried to remove the directory path from the connection string in the data source, but it just made a mistake.

Is it possible? Or is there a better way?

Answer 1

Can you use VBA? If you do, you can put code in ThisWorkbook to update the query string:

Sub UpdateQuery() 'This is just an example. Query must be changed accordingly ThisWorkbook.Connections(1).ODBCConnection = "SELECT `Sheet1$`.a, `Sheet1$`.b FROM `" & ThisWorkbook.FullName & "`.`Sheet1$` `Sheet1$`" End Sub Private Sub Workbook_AfterSave(ByVal Success As Boolean) If Success Then UpdateQuery End Sub Private Sub Workbook_Open() UpdateQuery End Sub