Use Date In File Name to Enter Date in Cell

Hi Y'all

Someone said that this can't be done! I said that he does not know what the guys and girls on the Excel Forum can do with Excel.
I have a file that is a template. Each day I need to save the template as the following file: "Summary of Shop Daily Charges- 16 October 2016.xls", the next day, "Summary of Shop Daily Charges- 17 October 2016.xls", etc.
In the file there is a cell where the date needs to be entered. I can be a bit scatterbrain sometimes and forget to change the date. So, can you help me build a Macro that when I rename and save the file with the new date that the Macro will pick up the date and enter it into the Cell?

Please prove that person wrong!!!!!

Many thanks

Patish

Put this in the WORKBOOK module:

Please Login or Register  to view this content.

Originally Posted by Olly

Put this in the WORKBOOK module:

Please Login or Register  to view this content.

Hi Olly

Thanks for the code, but this is a bit above what I am used to doing. I do not save the file by using a Macro. What I normally do with the template file is to Save As, find the file with the last date, click on the name, change the date from 16 to 17, let say, and hit Save. How would I put that into Code and add the the code that you built?

Thanks

Patish

Hi Olly

Could not quite make heads or tails of how to adapt your macro to my spreadsheet.. I have attached the file with the Macro according to what I think it should be. Horribly wrong because it does not work. Can you help me please?

Also, is it important what the date format should be in this Macro? For example, dd/mmm/yyyy, dd mmmm yyyy, and so on?

Thanks

Patish

If you want a non-VBA solution, you can use the =CELL() function to return the filename https://support.office.com/en-us/art...f-955d67c2b2cf Then using the different text functions [=MID() ], you can pull out the date information from the resulting text string.

Many thanks, John. Your second solution was a success. Did not understand why the first one had a message box. Am I correct in saying that the date placed in the cell is text? Any way it can be changed to a value in the Macro?

Thanks

Brian

John

That clinched it. Works like a well tuned engine. Many thanks.

I hope the elections on November 8 goes just as well!!!!!

Patish

Hi JOHN H.

Can you help me please? Your code works beautifully when the date in the file name is 25 June 2018.

I have changed the date that appears in my file name to 25062018 and, using your code there is an error. It points to the last line

.value= .value.

Is there code that can take into account any changes in date formats in a file name? Or is there a rule that I can use if I need to change the date format?

Many thanks

Patish