Use current file path to indetify folder location

[daren.beaney]

Hi, I have this code that works fine for majority however the way people access the folder from different offices is causing an issue. Basically if I use the direct path as shown in the code, it doesn't work, so I wondered if there is a way for the code to identify current path and then place the backup copy of the file into the Archive folder, which is a folder in the current file location.

Hope that makes sense.

Here's my current code:

ThisWorkbook.SaveCopyAs "F:\Adfact\Adelphi Modernisation\Business Change & Comms\01 Stakeholders\Archive\" _
& Replace(ThisWorkbook.Name, ".xlsm", " (" & Environ("USERNAME") & Format(Now, "dd-mm-yy hhmm") & ").xlsm")

Thanks in advance.

 

[ukmikeb]

Hi

Try:-

wbPath = ActiveWorkbook.Path

hth

 

[daren.beaney]

Hi

Try:-

wbPath = ActiveWorkbook.Path

hth

Thanks for this, how would I incorporate it into my line with the current path in it but just including the Archive as the final destination to save the backup copy to?

Thanks.
Daren

 

[ukmikeb]

Perhaps :-

wbPath = ThisWorkbook.Path
ThisWorkbook.SaveCopyAs wbPath & "\Archive\" _
& Replace(ThisWorkbook.Name, ".xlsm", " (" & Environ("USERNAME") & Format(Now, "dd-mm-yy hhmm") & ").xlsm")

NB I have changed the reference from ActiveWorkbook to ThisWorkbook for continuity.

hth

 

[daren.beaney]

Perhaps :-

wbPath = ThisWorkbook.Path
ThisWorkbook.SaveCopyAs wbPath & "\Archive\" _
& Replace(ThisWorkbook.Name, ".xlsm", " (" & Environ("USERNAME") & Format(Now, "dd-mm-yy hhmm") & ").xlsm")

NB I have changed the reference from ActiveWorkbook to ThisWorkbook for continuity.

hth

Thanks Mike that worked a treat!