macro to bold EndDate and use it in the File name

Phillip2

Board Regular
Joined
Aug 5, 2019
Messages
79
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
14,165
Office Version
  1. 2007
Platform
  1. Windows
I need the original routine to see how to return the variable.

Or just add these lines to your original routine.

VBA Code:
  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"
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Phillip2

Board Regular
Joined
Aug 5, 2019
Messages
79
Office Version
  1. 365
Platform
  1. Windows
' ------ 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

Well-known Member
Joined
Dec 3, 2018
Messages
14,165
Office Version
  1. 2007
Platform
  1. Windows
Try this way:

VBA Code:
' ------ 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

Board Regular
Joined
Aug 5, 2019
Messages
79
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
14,165
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
' ------ 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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,165
Office Version
  1. 2007
Platform
  1. Windows
Again with pleasure. Thanks for the feedback
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,843
Messages
5,766,740
Members
425,377
Latest member
MohdTareq007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top