Force save to CURRENT directory

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All...........

This code basically works, but saves to the "Excel default" directory rather
than to the directory the file came from......I want both saves to go right
back in to the same directory the file came from.......wherever that might
be. How can I force this please?

Sub NewSaveArchive()
CurrentPath = CurDir
archivepath = CurrentPath + "\" 'Default Directory
WorkBookName = ActiveWorkbook.Name
Fname = archivepath
Fname = Fname + Worksheets("INFO").Range("AH8").Value
Fname = Fname & Format(Time, "_hh_mm_ss") & Format(Date, "_Mmm_dd_yyyy")
Sheets("StaffingMatrixGF").Select
Range("a1").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Fname
Fname = CurrentPath + "\" + WorkBookName
ActiveWorkbook.SaveAs Fname
Application.DisplayAlerts = True
End Sub

Vaya con Dios,
Chuck, CABGx3
 
A little more info.......

If I open Excel first, and then open this file, this existing code will do
the saves to the current directory just like I want......BUT, if I open both
Excel and the file by just clicking on the filename in Windows Explorer, or a
Desktop Icon, (the preferred method), then is when Excel changes both saves
back to the default "my docments" directory specified in Tools >
Options............

Any help would be appreciated......

Vaya con Dios,
Chuck, CABGx3
 
Just use ActiveWorkbook.Path to get the save location. Something
like:
CurrentPath = ActiveWorkbook.Path
Fname = CurrentPath & "\" & Worksheets("INFO").Range("AH8").Value
ActiveWorkbook.SaveAs Fname
 
Fine.....FINE........SUPERFINE!!!
Thank you very much kind Sir.

Vaya con Dios,
Chuck, CABGx3
 
Fine.....FINE........SUPERFINE!!!
Thank you very much kind Sir.

Vaya con Dios,
Chuck, CABGx3






- Show quoted text -
Click to expand...

try this;
ThisWorkbook.Activate
FilePath = Left(thisworkbook.FullName,Len(thisworkbook.FullName)-
Len(thisworkbook.Name))

This should save the active workbook file directory to FilePath. Just
add the file name.

Regards
trevosef
 
Thanks for the response trevosef, but I have already used JW's suggestion,
which worked ok for me in this application.

Vaya con Dios,
Chuck, CABGx3
 
Back
Top