using worksheet in same workbook for query
 

Due to an "unusual" situation, I have had to setup a workbook as an RDBMS.

worksheets are treated as tables.

Using the MS-Query functionality in excel, everything works fine except when
I try to send the file to other people, the workbook is unable to reference
itself as the source of the MS-Query and I think looks for the absolute path
of the file on my machine.

Is there a way to point to the same worksheet "relatively"?

Hope I'm making sense :) thanks in advance

using worksheet in same workbook for query
 

In the VBA editor, type in the immediate pane
? Activesheet.Querytables(1).CommandText
(this assumes the active sheet is the one with the query results and that
there is only the one query table on the sheet)
The CommandText gives the connection string that specifies, among other
things, the file path. You will need to write code to duplicate the string
but replace the path. The path for the workbook in use is
ThisWorkbook.FullName, so you can use that to change the path showing in the
CommandText. To ensure it is up to date, you should really check it just
before the query is run (since the user could have resaved the file under a
new name). If you use the option to refresh when a parameter linked to a
cell changes, you may want to run the code to do this in the Worksheet_Change
event procedure; or if you use another macro to refresh the query you can put
it in there. Example below:

Let's say the string you get for the CommandText is:
SELECT MyTable.`Header 1`, MyTable.'Header 2' FROM `C:\Excel\MyFile`.MyTable
MyTable

Before running the query, run this line of code:
Worksheets("SheetName").QueryTables(1).CommandText = _
"SELECT MyTable.`Header 1`, MyTable.'Header 2' FROM '" _
& ThisWorkbook.FullName & "'`.MyTable MyTable"

That should update the query to use the current file instead of the
originally saved one.
Hope this works for you.

--
- K Dales


"Jaypee" wrote:

Due to an "unusual" situation, I have had to setup a workbook as an RDBMS.

worksheets are treated as tables.

Using the MS-Query functionality in excel, everything works fine except when
I try to send the file to other people, the workbook is unable to reference
itself as the source of the MS-Query and I think looks for the absolute path
of the file on my machine.

Is there a way to point to the same worksheet "relatively"?

Hope I'm making sense :) thanks in advance

using worksheet in same workbook for query
 

Thanks! That did it!

"K Dales" wrote:

In the VBA editor, type in the immediate pane
? Activesheet.Querytables(1).CommandText
(this assumes the active sheet is the one with the query results and that
there is only the one query table on the sheet)
The CommandText gives the connection string that specifies, among other
things, the file path. You will need to write code to duplicate the string
but replace the path. The path for the workbook in use is
ThisWorkbook.FullName, so you can use that to change the path showing in the
CommandText. To ensure it is up to date, you should really check it just
before the query is run (since the user could have resaved the file under a
new name). If you use the option to refresh when a parameter linked to a
cell changes, you may want to run the code to do this in the Worksheet_Change
event procedure; or if you use another macro to refresh the query you can put
it in there. Example below:

Let's say the string you get for the CommandText is:
SELECT MyTable.`Header 1`, MyTable.'Header 2' FROM `C:\Excel\MyFile`.MyTable
MyTable

Before running the query, run this line of code:
Worksheets("SheetName").QueryTables(1).CommandText = _
"SELECT MyTable.`Header 1`, MyTable.'Header 2' FROM '" _
& ThisWorkbook.FullName & "'`.MyTable MyTable"

That should update the query to use the current file instead of the
originally saved one.
Hope this works for you.

--
- K Dales


"Jaypee" wrote:

Due to an "unusual" situation, I have had to setup a workbook as an RDBMS.

worksheets are treated as tables.

Using the MS-Query functionality in excel, everything works fine except when
I try to send the file to other people, the workbook is unable to reference
itself as the source of the MS-Query and I think looks for the absolute path
of the file on my machine.

Is there a way to point to the same worksheet "relatively"?

Hope I'm making sense :) thanks in advance

using worksheet in same workbook for query
 

ok, when I tried it on my actual project... it was not working.

for some reason, my worksheets are treated as "system tables" and when I
copy it to a different machine, it cannot read it.

"Jaypee" wrote:

Thanks! That did it!

"K Dales" wrote:

In the VBA editor, type in the immediate pane
? Activesheet.Querytables(1).CommandText
(this assumes the active sheet is the one with the query results and that
there is only the one query table on the sheet)
The CommandText gives the connection string that specifies, among other
things, the file path. You will need to write code to duplicate the string
but replace the path. The path for the workbook in use is
ThisWorkbook.FullName, so you can use that to change the path showing in the
CommandText. To ensure it is up to date, you should really check it just
before the query is run (since the user could have resaved the file under a
new name). If you use the option to refresh when a parameter linked to a
cell changes, you may want to run the code to do this in the Worksheet_Change
event procedure; or if you use another macro to refresh the query you can put
it in there. Example below:

Let's say the string you get for the CommandText is:
SELECT MyTable.`Header 1`, MyTable.'Header 2' FROM `C:\Excel\MyFile`.MyTable
MyTable

Before running the query, run this line of code:
Worksheets("SheetName").QueryTables(1).CommandText = _
"SELECT MyTable.`Header 1`, MyTable.'Header 2' FROM '" _
& ThisWorkbook.FullName & "'`.MyTable MyTable"

That should update the query to use the current file instead of the
originally saved one.
Hope this works for you.

--
- K Dales


"Jaypee" wrote:

Due to an "unusual" situation, I have had to setup a workbook as an RDBMS.

worksheets are treated as tables.

Using the MS-Query functionality in excel, everything works fine except when
I try to send the file to other people, the workbook is unable to reference
itself as the source of the MS-Query and I think looks for the absolute path
of the file on my machine.

Is there a way to point to the same worksheet "relatively"?

Hope I'm making sense :) thanks in advance