Change Data Source of OLEDBConnection

[OneMEG]

I have a workbook I created that has an external connection to another XLSX file. I created it by going to the Data tab, then New Query->From File->From Workbook. It created this as a Workbook connection rather than a Query table. By that I mean in VBA ActiveSheet.QueryTables.Count=0, but ThisWorkbook.Connections.Count=1.

Further, when I look at the Connection Properties definition, the Connection string is:

Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=page;Extended Properties=""

The command Type is SQL, and the Command text is SELECT * FROM [page]

The problem I have is this - when I copy both workbooks (the one with the external reference, and the one it is referencing) to another location (another computer), it cannot find the linked file, which is understandable. Normally, I would create a macro when the workbook opens to change the Connection string. However, I am not familiar with how this works for a OLEDB connection. The string says Data Source=$Workbook$, and no matter where I look in VBA, I cannot seem to find where that string is held.

I know it is saved with the workbook somewhere, because when I try to refresh, it tells me it cannot find the file and it displays the full path/name of the original location it was linked to.

I would have assumed the following VBA would work, but it only returns an empty string:

ThisWorkbook.Connections(1).OLEDBConnection.SourceDataFile

Is there anyone who can tell me how to use VBA to reset the file path & name for this connection?

Thanks in advance!

 

[John_w]

Welcome to MrExcel forums.

Try the QueryTable within the ListObjects collection. This code dumps the ListObjects and QueryTables:

Public Sub Excel2007Connections()
    Dim ws As Worksheet
    Dim lo As ListObject
    Dim qt As QueryTable
     
    For Each ws In ThisWorkbook.Worksheets
        For Each lo In ws.ListObjects
            Debug.Print "List Object Name: " & lo.Name
            Debug.Print "List Object address: " & lo.Range.Address
            Set qt = lo.QueryTable
            Debug.Print "Query command text: " & qt.CommandText
            Debug.Print "Query connection: " & qt.Connection
            Debug.Print
        Next lo
    Next ws
End Sub

 

[OneMEG]

Thanks for the response. Unfortunately, this routine gives me the same info as the properties under ThisWorkbook.Connections(1).OLEDBConnection. All it produces is:

List Object Name: page
List Object address: $A$1:$O$34923
Query command text: SELECT * FROM [page]
Query connection: OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=page;Extended Properties=""

Still no indication of where the path & filename of the linked file is.

 

[John_w]

Those were just some of the properties of those two objects. Look in the Locals window for other properties, for example qt.SourceConnectionFile.

 

[OneMEG]

Respectfully, I have been through all that. I cannot find it no matter where I look, that is why I posted on this forum.

It is easy to reproduce this - open a blank workbook, go to the Data tab, then New Query->From File->From Workbook, then select any workbook with a table of information. It should create the link as an OLEDB connection. If anyone can find where the actual path/filename are held, then please let me know.

I would not call myself an Excel expert, but I have been developing in Access and Excel for 20+ years, and have more than a passing familiarity with VBA and the Excel Object Model. I don't have a lot of experience with OLEDB connections, and I simply cannot figure out where the path/filename are held.

 

[John_w]

Sorry, I didn't think I was telling you the obvious, which is now apparent from your level of experience of developing in Excel. I discovered the ListObjects collection when moving from Excel 2003 to 2007 as an extra level of abstraction for QueryTables.

The macro recorder is very helpful here. I recorded your steps and it produced the following first line of code:

    ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:\Temp\Temp table.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & "    Table1_Table = Source{[Item=""Table1"",Kind=""Table""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Table1_Table,{{""ColA"", type text}, {""ColB"", type text}, {""ColC"", type text}, {""ColD"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""

As you can see, the Formula property contains the full path and file name of the workbook containing the source table, and this is part of the ThisWorkbook.Queries collection:

Sub dq()
    Dim q As WorkbookQuery
    For Each q In ThisWorkbook.Queries
        Debug.Print q.Name
        Debug.Print q.Formula
    Next
End Sub

 

[OneMEG]

That was brilliant! I'm embarrassed now because I didn't think of using the macro recorder. Thank you so much for your insight.

 

[Abvlecxe]

Hi Onemeg,
What was your final code used to change your data source?

I am facing a similar issue and have spent days searching for an answer but i've not yet found one that works!
Thanks

That was brilliant! I'm embarrassed now because I didn't think of using the macro recorder. Thank you so much for your insight.