Excel VBA to save file to a defined location and defined name.

[minmark]

Dear Gents,

my code as following
Dim MyFile As String


MyFile = ActiveWorkbook.Name
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="D:\E-DRIVE2\1234567\Logbook\2016\" & MyFile

End sub

my current file name is "LogBook July"
My question are:
1) I would like to save this file name as LogBook +"month name" like "LogBook Aug", "LogBook Nov". How do i make this happen?

2) I want to make a inputbox to input the year to replace the save location "2016" and let the file save to 2017 folder if the year is over 2017.


I try to use formula like =month(today()) ... but can't figure out.

Hope anybody can help me.

Thanks.

 

[DaveBlakeMAAT]

Hi Mark

to get the short month just concatenate the following

Format(Month(Date), "mmm")

you can also use concatenation to automatically input the current year.

Year(Date)

Therefore try

[COLOR=#333333]MyFile = ActiveWorkbook.Name & " " & [/COLOR]Format(Month(Date), "mmm")
[COLOR=#333333]Application.DisplayAlerts = False[/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:="D:\E-DRIVE2\1234567\Logbook\" & Year(Date) & "\" & MyFile
[/COLOR]

I hope this helps


Regards

Dave

 

[MrManBoy]

Hopefully, this may help as well.

Sub FILESAVE()


Dim dt As String, wbNam As String


    wbNam = "D:\Users\Documents\TEST_FILE_" 'FILE DIR PATH AND NAME
    dt = Format(CStr(Now), "MMM_YYYY") 'MONTH & YEAR
    ActiveWorkbook.Sheets.Copy 'CREATES COPY OF WORKBOOK IF WB IS MACRO-FREE OTHERWISE CAN IGNORE THIS LINE"
    ActiveWorkbook.SaveAs Filename:=wbNam & dt, FileFormat:=51 '51 = XLSX FORMAT; UPDATE FILE FORMAT AS PER REQUIREMENTS
    ActiveWorkbook.Close Savechanges = True
    
End Sub