how can i save workbook as previous date in vba?

hi,
how can i create a macro to save the workbook as previous date?

i.e today is October 5 2013. I want to save the workbook as October 4 2013.xlsx

but dont want weekends included.
so ie. if it's a monday (when running the macro), use friday's date.
eastsidemarketAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
…deleted...
0
Martin LissOlder than dirtCommented:
Try this.


Dim strName As String

Select Case Day(Now)
    Case 2 To 6 ' Tuesday to Saturday
        strName = Format(Now - 1, "mmmm d yyyy")
    Case 7 ' Sunday
        strName = Format(Now - 2, "mmmm d yyyy")
    Case 1 ' Monday
        strName = Format(Now - 3, "mmmm d yyyy")
End Select

Open in new window

0
eastsidemarketAuthor Commented:
sorry, my needs have changed. how do i save the workbook as the current date in the format similar to: "October 11 2013"

Thanks.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Martin LissOlder than dirtCommented:
You'll need a macro, but if you save the workbook as an xlsx file all code including the macro will be deleted so you'll need to save it as an xls or xlsm file. This saves it as an xls with today's date.


      ActiveWorkbook.SaveAs Filename:=Application.Path & " " & Format(Now, "mmmm d yyyy") & ".xls", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False

Open in new window


But if you still want to subtract 1 then let me know.
0
eastsidemarketAuthor Commented:
good point. i will save as .xlsm.

where does this save the file? how can i designate the path?
0
Martin LissOlder than dirtCommented:
Do you want to choose the path or do you want to hard-code it?
0
Martin LissOlder than dirtCommented:
I should answer your question. The code above saves it to the application's current folder.
0
eastsidemarketAuthor Commented:
hardcode the path. right now i have no idea where its saving to. so yes, i want it hard coded.

thanks.
0
Martin LissOlder than dirtCommented:
Save to current path
    ActiveWorkbook.SaveAs Filename:=Application.Path & " " & Format(Now, "mmmm d yyyy") & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
 
Save to some other place    

Change "C:\MyPath\" to whatever you want

    ActiveWorkbook.SaveAs Filename:="C:\MyPath\" & " " & Format(Now, "mmmm d yyyy") & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Sorry I was rushing. These have two small changes.

Save to current path

    ActiveWorkbook.SaveAs Filename:=Application.Path & "\" & Format(Now, "mmmm d yyyy") & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Save to some other place    

Change "C:\MyPath\" to whatever you want  
     
    ActiveWorkbook.SaveAs Filename:="C:\MyPath\" & Format(Now, "mmmm d yyyy") & ".xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
0
eastsidemarketAuthor Commented:
nice work! thanks!
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2013
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.