path without hardcoding

[nancylee]

Hi,

I am trying to eliminate all the hardcoding of the drive so that I don't have to change the database every time the user change a drive. How should I code this ?

Please help. Thanks!

 

[Rob.Mills]

Why does the drive change? Is the user moving the application around?

 

[nancylee]

I think an example can illustrate this.

1. A excel spreadsheet is located in x:\ drive
2. In the database the vba is hard coded x:\nnn\nnn.xls

When the user click on the button (front-end) it will run the event procedure. The function is to access the xls in x:\ to do processing.

However, if the user change the mapping of nnn to Y then the program won't work because the drive is hard coded. This is the dilemna. I have this kind of problem in macros and modules.

Thanks!

 

[Rob.Mills]

I see. I have a class module in a db at work that uses the Windows open file window to let the user select a file and its path. Then it will return the file as a string variable that you could use in your code. You could have the code test whether the file is still in the same place and if not then open this window. Right now I'm snowed in at home. I'll post it when I get back to work probably Wed.

 

[nancylee]

I think I got your idea:

1. hard code a drive (current) in the module
2. when the drive mapping has changed by the user, prompt user to enter a new drive
3. the module will then use the new drive

What I am thinking is :
Is there a function that can search for the drive and then I can append the drive letter to the directory ?

Thanks very much for your prompt reply

 

[Rob.Mills]

Pat - I can always depend on you to swoop in and give a more simple answer. ;-)

I'm losing credibility because of you.

 

[Rob.Mills]

Well I'll give you the code you can put in a class module to use anyway.

I got this out of Access Developer Handbook's. Gotta give credit where it's due. Just copy and paste the whole thing into the class module. Then you call it like this:

Dim cdl as New Commondlg

Then setup the properties for it as you would any other object.

 

[nancylee]

Thank you very much for your help