StevenPMoffat
asked on
Change connection string to use relative path instead of full path
I want to change my query connection string that is used on one tab of a workbook and extracts information from another tab on the same workbook. In the old days this was called using a relative pathname instead of a fullpathname. The reason I want this is that I want to be able to move the file to another folder or give the file to someone else and I want the queries to still work. Here is a sample connection string.
DSN=Excel Files;DBQ=C:\Documents and Settings\moffats\My Documents\aBudget\Source.x
The other thing that I note which I don't understand is that this excel workbook is a macro enabled workbook, yet the filename has only a .xls??? Reason? The queries work fine until I move the file to another location.
DSN=Excel Files;DBQ=C:\Documents and Settings\moffats\My Documents\aBudget\Source.x
The other thing that I note which I don't understand is that this excel workbook is a macro enabled workbook, yet the filename has only a .xls??? Reason? The queries work fine until I move the file to another location.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I am hoping for something a little more automatic. I realize that I can use the first idea to maybe code VBA on each open of the workbook to check and update the pathname. WHat I am looking for is something that I can make the actual connection string so that it will always go to the same folder the file is in? Can i use the "CurrentDir = ActiveWorkbook.Path" inside the connection string?
As another example. In the unix or DOS days you could have a pathname that began with somehting like "../" that would refer to the current directory. This was called a relative pathname as opposed to a full pathname that started from C:/........... I was hoping for something that I can edit in the connection string that will work whereever I place the file.
As another example. In the unix or DOS days you could have a pathname that began with somehting like "../" that would refer to the current directory. This was called a relative pathname as opposed to a full pathname that started from C:/........... I was hoping for something that I can edit in the connection string that will work whereever I place the file.
no Steven you're right... my code would be based on VBA that executed either at xls file startup or on the press of a button pointing to a macro or something.
i haven't tried this but have you tried some simple file, folder references??
DSN=Excel Files;DBQ=Source.xls;Defau
basically DBQ points to a file in the same directory and for defaultdir, try a bunch of different iterations of local file specifications because unfortunately i'm not sure and don't have a file to test with. try using these for the defaultdir param:
.
./
.\
blank?
i haven't tried this but have you tried some simple file, folder references??
DSN=Excel Files;DBQ=Source.xls;Defau
basically DBQ points to a file in the same directory and for defaultdir, try a bunch of different iterations of local file specifications because unfortunately i'm not sure and don't have a file to test with. try using these for the defaultdir param:
.
./
.\
blank?
ASKER
Trying these options.
ASKER
It appears that I have to use VBA to reset the pathnames when I open the file. The other options suggested didn't work.
currentDir = ActiveWorkbook.Path
instead