macro to bold EndDate and use it in the File name

[Phillip2]

I have three reports that have start and end dates listed inside the last line of the report. I want to add a macro to my sub routine that will to bold the EndDate to draw attention to it. (See example below)

(Report1)-----Filter Resource Group Names: DF-Clinic; Interval[Ending At]: 12/09/2019 23:59:59; Team Names: DF-Clinic; Interval[Starting At]: 12/09/2019 00:00:00
(Report2)-----Filter Interval[Ending At]: 12/12/2019 23:59:59; Interval[Starting At]: 12/12/2019 00:00:00; CSQ Names: DFClinicCSQ; Interval Length: Thirty(30) Minutes
(Report3)-----Filter Interval[Starting At]: 12/09/2019 00:00:00; Interval[Ending At]: 12/09/2019 23:59:59; CSQ Names: DFClinicCSQ


Then I want to insert this date into the name when I save it. There are a couple of additional problems. The macro that bolds the EndDate is called from the Sub Routine that will save it. And this date uses slashes (/) which will need to be changed to dashes (-). I’m not even sure where to start with this macro. I’ve started the one to bold the date.

Thank you, I really appreciate your help.


Below is part of the first Sub Routine that calls the formatting routine and then saves the excel file to the appropriate folder.



'------------------Calls Sub to Format the Agent Call Summary Report-----------------------------

Call formatACS_Report 'Process Report


'----------Saves the Active Workbook inside the appropriate folder ------------------------------


'-------------------I want to insert EndDate into saved name such as ------12-09-2019 Contact Service Queue Activity Report.xlsx---------------------------


ActiveWorkbook.SaveAs "X:\Telecom\CallReports\PrimaryCare Mail\Agent Call Summary Report.xlsx"
ActiveWorkbook.Close

---------------------------------------------------------------------------------------------------------------------------------


Below is the macro located in the formatting routine that I started which bolds the EndDate

………
………
………

'Find and bold the EndDate located in last row of column A

Dim DateRow As Long
Dim EndDate As Long

'Find the last row in column A

DateRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row


'Find the End Date listed in the text

????

'Bold the End Date

ActiveSheet.Range("EndDate").Font.Bold = True

 

[DanteAmor]

To understand, you have this:

And you want this:

Additional, do you want to get the date 12/09/2019 and change it to 12-09-2019 to be able to save the file with that data?

 

[Phillip2]

Yes, I just want a quick way that the customer can glance and see the date of an opened report .

 

[DanteAmor]

After this text: [Ending At]: is there always a date?
Do you want it for all the rows in column A?
And of all the dates that appear after the text [Ending At] :, which one do you want in dd-mm-yyyy format?

 

[Phillip2]

The last row is always the same except for the times and dates. I was planing on just using one of the dates in the last line. This way it could be assigned to a variable and used to add the date to the file name when saved.

 

[Phillip2]

I want to use the same date in dd-mm-yyyy format in the name of the saved file.

 

[DanteAmor]

Try this

Sub test14()
  Dim s As Variant, v As String
  With Range("A" & Rows.Count).End(xlUp)
    s = InStr(1, .Value, "[Ending At]") + 13
    .Characters(Start:=s, Length:=10).Font.FontStyle = "Bold"
    v = Format(Mid(.Value, s, 10), "dd-mm-yyyy")
  End With
  MsgBox v
End Sub

 

[Phillip2]

Thanks Dante, That works perfectly. The only problem that I have is inserting the variable into the date.


'----------Saves the Active Workbook inside the appropriate folder ------------------------------

ActiveWorkbook.SaveAs "X:\Telecom\CallReports\PrimaryCare Mail\Agent Call Summary Report.xlsx"
ActiveWorkbook.Close

Application.ScreenUpdating = True


I tried ActiveWorkbook.SaveAs "X:\Telecom\CallReports\PrimaryCare Mail\"v"Agent Call Summary Report.xlsx"
but it didn't like it.

 

[DanteAmor]

If you put your full macro, maybe you could get better help.
If the macro is in the same book you are trying to save, it is not possible to save with the extension "xlsx".
So I will have to assume that the active book is another that does not contain the macro. Then try this:

Sub test()

  Dim s As Variant, v As String
  With Range("A" & Rows.Count).End(xlUp)
    s = InStr(1, .Value, "[Ending At]") + 13
    .Characters(Start:=s, Length:=10).Font.FontStyle = "Bold"
    v = Format(Mid(.Value, s, 10), "dd-mm-yyyy")
  End With
 
  ActiveWorkbook.SaveAs "X:\Telecom\CallReports\PrimaryCare Mail\" & v & "Agent Call Summary Report.xlsx"

End Sub

But if the macro is in the book you are trying to save then try this:

Sub test()

  Dim s As Variant, v As String
  With Range("A" & Rows.Count).End(xlUp)
    s = InStr(1, .Value, "[Ending At]") + 13
    .Characters(Start:=s, Length:=10).Font.FontStyle = "Bold"
    v = Format(Mid(.Value, s, 10), "dd-mm-yyyy")
  End With
 
  ActiveWorkbook.SaveCopyAs "X:\Telecom\CallReports\PrimaryCare Mail\" & v & "Agent Call Summary Report"

End Sub

 

[Phillip2]

Dante,
Thanks again for all of your help. I'm still having an issue. Allow me to explain. I have a sub routine that is calling the sub routine that you just wrote. However, the line
"----ActiveWorkbook.SaveCopyAs "X:\Telecom\CallReports\PrimaryCare Mail\" & v & "Agent Call Summary Report"----" in inside the original Sub Routine. I believe that the reason it isn't working is the variable isn't returning back. The little routine is being used for several reports is it possible to do what I'm asking?

By the way, my idea has evolved over time. At one point it occurred to me that I really didn't need need for the macros to be inside of the workbooks, to accomplish what I needed. I just wanted to format the worksheet. So that is why I am saving them as a "xlsx" file. I have a huge amount of code in these and this just cleans them up nicely.