Save As with a date in the file name

[zombiemaster]

Good morning, All...

I know how to save a file using VBA with a date in the file name based on the day of the week, but now I need to save a file using a date that is sitting in a particular cell in the worksheet...

How do I do a Save As and use a date in cell B1 as part of the file name? For example, I want the file name to be:

CASH REPORT 02-15-2019

Based on the cell content of B1 showing as 2/15/19 and formatted as either text or date (I can change the formatting as needed if necessary).

Thanks for any help!
~ZM~

 

[zombiemaster]

Looking for some additional help on this. The VBA opens the last report, which is a Shared workbook (I know, I know – not the best idea but it’s what Corporate wanted):

ChDir "\\SERVER\Cash Report"
    Workbooks.Open filename:="\\SERVER\Cash Report\DAILY CASH REPORT.xlsx"

It then makes the file non-shared:

ActiveWorkbook.ExclusiveAccess

Then the code performs some basic stuff like find/replace as well as a text-to-column and deleting of some unnecessary ‘garbage’ data.

At this point, I need to re-save the file in the Archive folder:

Dim Path As String
Dim filename As String
Path = "\\SERVER\Cash Report\Archives\"
filename = Range("A1")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx"

ActiveWindow.Close

The file shows up in the Archive fine and has the right name in the file name based on whatever is sitting in cell A1, just like I had hoped…the rest of the code runs fine as well. But NOW, the archived file won’t open. I get a message of:

“Excel cannot open the file ‘CASH 3-8-2019.xlsx’ because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.”

Hoping someone here might know what the problem might be?

Thanks as always for your help! I’m leaving work for the day right now but will be back tomorrow if anyone has any ideas…thank you!
~ZM~

 

[zombiemaster]

Hoping this reaches someone - been a few days with no responses, so trying again. I have a manual workaround that I've been doing for a couple of weeks until I can get this to work correctly.

Thanks for anyone who might be able to assist!

~ZM~