Super simple macro to save with date, but prompt for the rest of the file name?

[MikeyW1969]

Hi all,
I'm trying to set up a basic document for when I purchase IT supplies. Everything is pretty simple, and I don't need macros for this, except that it would be kind of nice to have it auto-save the file every time in the same format I've been trying to use.

Here's an example of the filename format: Office Cabling and Supplies_Nov 29_2017
And I'll be saving it in my 'My Documents' folder, in a folder called 'Purchasing'.

So how do I set it up to prompt for the first part of the name(Office Cabling and Supplies), but then auto-append the date? I'm guessing that this one is pretty basic, I just seem to be missing that one thing...

 

[cerfani]

something like...

Workbook.SaveAs(InputBox("Filename?")&"_"&YEAR(NOW())&"_"&MONTH(NOW())&"_"&DAY(NOW())&".xlsx")

I forgot but you should google how to pull the year, day and month seperately and then you just concatenate it all. My code above is pseudo code.

edit: Oh and you will also need to prepend the directory...

Workbook.SaveAs("C:\wherever\"&InputBox("Filename?")&"_"&YEAR(NOW())&"_"&MONTH(NOW())&"_"&DAY(NOW())&".xlsx")

 

[MikeyW1969]

That worked. I was getting an "Object Required 424" error(In case anyone else tries this), and I had to change Workbook.SaveAs to ActiveWorkbook.SaveAs and that did it. Thank you!

 

[cerfani]

OK great, yeah my code was pseudo, I wrote from memory basically, without referencing the proper functions. Pseudo code is just used to transmit an idea rather than the exact character for character solution.

Glad it worked out!

Also the code I wrote does not handle any unexpected input so that can cause errors if someone presses cancel or types in characters not permitted by windows to be used for filenames so it would need error handling if other users would run this code. Or a warning to not use special character, etc. The design is up to you but just fyi.