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~

 

[steve the fish]

Heres an example:

ThisWorkbook.SaveAs ThisWorkbook.Path & "\CASH REPORT " & Format(Sheets("Sheet1").Range("B1"), "mm-dd-yy") & ".xlsm"

 

[zombiemaster]

Thanks for the response, Steve! I'm not familiar with the "ThisWorkbook.saveAs ThisWorkbook.Path" piece of what you gave me...so if I need to take the original file in location "ABC" and save it to an sub-folder location in "ABC\Archive" with the date, how would I state that? Right now, I save it as the previous workday's date in the archive folder and then go in manually and change the date to the "real" date based on the date in cell B1. Here's the code I use for that, if it helps...

Application.DisplayAlerts = False  ' turns off all alerts
    ChDir "\\SERVER\Cash Report"
    Workbooks.Open Filename:="\\SERVER\Cash Report\CASH DAILY REPORT.xlsx"
Select Case WorksheetFunction.Weekday(Date)
    Case 1
        X = 3
    Case 2
        X = 4
    Case Else
        X = 2
End Select
    ChDir "\\SERVER\Cash Report\Archives"
    ActiveWorkbook.SaveAs Filename:="\\SERVER\Cash Report\Archives\CASH TESTING" & Format(Date - X, "mm-dd-yyyy"), FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, AccessMode:=xlShared

Thanks again,
~ZM~

 

[steve the fish]

See if this works ok:

Dim fpath As String, fname As String, wb As Workbook, x As Long

fpath = "\\SERVER\Cash Report\"
fname = "CASH DAILY REPORT.xlsx"

Set wb = Workbooks.Open(Filename:=fpath & fname)

Select Case WorksheetFunction.Weekday(Date)
    Case 1
        x = 3
    Case 2
        x = 4
    Case Else
        x = 2
End Select

wb.SaveAs fpath & "Archives\CASH TESTING " & Format(Date - x, "mm-dd-yyyy") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, AccessMode:=xlShared

 

[zombiemaster]

Hi, Steve - sorry for the late response. I got pulled off this project for a few days but am back now.

I tried to combine both of your responses, since it looked like your first response was answering my question about how to save the file using a date in cell B2, and your second response modified how I was opening the file by using fpath and fname instead of ChDir and Workbooks.Open Filename which I thought was cool, and then saving it using wb.SaveAs fpath.

So at this point, this is what my code looks like:

Dim fpath As String, fname As String, wb As Workbook, x As Long

fpath = "\\SERVER\Cash Report\"
fname = "DAILY CASH REPORT.xlsx"

Set wb = Workbooks.Open(Filename:=fpath & fname)

ThisWorkbook.SaveAs ThisWorkbook.Path & "Archives\CASH REPORT " & Format(Sheets("Sheet1").Range("B1"), "mm-dd-yy") & ".xlsm"

I'm getting a Run-time error '9': Subscript out of range message when it reaches ThisWorkbook.SaveAs...

I also tried replacing that line of code with this, but it gave me the same error:

wb.SaveAs fpath & "CASH TESTING " & Format(Sheets("Sheet1").Range("B1"), "mm-dd-yy") & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False, AccessMode:=xlShared

I'm sure I am just missing something simple - any ideas what it might be?

Thanks as always!
~ZM~

 

[zombiemaster]

Hi again - just following up on this, in case anyone has any ideas on what I may be doing wrong...still not working and getting the run time error...

Thanks in advance!
~ZM~

 

[steve the fish]

You would need a slash in here:

ThisWorkbook.Path & "Archives\CASH REPORT "

change to:

ThisWorkbook.Path & "[COLOR=#ff0000]\[/COLOR]Archives\CASH REPORT "

 

[zombiemaster]

I was still getting the same error after including that backslash before the folder name:

ThisWorkbook.SaveAs ThisWorkbook.Path & "\Archives\CASH REPORT " & Format(Sheets("Sheet1").Range("A1"), "mm-dd-yy") & ".xlsm"

So, since I assume I was doing something wrong somewhere, I decided to do another search online for alternatives and found this:

Dim Path As String
Dim filename As String
Path = "\\SERVER\Cash Report\"
filename = Range("A1")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xls", FileFormat:=xlNormal

It looks like this is going to work! I was getting a new error because of the slashes in the date format, but did some fancy footwork on doing a text-to-column on the date (3/1/2019) to get three separate cells (3 1 2019) then recombining them with dashes (3-1-2019). Then I save it again as text and that allows the SaveAs to work.

So far, so good, but I might come back with more questions if I run into other issues...one step closer to fine...thanks for your help!

~ZM~

 

[steve the fish]

The format was there to format the date with dashes instead of slashes.

 

[zombiemaster]

Yeah, I saw that - that's why I knew I was doing something wrong, when it wasn't working as expected! No worries, I got what I needed and at the end of the day, that's all that matters!

Thanks again for pointing me in the right direction!
~ZM~