Save File to specific location and name with date

[HomePro]

I would like to save the file without being prompted at the end of my existing macro.
I would prefer:
A variable to preset the file name
A variable to preset the location
And then the current date.

So i tried :
Sub Save()
FN="Test1"
lc ="C:\documents"
DT= (today)
ActiveWorkbook.SaveAs filename:=LC & FN &DT ".xls"
End Sub

wont work.
Any ideas?

 

[Joe4]

Try this:

Sub MySave()

    FN = "Test1"
    LC = "C:\Temp\"
    DT = Format(Date, "mm-dd-yyyy")
   
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=LC & FN & DT & ".xlsx"
    Application.DisplayAlerts = True

End Sub

Change the date format to whatever format you want (except it CANNOT have slashes in it!).

 

[Georgiboy]

Maybe something like:

Sub Save()
    Dim FileName As String
    Dim FileLocation As String
    Dim dte As String

    FileName = "Test1"
    FileLocation = "C:\Users\jbloggs\Documents\"
    dte = Replace(Date, "/", "-")
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileLocation & FileName & dte, 51
    Application.DisplayAlerts = True
End Sub

Need to change the jboggs bit to your username Or specify a different path.

IMO: You should try to declare those variables as it will save you time finding errors in the future. Also worth making the variable name meaningful - as in when you look back in 4 years you don't want to have to decipher your own code.

 

[rlv01]

If you really want to save to the older .xls format.

Sub Save()
    FN = "Test1"
    LC = "C:\documents"
    DT = "_" & Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)
    FilePath = LC & "\" & FN & DT & ".xls"
   
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlExcel8
    Application.DisplayAlerts = True
End Sub

Note that the above will save your current file to the new name. If you just want to save a copy of the current file,while keeping the current file open, it gets a bit more complicated.

 

[Joe4]

IMO: You should try to declare those variables as it will save you time finding errors in the future. Also worth making the variable name meaningful - as in when you look back in 4 years you don't want to have to decipher your own code.

And you should NEVER, NEVER, NEVER used reserved words like "FileName" as names for your variables (I cannot stress that enough).
That is just asking for trouble (errors/unexpected results), especially since "FileName" is already one of the arguments of "SaveAs".

Workbook.SaveAs method (Excel)

Office VBA reference topic

docs.microsoft.com

.

Reserved words are words Excel already using for other things, like Functions, Objects, Properties, arguments for other commands, etc.
If you are sure not if a word is already being used by Excel or not, one trick that often works is to type the name in lower case in any Sub like this:

Sub test()
    filename
End Sub

If Excel then automatically capitalizes it like this:

Sub test()
    Filename
End Sub

then don't use it for a variable name (or Function or Procedure name either).

To avoid issues, I often just preface variables with the word "My", i.e. "MyFileName".

 

[Georgiboy]

You are absolutely right, I typed the code up in about 1.5 minutes so was not thinking. This is my mistake but my point still stands.

Seen so many errors due to undeclared variables. Difficult to find errors with variables accepting multiple types etc...

 

[Joe4]

One

You are absolutely right, I typed the code up in about 1.5 minutes so was not thinking. This is my mistake but my point still stands.

Seen so many errors due to undeclared variables. Difficult to find errors with variables accepting multiple types etc...

No I agree wholeheartedly.
As matter as fact, I highly recommend turning on the "Option Explicit" option, which forces you to declare all your variables: Option Explicit in Excel VBA

 

[Georgiboy]

One

No I agree wholeheartedly.
As matter as fact, I highly recommend turning on the "Option Explicit" option, which forces you to declare all your variables: Option Explicit in Excel VBA

I wish that option was default for the application, I will try and slow down a bit and look at my V names.

I am off on holiday for two weeks from now so I fear I am in full holiday mode.

 

[HomePro]

Thank you guys - the codes work great.
However I ran into an unexpected issue - the file location.
I thought I had a UNC path for it but it turns out to be an internet path.
Can we still make this work?


LC="https://workplac.com/#workdrive/hom...folders/rdj8id0786001aa5c4d2ba095f37928e04cf0"

 

[rlv01]

I thought I had a UNC path for it but it turns out to be an internet path.
Can we still make this work?

If you are able to manually save to that path, then yes. If not, no.