SQL Connections To Tables Within Same Excel Spreadsheet

[tjh392]

Hi all,

I'm hoping that someone on here might be able to help.

I've created a workbook which contains tables of data on individual sheets (see example below)

On Sheet1 I have an SQL statement which queries these tables using the connection "From Microsoft Query" and then I've chosen that same Excel workbook as the source and the tables within it.

Problem is if I move the workbook, all the hardcoded connections need updating to show the new file location which poses a problem when I will want to email this. Is there a way to make my connections dynamic so that they update and follow wherever I move the workbook?

If I try refreshing the data without updating the connections to the new file location I get this error message:

So to recap, I'm not actually connecting to an external source I'm only querying the tables within the same spreadsheet, perhaps there's a better way to do it? Any help would be appreciated!

Thanks

Tim

 

[RoryA]

I guess the first question would be, why are you using a query?

As far as updating the links goes, you'd need code to update the connection.

 

[tjh392]

Hi Rory, well the first sheet is basically a Division table which then separates the teams by division and underneath by the people who represent that area. This way the person who updates all the details can do it with minimal alterations to the front page. Also on other sheets it's shows projects occurring for each of those clubs so manual manipulation would be a lot more intensive than using these queries.

 

[RoryA]

OK - you're going to need code then. Something like this should do it:

Private Sub Workbook_Open()
   Dim oConn                       As WorkbookConnection
   Dim strOldFilePath              As String
   Dim n                           As Long
   Dim vParts

   For Each oConn In Me.Connections
      If oConn.Type = xlConnectionTypeODBC Then
         vParts = Split(oConn.ODBCConnection.Connection, ";")
         For n = LBound(vParts) To UBound(vParts)
            If Left$(UCase$(vParts(n)), 4) = "DBQ=" Then
               strOldFilePath = Mid$(vParts(n), 5)
               Exit For
            End If
         Next n

         If strOldFilePath <> Me.FullName Then
            With oConn.ODBCConnection
               .Connection = Replace$(.Connection, strOldFilePath, Me.FullName)
               .CommandText = Replace$(.CommandText, strOldFilePath, Me.FullName)
               .Refresh
            End With
         End If
      End If
   Next oConn

End Sub

Note: this must be placed in the ThisWorkbook module of the workbook project. It will then run automatically when the workbook is opened.

 

[tjh392]

Thanks Rory! Much appreciated. I'll give it a go and let you know.

 

[tjh392]

I've just remembered that when I previously saved my workbook from .xlsx to .xlsm the SQL statements on the front page would run without showing an error but would not update the data within.

Is there something I can do to fix that first before implementing your code above?

Thanks

 

[RoryA]

The code should take care of that the next time you open the file.

 

[tjh392]

The code should take care of that the next time you open the file.

Thanks Rory, that worked a treat. I was able to run the SQL within the xlsm, initially I found that some of the SQL statements under the Command Text still had the old file location, whilst other's didn't. I realised that the ones that had updated had been aliased under the Name Manager so after adding all the tables using that it worked without any further alterations.

Thanks for your help.

 

[RoryA]

Glad to help.

 

[tjh392]

Thanks Rory, that worked a treat. I was able to run the SQL within the xlsm, initially I found that some of the SQL statements under the Command Text still had the old file location, whilst other's didn't. I realised that the ones that had updated had been aliased under the Name Manager so after adding all the tables using that it worked without any further alterations.

Thanks for your help.

Just got back round to altering this spreadsheet and I thought I best update the thread. The problem with some connections not updating in the SQL query was down to something a lot simpler than I thought. I didn't have to use aliases to make it work the SQL code just needed the hardcoded file location removing for it to work for example:

From:

SELECT 
   `Clubs Data$`.Team, 
   `Clubs Data$`.Division

FROM `E:\RFU Divisions 2013.xlsm`.`Clubs Data$` `Clubs Data$`

WHERE 
   (`Clubs Data$`.Division='Division C')

To:

SELECT 
   `Clubs Data$`.Team, 
   `Clubs Data$`.Division

FROM `Clubs Data$` `Clubs Data$`

WHERE
   (`Clubs Data$`.Division='Division C')

By removing the location at the start of the FROM the query is still able to run and is now update-able using the above macro.