JeffT
02-26-2012, 11:30 AM
Hi
I'm trying to open a file (Susa Charts.xls) which is in the same folder as the open file (Susa Spreadsheet). When in use it could be anywhere on any computer but will be stored in the same folder. I'm looking to use:-
Dim Path As String
Path = ActiveWorkbook.FullName
to get the path to the spreadsheet. It shows something like:-
C:\Documents and Settings\My Documents\Work\Susa Spreadsheet.xls
Then cut the name "Susa Spreadsheet.xls", though this name may have changed, so want to cut it back to the first "\". I can then use "&" to add back the name "Susa Chart.xls" to the Path so it will open using
Workbooks.Open (Path)
Path = C:\Documents and Settings\My Documents\Work\Susa Charts.xls
It may be that the "Susa Chart" name may change but I'll cross that bridge later. I'll put in a file selection dialog box if the path doesn't work.
I think I need something like Trim & Len but have searched and can't find it working with File paths. Perhaps this is not the best way to do it but it's the only way i can think of. If there's an easier way of opening a file in the same folder I'll be happy
Hope you can help
Thanks in anticipation
Jeff
I'm trying to open a file (Susa Charts.xls) which is in the same folder as the open file (Susa Spreadsheet). When in use it could be anywhere on any computer but will be stored in the same folder. I'm looking to use:-
Dim Path As String
Path = ActiveWorkbook.FullName
to get the path to the spreadsheet. It shows something like:-
C:\Documents and Settings\My Documents\Work\Susa Spreadsheet.xls
Then cut the name "Susa Spreadsheet.xls", though this name may have changed, so want to cut it back to the first "\". I can then use "&" to add back the name "Susa Chart.xls" to the Path so it will open using
Workbooks.Open (Path)
Path = C:\Documents and Settings\My Documents\Work\Susa Charts.xls
It may be that the "Susa Chart" name may change but I'll cross that bridge later. I'll put in a file selection dialog box if the path doesn't work.
I think I need something like Trim & Len but have searched and can't find it working with File paths. Perhaps this is not the best way to do it but it's the only way i can think of. If there's an easier way of opening a file in the same folder I'll be happy
Hope you can help
Thanks in anticipation
Jeff