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]

I need the original routine to see how to return the variable.

Or just add these lines to your original routine.

  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"

 

[Phillip2]

' ------ Module 5 Primary Care Button 1 Processes Agent Call Summary Report ------------------------


'-----------Get Agent Call Summary Report and process it -------------------------------------

Sub ProcessACSReport()

Application.ScreenUpdating = False

With Workbooks.Open("X:\Telecom\CallReports\PrimaryCare\Agent Call Summary Report-Agent Call Summary Report.*.*")

Worksheets(1).Copy 'Copies sheet as a new workbook
.Close SaveChanges:=False

End With

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

Call formatACS_Report 'Process Report


'------------------Calls Sub to bold date-----------------------------------------------------------

Call BoldDate 'bold date


'----------Saves the Active Workbook inside the appropriate folder ------------------------------
Here is some of my coding.



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

Application.ScreenUpdating = True

'---------message box saying that job is complete -----------------------------------------------

MsgBox "Agent Call Summary Report has been processed", vbInformation, ""

End Sub




Sub BoldDate()
Dim s As Variant, vAs 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

End Sub

 

[DanteAmor]

Try this way:

' ------ Module 5 Primary Care Button 1 Processes Agent Call Summary Report ------------------------
'-----------Get Agent Call Summary Report and process it -------------------------------------
Sub ProcessACSReport()
  Application.ScreenUpdating = False
  With Workbooks.Open("X:\Telecom\CallReports\PrimaryCare\Agent Call Summary Report-Agent Call Summary Report.*.*")
    Worksheets(1).Copy 'Copies sheet as a new workbook
    .Close SaveChanges:=False
  End With
  '------------------Calls Sub to Format the Agent Call Summary Report-----------------------------
  Call formatACS_Report 'Process Report
  '------------------Calls Sub to bold date-----------------------------------------------------------
  Dim bold_v As Variant
  bold_v = BoldDate
  '----------Saves the Active Workbook inside the appropriate folder ------------------------------
  ActiveWorkbook.SaveAs "X:\Telecom\CallReports\PrimaryCare Mail\" & bold_v & "Agent Call Summary Report.xlsx"
  ActiveWorkbook.Close
  Application.ScreenUpdating = True
  '---------message box saying that job is complete -----------------------------------------------
  MsgBox "Agent Call Summary Report has been processed", vbInformation, ""
End Sub

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

 

[Phillip2]

Thank you Dante. That works great! However, can you explain the difference between calling a sub routine and using a function. It appears that you are triggering the function with the variable within the file name. Is this correct? Would I be correct is saying that a variable can't "travel" between sub routines? However, in ths case use were able to Dim the variable 'v' just before using the function.

Interesting, but a little confusing too. Again, thank you so much for your help, and I hope that you and yours have a Merry Christmas.

 

[DanteAmor]

One of the main differences is that the function returns a result. In this case the date returns.
The date data is stored in the variable bold_.

We could make "travel" a variable between sub routine.

We have to declare the variable as global. Try the following:

' ------ Module 5 Primary Care Button 1 Processes Agent Call Summary Report ------------------------
'-----------Get Agent Call Summary Report and process it -------------------------------------

  Dim bold_v As Variant 'at the beginning of the code


Sub ProcessACSReport()
  Application.ScreenUpdating = False
  With Workbooks.Open("X:\Telecom\CallReports\PrimaryCare\Agent Call Summary Report-Agent Call Summary Report.*.*")
    Worksheets(1).Copy 'Copies sheet as a new workbook
    .Close SaveChanges:=False
  End With
  '------------------Calls Sub to Format the Agent Call Summary Report-----------------------------
  Call formatACS_Report 'Process Report
  '------------------Calls Sub to bold date-----------------------------------------------------------
  Call BoldDate
  '----------Saves the Active Workbook inside the appropriate folder ------------------------------
  ActiveWorkbook.SaveAs "X:\Telecom\CallReports\PrimaryCare Mail\" & bold_v & "Agent Call Summary Report.xlsx"
  ActiveWorkbook.Close
  Application.ScreenUpdating = True
  '---------message box saying that job is complete -----------------------------------------------
  MsgBox "Agent Call Summary Report has been processed", vbInformation, ""
End Sub

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

 

[Phillip2]

Thanks for the explanation!

 

[DanteAmor]

Again with pleasure. Thanks for the feedback