4

I can connect the excel file to itself as a datasource for MS Query to work. But as soon as I move the file around the query tries to find it from its previous location on the network and fails.
I just want it to try and query itself.
I tried removing the directory path from the connection string in the datasource but it just errored.

Is this even possible?
Or is there a better way?

zoonosis
  • 799
  • 1
  • 11
  • 30

1 Answers1

2

Can you use VBA? If you do, you can place code in ThisWorkbook to update 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
LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46
  • Yes I can use VBA but in this case the excel query is generated from Sharepoint. So it wont work. Your code seems to change the SQL query and not the actual connection string or the source files location. – zoonosis Aug 06 '13 at 02:11
  • I didn't find a distinction between connection and query string in `ODBCConnection`. No, this could would not change source files - it can't, file is opened in Excel! It just updates connection whenever file is open or saved. – LS_ᴅᴇᴠ Aug 06 '13 at 07:28
  • That should have read "of the source files location", not "or". The connection string im talking about is about the file's location on the computer eg c:\my documents or d:\data. If I move the file the query fails. I need a way to either update the location or make it just look at the local folder the file is currently in. – zoonosis Aug 07 '13 at 00:52
  • Note that `ThisWorkbook.FullName` stands for workbook full pathname, so correct path is assigned. Another option is to change string to `".\Worbook.xls"`, and get sure that Excel is started from workbook directory. I don't know if it works. – LS_ᴅᴇᴠ Aug 07 '13 at 07:26
  • Ill give it a go. Thanks for you help. Tick for you. – zoonosis Aug 07 '13 at 23:23