Saving workbook to specific folder with a name based on info in a cell + today's date.

[jonvanwyk]

The code below that I am using takes a person's name in cell B6 and combines it with today's date which is located in B16. The problem is, although B16 is formatted as "mmddyyyy - hhmm" in cell B16, it tries to save it as "mm/dd/yyyy - hh:mm", which is of course not a valid file name. I don't really care if we use B16, I just want a "- today's date" to be in the filename after the person's name found in B6. Thoughts?

Please Login or Register  to view this content.

[Authentik8]

Try

Please Login or Register  to view this content.

[ChuckYeager]

Instead of just using - Range("B16").Value

use Format(Range("B16").Value,"MMDDYYYY - hhmm")

I hope it should work

[jonvanwyk]

Please forgive me, as I have done a poor job of explaining the result I am looking for. Below is an example of the path of the folder that the file needs to save itself to upon clicking on the "Save" macro button:

G:\folder1\folder2\folder3\2012\JUL\Doe,John 07-23-12.xlsm

Whereas "B6" contains the name "Doe,John"; upon clicking of the "save" button, the file should know to automatically save itself as "Doe,John [Today's date].xlsm" in the appropraite folder of the year and month. I do not really care what is used for the name of the month, beit July, JUL, 07, or 7; as long as it is clear that it is the current month. So, the code needs to know what today's date is to save to the correct folder that contains the year and month of when the button was clicked.

Thanks in advance for any further assistance that comes my way.

[jonvanwyk]

I am using the following code, which does not yet work. When you click on "save" you get the text from the msgbox code saying you successfully saved the file, and where it says it saved it appears to be the correct path. However, when you go to the folder that is supposed to contain the saved file, it cannot be found. I have also attached a copy of the workbook to this reply. Thoughts?

Please Login or Register  to view this content.

[JosephP]

probably because there is no actual saveas command in there... ;-)

[jonvanwyk]

oops.

Please Login or Register  to view this content.

[ChuckYeager]

You got it.

But your FileFormat should be 52
and the syntax FileFormatNum:=52 if I am not wrong, if you are using a 2007 version and as far as I understand 2003 did not have the ".xlsm" format.

The .saveas statement should appear as

Please Login or Register  to view this content.

That is if you are using Excel 2007 and you are trying to save the file as "Macro Enabled Excel File"

If you are using 2003

Please Login or Register  to view this content.

xlFileFormat Enumeration '50' does not seem to work for 2003.

[jonvanwyk]

We are actually using Excel 2010 now. I typically just make all of my forms in .xlsb format. It eliminates the compatibility issues and I haven't experienced a downside to doing so as of yet. I only saved the document as a macro enabled workbook for the purposes of posting a copy to this thread (the forum does not support uploading .xlsb files).