Embedding MS query into an excel

Question

I have made an excel workbook consisting of several complex worksheets.

Now I have created queries using MS query to allow me to gather the information needed for calculations from these different worksheets. Everything works just fine until I move the workbook to a new location.

As this is going to happen often and I do not want to rebuild my queries every time I was wondering if there is a way to link my query to the workbook itself and not to a specific file location.

Can anybody help me?

This is de SQL build up of the query

SELECT `'Production Plan$'`.`Scheduled Operation`, `Tests$`.`Test Name`, `Tests$`.`Tests per batch`, `Tests$`.`Ops PER BATCH or PER YR:`, `Tests$`.`Total Locations (MULTIPLIER):`, `Tests$`.`Retest Rate`, `'Production Plan$'`.`Batches for 2015`, `'Production Plan$'`.`Batches for 2016`, `'Production Plan$'`.`Batches for 2017`, `'Production Plan$'`.`Batches for 2018`
FROM `C:\Users\UGent\Desktop\XXX\XXX\Models\new model SV 0106.xlsx`.`'Production Plan$'` `'Production Plan$'`, `C:\Users\UGent\Desktop\XXX\XXX\Models\new model SV 0106.xlsx`.`Tests$` `Tests$`
WHERE `'Production Plan$'`.`Scheduled Operation` = `Tests$`.`Scheduled Operation:`

And this is the connection string

DSN=Excel Files;DBQ=C:\Users\UGent\Desktop\BQG\Genzyme\Models\new model SV 0106.xlsx;DefaultDir=C:\Users\UGent\Desktop\BQG\Genzyme\Models;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Answer 1

I was wondering if there is a way to link my query to the workbook itself and not to a specific file location

No - you cannot "link` the query to an Excel Workbook as there is (as I am aware) no mechanism to track how you move the source Excel Workbook.

What can you do?

Option 1 - Manual reconnection

Write a VBA macro that will recreate the SQL query. It could work like this:

1. When opening your Workbook a VBA macro runs at the Workbook.Open event

2. The Event macro checks if the source worksheet is located in the correct file path. If not then (3)

3. If the file is not there then recreate then show a popup asking for the new file path. When the user clicks OK, recreate SQL query via VBA macro. You can use macro recording to extract the necessary VBA or see here.

Some example code of how to create a MS Query in VBA:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=C:\test.xlsx;DefaultDir=C:\;DriverId=1046;Ma" _
        ), Array("xBufferSize=2048;PageTimeout=5;")), Destination:=Range("$A$1")). _
        QueryTable
        .CommandType = 0
        .CommandText = Array( _
        "SELECT `Sheet1$`.A, `Sheet1$`.B" & Chr(13) & "" & Chr(10) & "FROM `C:\test.xlsx`.`Sheet1$` `Sheet1$`" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Excel_Files"
        .Refresh BackgroundQuery:=False
    End With

Option 2 - Push the Excel to a network location

Try locating the Workbook into a Network location that will not require frequently asking for the the source filepath. You can even place it as a link. It could work like this:

1. Download the file using VBA or tools on the Data ribbon section

2. Recreate the query as above

Option 3 - Push the Excel source data to a database like SQL

1. Push the data to SQL Server, MySQL or another easily accessible database

2. Make a permanent SQL.OLEDB connection from your master Workbook