Change SELECT FROM When Using Excel to Query Excel

[Guest]

Hi Everyone,

I am creating an excel workbook that I would like to distribute to others.
This workbook uses another excel workbook as the data source. Currently, the
select statement looks like this:

SELECT [everything$].ID, [everything$].UID, ...
FROM `C:\path\export`.[everything$] [everything$]

What I would like to accomplish is remove the "C:\path" part and have the
query look for the data store in the same directory as the workbook. This
should allow me to zip up the file and the data store and distribute it to
others with the instructions to just overwrite the the data store with more
current information.

I have changed the FROM line to read:
FROM `export`.[everything$] [everything$]

and I get an error: Could not add the table '`export`.[everything$]'.

Is there a graceful way to accomplish this? My backup plan is to just force
the users to use a hard-coded directory (small user community), but I'd like
to shoot for something a little bit more flexible.

Thanks in advance!

 

[Guest]

concatenate in the path from the activeworkbook. Something like :

SELECT [everything$].ID, [everything$].UID, ...
FROM `" & activeworkbook.path & "\`.[everything$] [everything$]

Peter Richardson

 

[Guest]

I'm sorry, I don't understand the response. The data source name is
export.xls... could you please clarify? Thanks!

concatenate in the path from the activeworkbook. Something like :

SELECT [everything$].ID, [everything$].UID, ...
FROM `" & activeworkbook.path & "\`.[everything$] [everything$]

Peter Richardson

Hi Everyone,

I am creating an excel workbook that I would like to distribute to others.
This workbook uses another excel workbook as the data source. Currently, the
select statement looks like this:

SELECT [everything$].ID, [everything$].UID, ...
FROM `C:\path\export`.[everything$] [everything$]

What I would like to accomplish is remove the "C:\path" part and have the
query look for the data store in the same directory as the workbook. This
should allow me to zip up the file and the data store and distribute it to
others with the instructions to just overwrite the the data store with more
current information.

I have changed the FROM line to read:
FROM `export`.[everything$] [everything$]

and I get an error: Could not add the table '`export`.[everything$]'.

Is there a graceful way to accomplish this? My backup plan is to just force
the users to use a hard-coded directory (small user community), but I'd like
to shoot for something a little bit more flexible.

Thanks in advance!

 

[Guest]

Sorry I took out too much. Simply replace the "C:\PATH" part with the value
for Activeworkbook.path This will be the path to the workbook that they had
selected when they ran the macro so should give the folder they copied the 2
books into.

SELECT [everything$].ID, [everything$].UID, ...
FROM `" & activeworkbook.path & "\export`.[everything$] [everything$]

I'm sorry, I don't understand the response. The data source name is
export.xls... could you please clarify? Thanks!

concatenate in the path from the activeworkbook. Something like :

SELECT [everything$].ID, [everything$].UID, ...
FROM `" & activeworkbook.path & "\`.[everything$] [everything$]

Peter Richardson

Hi Everyone,

I am creating an excel workbook that I would like to distribute to others.
This workbook uses another excel workbook as the data source. Currently, the
select statement looks like this:

SELECT [everything$].ID, [everything$].UID, ...
FROM `C:\path\export`.[everything$] [everything$]

What I would like to accomplish is remove the "C:\path" part and have the
query look for the data store in the same directory as the workbook. This
should allow me to zip up the file and the data store and distribute it to
others with the instructions to just overwrite the the data store with more
current information.

I have changed the FROM line to read:
FROM `export`.[everything$] [everything$]

and I get an error: Could not add the table '`export`.[everything$]'.

Is there a graceful way to accomplish this? My backup plan is to just force
the users to use a hard-coded directory (small user community), but I'd like
to shoot for something a little bit more flexible.

Thanks in advance!