If/Then Logic For VBA Save As functionality?

[mjd]

Hello,

I am working on a project in Excel 2010 that will automatically save a file using a specific naming protocol, based off the dates included on the spreadsheet.

Right now, I have the following code working perfectly for today's date

Sub Save()


Dim dtDate As Date
dtDate = Date


Dim strFile As String
strFile = "H:\Trade Files\" & Format(dtDate, "mm-dd") & " " & "Bank" & ".xlsx"


ActiveWorkbook.SaveAs Filename:=strFile, FileFormat _
    :=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = False

Now, what I'm hoping to do, is to include logic that if a certain cell [say B3 for this example] contains a date that is not equal to today's date, to instead name the file as "[date in referenced cell] As Of Trades - Bank.xlsx"

Simplifying matters is that the source report will never co-mingle data from multiple dates, so it will really be an either/or situation in terms of the file naming protocol.

I've searched high and low and haven't had much success finding any resource on conditional save-as functions. Is this even possible in excel vba?

Thank a million!

Mike

 

[Norie]

Mike

Can't you just set dtDate to the value in B3?

 

[mjd]

I could, the major issue for me is the difference in the text component "mm-dd Bank.xlsx" for today's data versus "previous mm-dd As-Of Trades Bank.xlsx" for a prior day's activity. Getting the if/then logic in there is what is tripping me up.

Thanks,
Mike

 

[Norie]

Perhaps.

Sub MySave()
Dim dtDate As Date
Dim strFile As String
Dim strPath As String

    strPath = "H:\Trade Files\"

    dtDate = Date

    If dtDate = Range("B3").Value Then
        strFile = Format(dtDate, "mm-dd") & " Bank.xlsx"
    Else
        strFile = Format(Range("B3").Value, "mm-dd") & " As Of Trades - Bank.xlsx"
    End If

    ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat _
    :=xlOpenXMLWorkbook, CreateBackup:=False

    Application.DisplayAlerts = False

 

[mjd]

Norie,

Thank works beautifully! Thank you so very much!

Mike

Perhaps.

Sub MySave()
Dim dtDate As Date
Dim strFile As String
Dim strPath As String

    strPath = "H:\Trade Files\"

    dtDate = Date

    If dtDate = Range("B3").Value Then
        strFile = Format(dtDate, "mm-dd") & " Bank.xlsx"
    Else
        strFile = Format(Range("B3").Value, "mm-dd") & " As Of Trades - Bank.xlsx"
    End If

    ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat _
    :=xlOpenXMLWorkbook, CreateBackup:=False

    Application.DisplayAlerts = False