Using MS Query to get data from multiple workbooks on Shared/Network drive via UNC path

Hi everyone. :) I've reached the limit of what googling can tell me about this problem. I'm hoping someone here can help out.

Using Excel 2007, I've created a set of workbooks. A number of regional books, and a master that collects their information together.

These workbooks are stored in the same folder, on a network drive, so as to be accessible from multiple sites.

I use MS Query and an SQL query to transfer any information between the books that is needed. These queries UNION all or a subset of the regional workbooks information.

First Problem: Each remote site maps this network drive to a different letter, or, at the very least, can't be expected to map it to the same letter. Given that the queries use the mapped path, I assume that this will prevent the queries from being refreshed by those remote users who map the drive differently? Unless the UNC is being referred to behind the scenes, or something helpful like that. If this isn't the case, PLEASE TELL ME. :D

In this case, I thought the most robust thing to do, is to use the UNC path to refer to any external data sources.

Simply swapping the paths out didn't achieve the desired affect, the path was invalid. I read a Microsoft help page that said I needed to specify the UNC path when creating the connection (via the "Connections" button, not the "From a Microsoft Query" or any other data source). This worked. However, now I was only pulling information from ONE workbook. I need to UNION the tables in multiple workbooks. However, this data source was not "From a Microsoft Query". The "Edit Query" button was greyed out. When I edit the command text to add the UNION, it gives the same error, where the path is invalid.

Second Problem: I need to use an SQL query to UNION together tables from multiple workbooks. These are stored on a network drive, as such, I need to use the UNC paths. I can make a query that gets data from a single workbook via its UNC path. When I add the UNION, it errors saying the path is invalid. Is it possible? If so, how? I would assume, given that it's possible to access one file this way, I should be able to access many at the same time.

Steps I've taken to verify the user account has access to the path:

I read that the "net use" command with the UNC path as its only argument, can verify this. It returned that the operation completed successfully.

Then I DIR'd into the folder via the UNC path, which also worked.

Possibly relevant information:

I'm not sure of the correct terminology, but the folder is located in a "share" of some kind. The drive icon and letter (in My Computer) does not map to the top level of the network drive, but what appears to be a shared folder in that top level. The list generated by "net use" would seem to confirm this. However, I have tried using this extra folder in the filepath to no avail. I don't know if I created the connection with this extra folder, but I will test that as soon as I can, though I don't expect much.

The workbooks were actually created in 2003 and then opened and saved in 2007. However, I am creating these connections in 2007. The extension is ".xlsm".

Any and all suggestions are appreciated. :)

Thanks
Scott

P.S. I'll find that MS page about specifying the UNC when creating a connection, and post a link.

EDIT: http://office.microsoft.com/en-us/ex...516.aspx?CTT=1

http://bytes.com/topic/access/answer...not-seen-excel

I think this may contain the solution. My paths had some folders that were not one word, as such I've been enclosing them in double quotes. This seemed fine for accessing a single file. Apparently, double quotes are an issue in UNION statements.

Again, I can't test this till Monday. Any help or a confirmation is appreciated.

EDIT: Additionally, it seems to suggest that I can't simply edit the command text if I want to use it with a pivot table (which is what I'm doing). The pivot table needs to be created by going through the "From Microsoft Query" option.

Feeling kinda sheepish. :D

It was the syntax, though it didn't like either double or single quotes.

What worked was using SQUARE brackets around the path, like this:

[\\server\share\folder\filename.xlsm].name

where name is a defined name, or something similar, in the Excel file being used as a data source.

I'm gonna mark this solved. If any mods think this info is more relevant to the General forum, feel free to move it.

EDIT: The same issue probably applies to all queries that use paths with multiple word directories, not just those involving UNC paths or UNION queries.