Use Current directory to store file

  • Thread starter Thread starter Paul Moles
  • Start date Start date
P

Paul Moles

In an earlier post I found a save as date macro:
(Thanks to Bob Phillips)

Sub SaveWithDate()
ActiveWorkbook.SaveAs Format(Date, "dd-mmm-yy")
End Sub

I also have

Sub SaveAs_Range()
ActiveWorkbook.SaveAs Filename:=Range("A1")
End Sub

Unfortunately they both default to saving in the user
default file location, Tools Options General setting.
Which for convenience may have been set to "My Documents",
with users navigating within.

Rather than from whence they came. c:\wherever\ might
even be d:\my documents\operator1\here ever\

I don't want to create a directory on the user machine, or
know that it already exists.

How do I get the macro to default to the source directory.

It is unlikely other files would be open at the same time,
polluting the "save as" path, but not impossible.
The file will be circulated allowing users to store in
their normal location. Following data entry, at end of
week an update will be stored in the same folder, both
macros give a new filename knowing the old file will not
be overwritten.
 
If the file is saved ones you can use this

ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Format(Date, "dd-mmm-yy")
 
Back
Top