Passing variable values (complete specifically path of an MDB) inside a SQL query in Excel

[va1b4av]

Hi Folks,

There's this application we are working on here, written completely in excel and uses Pivot tables. Now, we have a particular pivot table here that gets the data from a SQL query (using Microsoft Query I think).

Here's what I do to get to the query:
Right click on a cell in the Pivot sheet and Go to the Pivit Table Wizard. Subsequently, follow all the steps till I get to the SQL query.

Here's the query I get <table width="550px"></table>

SELECT `SS Camp 2010`.Country, `SS Camp 2010`.`Product Line Name`, `SS Camp 2010`.`Edition Name`, `SS Camp 2010`.`Promotion Prefix`, `SS Camp 2010`.Info, `SS Camp 2010`.Year, `SS Camp 2010`.Month, `SS Camp 2010`.Week, `SS Camp 2010`.Despatch, `SS Camp 2010`.Intake
FROM `[COLOR=Red][B]G:\topstat\xls-ro\SSCAMP`.`SS Camp 2010` `SS Camp 2010`[/B][/COLOR]
ORDER BY `SS Camp 2010`.Year

Now, the highlighted RED section is a path to an MDB file placed within the same folder. We need to remove all hardcoding references to this path so that in case the entire folder gets moved to some other place (say F:\topstat\xls-ro\....), it does not break.

The MDB file and the Pivot sheet will ALWAYS remain in the same folder structure.

Any help or inputs on this would be appreciated.

Thanks

 

[Sektor]

ThisWorkbook.Path

 

[va1b4av]

Thanks for your reply Sektor..I just tried to use this in the query,

SELECT `SS Camp 2011`.Country, `SS  Camp 2011`.`Product Line Name`, `SS Camp 2011`.`Edition Name`, `SS Camp  2011`.`Promotion Prefix`, `SS Camp 2011`.Info, `SS Camp 2011`.Year, `SS Camp  2011`.Month, `SS Camp 2011`.Week, `SS Camp 2011`.`Week Number`, `SS Camp  2011`.Despatch, `SS Camp 2011`.Intake
 FROM [B][COLOR=Red]ThisWorkbook.Path.`SS Camp  2011` `SS Camp 2011`[/COLOR][/B]

Fails with the following error:

Could not add the table ' ThisWorkbook.Path.`SS Camp 2011` `SS Camp 2011`'

Is 'ThisWorkBook.Path' not used for VBA code, does it also work for queries?

 

[Ruddles]

Rather than:-

FROM `[COLOR=red][B]G:\topstat\xls-ro[/B][/COLOR]\SSCAMP`.`SS Camp 2010`

try:-

FROM `[COLOR=red][B].[/B][/COLOR]\SSCAMP`.`SS Camp 2010`

(Not tested here.)

 

[va1b4av]

Thanks for your replies guys..I am trying this out on the application..Will post the results here.
thanks again for taking the the time to help

 

[va1b4av]

okay guys, I wa able to figure out a solution to this:
Basically if the MDb and the excel reside in the same folder, I can just use the following:

FROM `\SSCAMP`.`SS Camp 2010

However, when the excel and the mdb reside in different locations, this would not work.Any inputs on that would be highly appreciated.