continuing thread68-1421332 Using MS Query to get data from Excel 1

[flyover789]

Hi again,

my question was:

Hey,

I have a question on the FAQ
FAQ68-5829: Using MS Query to get data from Excel by SkipVought.

I'm using the exact technique Skip is describing for building a little reporting tool in Excel. My problem is, that when the Excel workbook that contains the data source and the queries is moved to another location (e.g. forwarded by email), the queries don't recognize the datasource anymore. it seems that during creation of the queries the absolute path to the workbook was stored in the query definition.

any thought how this can be solved?

Many thanks in advance,
Best regards,
Martin

extremely enlightening answer from SkipVought was:

Hi,

Please post the question in forum707: VBA Visual Basic for Applications (Microsoft), as there will be macro coding involved. Nothing too difficult.

Paste these functions into a MODULE in your workbook and use as you would any other spreadsheet function.

CODE
Function QueryTableConnection(sSheetName As String, iQT_Nbr As Integer) As String
QueryTableConnection = Sheets(sSheetName).QueryTables(iQT_Nbr).Connection
End Function
Function QueryTableSQL(sSheetName As String, iQT_Nbr As Integer) As String
QueryTableSQL = Sheets(sSheetName).QueryTables(iQT_Nbr).CommandText
End Function
please post the results from these two function in your question in forum707: VBA Visual Basic for Applications (Microsoft).
Skip,

Many thanks, Skip so far!!

I will not post the whole result of the two function in here (business privacy) .... but here are two neutralized examples (one for the connection string, one for the SQL) that show, that the absolute path to the file is stored in both the connection and the SQL:

ODBC;DSN=Excel Files;DBQ=C:\Users\martin\Documents\InstBase.xls;DefaultDir=C:\Users\martin\Documents;DriverId=790;
MaxBufferSize=2048;PageTimeout=5;

SELECT `'Source Data$'`.Country
FROM `C:\Users\martin\Documents\InstBase`.`'Source Data$'` `'Source Data$'`
GROUP BY `'Source Data$'`.Country
HAVING (`'Source Data$'`.Country Is Not Null)
ORDER BY `'Source Data$'`.Country

Any idea how this can be made a relative path or however, how my problem can be solved?

Greatly appretiate your help !!

Best regards,
Martin

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.eu

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[SkipVought]

Change the path assigned to sPath and/or the database assigned to sDB if necessary.

You will notice that the path \ database are in both the sommection string as well as the SQL, just FYI.

ALSO be sure that you use the appropriate Sheet name for the sheet containing the query table.

Sub GetData()
    Dim sPath As String, sDB As String, sConn As String, sSQL As String
[b]
'change this path as needed [/b]
    sPath = "C:\Users\martin\Documents"
[b]
'change this database as needed[/b]
    sDB = "InstBase"
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "MaxBufferSize=2048;PageTimeout=5;"
    
    sSQL = "SELECT `'Source Data$'`.Country"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.`'Source Data$'` `'Source Data$'`"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "GROUP BY `'Source Data$'`.Country"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "HAVING (`'Source Data$'`.Country Is Not Null)"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "ORDER BY `'Source Data$'`.Country"

    With Sheets("[b]YourSheetName[/b]").QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh BackgroundQuery = True
    End With
End Sub

BTW, I will often add a QT to a sheet, then turn on the macro recorder and record Data > Get external Data > Edit Query ---- File > Return data to Excel, which results in a gibberish of the code I posted. I then, clean it up and run/refresh the query using that code, as I often CHANGE the SQL or the Connection on the fly with interactive controls.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[SkipVought]

I really did not pay much attention to the SQL code, but may I offer a comment or two.

1. The GROUP BY clause is only needed when performing an aggregation: Sum, Count, Min, Max, etc AND where there are fields in the SELECT clause other than the aggregations(s)

