Question on Using MS Query to get data from Excel 1

[flyover789]

Hey,

I have a question on the FAQ
faq68-5829 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

[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]

Hi,

Please post the question in Forum707, 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.

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.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[flyover789]

done. and thanks a million so far!

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]