Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change SELECT FROM When Using Excel to Query Excel
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change SELECT FROM When Using Excel to Query Excel
concatenate in the path from the activeworkbook. Something like :
SELECT [everything$].ID, [everything$].UID, ... FROM `" & activeworkbook.path & "\`.[everything$] [everything$] Peter Richardson "Baglog" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change SELECT FROM When Using Excel to Query Excel
I'm sorry, I don't understand the response. The data source name is
export.xls... could you please clarify? Thanks! "barnabel" wrote: concatenate in the path from the activeworkbook. Something like : SELECT [everything$].ID, [everything$].UID, ... FROM `" & activeworkbook.path & "\`.[everything$] [everything$] Peter Richardson "Baglog" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change SELECT FROM When Using Excel to Query Excel
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$] "Baglog" wrote: I'm sorry, I don't understand the response. The data source name is export.xls... could you please clarify? Thanks! "barnabel" wrote: concatenate in the path from the activeworkbook. Something like : SELECT [everything$].ID, [everything$].UID, ... FROM `" & activeworkbook.path & "\`.[everything$] [everything$] Peter Richardson "Baglog" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fields change order from MS Query to Excel | Excel Discussion (Misc queries) | |||
select database file path location for excel query | Excel Programming | |||
Excel MS Query multiple select statements | Excel Discussion (Misc queries) | |||
How do I select on a SQL Datetime field in Excel Query? | Excel Programming | |||
CHANGE QUERY SQL CUBE FROM EXCEL | Excel Programming |