I send out an excel file to about 150 locations that contains a macro for
posting bonus information to a second excel file. Because I cannot control
the location where this second file is placed, I currently have the user
"search" (just like when you open or save as a file) for the file when the
macro is started.
What I would prefer to do is have the user search for the file the first
time, and once they find it and click on the file to open, have the macro
save the path so that in the future, the macro will automatically know where
to find the file and open it without the user having to do anything.
Of course, I will need something should the user move the file or need to
change the file (it will change annually) that will ask if they want to
search for another file and make it the default file.
Any help would be appreciated!

The way I do this type of thing is to save the filename and path in the
Registry. VB has a special area in the registry at
"HKEY_CURRENT_USER\Software\VB and VBA Program Settings" and the
SaveSetting method and GetSetting function will put a string into a key
at this location and read from it. SaveSetting is used something
like:

SaveSetting "Application title", "My Key Name", "My Data Value Name",
MyString

and GetSetting is used like

MyString = GetSetting "Application title", "My Key Name", "My Data
Value Name"

Hope this helps.

Seiscons

if the file has been saved you can extract the path from the file name .I
paste this name into a couple of cells and manipulate it and use it in the
path,because different people save this file in different places.My main
macro calls this first


Sub printpath()
Dim myfull As String

myfull = ActiveWorkbook.FullName
Range("fullr").Formula = myfull
--
paul
remove nospam for email addy!



"seisman" wrote:

The way I do this type of thing is to save the filename and path in the
Registry. VB has a special area in the registry at
"HKEY_CURRENT_USER\Software\VB and VBA Program Settings" and the
SaveSetting method and GetSetting function will put a string into a key
at this location and read from it. SaveSetting is used something
like:

SaveSetting "Application title", "My Key Name", "My Data Value Name",
MyString

and GetSetting is used like

MyString = GetSetting "Application title", "My Key Name", "My Data
Value Name"

Hope this helps.

Seiscons