2. The HAVING clause is only necessary for an aggregation criteria, like HAVING SUM(Amount)>0. Otherwise, put all criteria in the WHERE clause.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[flyover789]

Hey Skip,

thanks a lot for the input.

As I needed a dynamic way of adapting the connection- and SQL-strings of all querytables to the path where the workbook is opened from, I've applied the following solution:

Created 3 functions to update the conn-string, SQL-string and refresh the querytables and call them subsequently in the Workbook_Open event:

Function localize_QT_connections()
Dim sSheetName As String
Dim iQT_Nbr As Integer
Dim qtconn As String
'MsgBox "hello conn"

For Each Worksheet In ActiveWorkbook.Sheets
Debug.Print Worksheet.Name
Debug.Print "querytablescount" & Worksheet.QueryTables.Count
If Worksheet.QueryTables.Count > 0 Then
iQT_Nbr = 1
For Each QueryTable In Worksheet.QueryTables
Worksheet.QueryTables(iQT_Nbr).Connection = "ODBC;DSN=Excel Files;DBQ=" & _
ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & _
";DefaultDir=" & ActiveWorkbook.Path & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
qtconn = QueryTableConnection(Worksheet.Name, iQT_Nbr)
Debug.Print qtconn
iQT_Nbr = iQT_Nbr + 1
Next QueryTable
End If
Next Worksheet

End Function


Function localize_QT_SQL()
Dim sSheetName As String
Dim iQT_Nbr As Integer
Dim qtSQL As String
Dim qtSQLfind1 As String
Dim qtSQLfind As String
Dim qtSQLreplace As String
Dim i As Integer
'MsgBox "hello SQL"

For Each Worksheet In ActiveWorkbook.Sheets
If Worksheet.QueryTables.Count > 0 Then
iQT_Nbr = 1
For Each QueryTable In Worksheet.QueryTables
qtSQL = ""
qtSQLreplace = ""
qtSQLfind1 = ""
qtSQLfind = ""
qtSQL = QueryTableSQL(Worksheet.Name, iQT_Nbr)
Debug.Print qtSQL & vbCrLf
If InStr(qtSQL, ":") > 0 Then
qtSQLfind1 = Right(qtSQL, Len(qtSQL) - InStr(qtSQL, ":") + 2)
qtSQLfind = Left(qtSQLfind1, InStr(qtSQLfind1, ".") - 2)
Debug.Print qtSQLfind & vbCrLf
qtSQLreplace = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)
Debug.Print qtSQLreplace & vbCrLf
Worksheet.QueryTables(iQT_Nbr).CommandText = Replace(Worksheet.QueryTables(iQT_Nbr).CommandText, _
qtSQLfind, qtSQLreplace)

qtSQL = QueryTableSQL(Worksheet.Name, iQT_Nbr)
Debug.Print "qtSQLnew " & qtSQL & vbCrLf

End If

iQT_Nbr = iQT_Nbr + 1
Next QueryTable
End If
Next Worksheet

End Function



Function refresh_QTs()
Dim sSheetName As String
Dim iQT_Nbr As Integer
'MsgBox "hello refreshing"

For Each Worksheet In ActiveWorkbook.Sheets
Debug.Print Worksheet.Name
Debug.Print "querytablescount" & Worksheet.QueryTables.Count
If Worksheet.QueryTables.Count > 0 Then
iQT_Nbr = 1
For Each QueryTable In Worksheet.QueryTables
Worksheet.QueryTables(iQT_Nbr).Refresh BackgroundQuery = True
iQT_Nbr = iQT_Nbr + 1
Next QueryTable
End If
Next Worksheet

End Function

Private Sub Workbook_Open()
Call localize_QT_connections
Call localize_QT_SQL
Call refresh_QTs
End Sub

regarding the SQL with the goup by clause: Here I'm obtaing unique values from the source that contains each country multiple times ... that's why I put the SQL.

Best regards,
Martin

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.eu

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]