Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
same workbook different paths (home/work)
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. -- jeff ... I used to have a handle on life, but it broke. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
same workbook different paths (home/work)
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 -- Regards, Tom Ogilvy "jeffP" wrote in message ... 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. -- jeff .. I used to have a handle on life, but it broke. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
same workbook different paths (home/work)
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. -- Regards, Tom Ogilvy "jeffP" wrote in message ... 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. -- jeff .. I used to have a handle on life, but it broke. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
same workbook different paths (home/work)
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. -- Regards, Tom Ogilvy "jeffP" wrote in message ... 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. -- jeffP "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy "jeffP" wrote in message ... 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. -- jeff .. I used to have a handle on life, but it broke. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Work with the home key. | Excel Discussion (Misc queries) | |||
work from home | Excel Discussion (Misc queries) | |||
How do I transfer the autocorrect options from work to home? | Excel Discussion (Misc queries) | |||
How do I take a toolbar created at home and install it at work? | Excel Discussion (Misc queries) | |||
another language in MS office at home and another at work | Excel Worksheet Functions |