Solved: Change file path name

[JeffT]

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

[mdmackillop]

[VBA]Pth = ActiveWorkbook.Path[/VBA]

[JeffT]

Thanks mdmackillop

I knew it'd be simple. I need to practise more

Best wishes

Jeff

[mdmackillop]

Happy to help. Also, look at the Split function. Very useful for extracting data from such strings.

[JeffT]

Yes had a quick look in VBA help thanks. The problem is always knowing what to look for.

Just trying to find out how to set the thread to solved. I used to know how but haven't been here for a while. Pity there's not a button.

[mdmackillop]

It's under Thread Tools if you don't use Chrome.

[JeffT]

I was in Chrome, switched to IE thanks