same workbook different paths (home/work)

[jeffP]

HI,
I have a few workbooks that I use at work and at home. They use other files
through code to import data / export data.
My problem is the paths in the code. At home I may be working in C:\my
documents\MIS\Invoices, retrieving a file from C:\Data\finance\mydir ,
exporting finished data to C:\WorkDocs\xyzcarsales\2004 and saving a copy of
the workbook in D:\Backups\work\etc
When I move the worksheet to my work computer I must go into the code to
change all these paths. Then someone wants to put it on a laptop and all the
paths there must be changed there too! This invariably is done wrong and
causes 'run time error 1004' when calling for the files.
Obviously, setting up the drive/directory paths to match on all three
would help. Maybe just having to search and replace the drive letter would
be simpler and less error prone but.... what if someone else wants to use it
on another laptop, etc. I imagine this is a fairly common thing and
wondered how experienced people handled it.

Any help is always appreciated.

 

[Tom Ogilvy]

Prompt for the path (perhaps store it in the registry so this is only done
once)
search for the file (slow)
Use a case statement for a finite number of computers - use the computer
name - Environ("computername")
- or maintain a database in the application, prompting for the
information the first time on each computer

 

[Tom Ogilvy]

You can also use paths relative to the location of the App

ThisWorkbook.Path

but that didn't look applicable to the situation you showed.

 

[jeffP]

Tom,
I ended up using thisworkbook.path and moving as much as possible into the
one directory. I 'll probably try using a case statement for the others when
I get a chance.
I didn't really understand this suggestion >>>- or maintain a database in
the application, prompting for the
information the first time on each computer<<<
If you wouldn't mind elaborating on how to do this I would appreciate it.

As always, I appreciate you time and help.

 

[Tom Ogilvy]

in a worksheet in the workbook maintain the computer name and the
appropriate paths for that computer. Your code would check for this
information and if not found, put up an inputbox or userform to get the
information from the user, then write it to the cells.