Generic Connections in Queries? (1 Viewer)

[HGMonaro]

Long shot... but is there a way to have a file reference without full path reference in a query?

Senario... I have a spreadsheet with several queries to another spreadsheet (hope to change this to an Access DB at some stage since that's where the data comes from) that hold valid values for those queries. The idea was the 2nd spreadsheet was to be updated whenever needed and distributed to the couple of people that needed to use it. Worked fine as those people were all on the same site with similar drive mappings, however I've now got someone else needing it at a different site and they map to our server as a different drive letter (can't be helped... they have their own local server with similar drive mappings to ours that can't be changed). I don't want to have to send them a special version.

I have the queries seperate sheets in the 'master' spreadsheet and after reading heaps (but not finding an exact answer) I thought maybe I can manipulate the connection string so that it works from the folder this file resides in (thus picking up the different drive mappings in the process). Haven't tried this yet though.

Any other suggestions?

 

[noboffinme]

Hi HGMonaro

I think you're talking about a UNC path.

Instead of using any Drive letter, the path is written in the below syntax;

\\Shared1_svr\Shared1\WGroups\Network\Orders.xls.

Use the path that you paste in when you map your shared network drive.

Another way to get this string is to open an email or Word doc Go to Insert a file & Browse to the file you want everyone to find, then insert the file as a hyperlink once the hyperlink is in your document, right click on it, select 'Edit Hyperlink' & copy the string where the dialogue box says 'Address'.

This should be the exact path to drop into your code. No matter what letter your users have the drive named as, it will go to the file.

Hth

 

[HGMonaro]

thanks for the quick reply!

It sorta works... works on the 1st occurance but opens the secondary spreadsheet in read mode. On the second occurance (by selecting a different option in a combo box) it waits to get read/write access (pops up a dialog to say Read/write access is now available). If I cancel that dialog (leaving the file in read only mode) and ALT-TAB back to my main spreadsheet, it works fine. When I had the original drive references it didn't open the secondary file at all? When I go to close that file, it asks me if I want to save my changes... not that I explicitly do any... actually I think this is because I blank out some cells but because this file is now open and got the focus it goes and blanks the cells in this file instead of where it's supposed too! Before I try to get around that I think I need to find why the file is now opening rather than just being queried. Maybe the connection parameter has changed... off to check that now...