Trying to run a macro every 30 minutes

Question

I've search up and down and cannot seem to find the answer to my question.

I'm trying to run a macro every 30 minutes. The workbook can be kept open but doesn't need to be. I DO have 3 other excel workbooks that MUST be kept open the whole day as this is for a hedge fund and they provide real time data using the bloomberg API. The macro I'm trying to run sends an email with a PnL update of our portfolios throughout the day. Running the macro on it's own works great. The macro is here:

Public Sub PnLUpdate()

Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range, cell As Range, HtmlContent As String, i As Long, j As Long
Set rng = Range("A1:M300")
HtmlContent = "<table>"

For i = 1 To rng.Rows.Count + 1
    HtmlContent = HtmlContent & "<tr>"
    For j = 1 To rng.Columns.Count + 1
        HtmlContent = HtmlContent & "<td>" & Cells(i, j).Value & "</td>"
    Next
    HtmlContent = HtmlContent & "</tr>"
Next
HtmlContent = HtmlContent & "</table>"


Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
    .To = "xx"
    .CC = "xx"
    .Subject = "PnL Update // " & Format(Now, "mm-dd-yy // hh:mm AM/PM")
    .HTMLBody = HtmlContent
    .Send
End With
On Error GoTo 0
Set OutMail = Nothing

End Sub

I've tried inserting

Application.OnTime Now + TimeValue("00:00:30"), "PnLUpdate"

Into said macro. It works and sends the email every 30 minutes. Issue is that it sends whatever the active sheet is at the time, not the sheet I need it to send.

I tried writing a module that looks like this:

Sub callPnLUpdate()

With Sheet10
Call .PnLUpdate
Application.OnTime Now + TimeValue("00:00:30"), "PnLUpdate"
End With
End Sub

This gives me the error "Cannot run the macro 'worksheetname.Xlsx!PnLUpdate'. The macro may not be available in this workbook or all macros may be disabled.

I tried searching for the solution to this issue but all the solutions say to enable programmatic access which I've already done and that doesn't work either.

To be clear: the solution to this problem does NOT have to be to use my macro. I will accept any solution which can send this specific spreadsheet every 30 minutes (preferably starting at 9:31am and ending at 4:05pm but that's less important than getting this automation to work every 30 minutes).

Any help is very much appreciated.

Thank you!

Answer 1

The key is to fully qualify the Workbook and Worksheet in any Range or Cells calls, otherwise, there's an implied ActiveWorkbook and ActiveSheet.

For instance:

• Set rng = Range("A1:M300") should be something like Set rng = ThisWorkbook.Sheets("PnL").Range("A1:M300"). Or if you prefer using the sheet code name, something like Set rng = Sheet10.Range("A1:M300").
• And again, HtmlContent = HtmlContent & "<td>" & Cells(i, j).Value & "</td>" should be HtmlContent = HtmlContent & "<td>" & rng.Cells(i, j).Value & "</td>". Otherwise Cells(i, j) refers to the cells on the active sheet.

Answer 2

I don't know if this will work for your specific circumstances, but whenever I need to run a macro at certain intervals, I setup the macro on a Workbooks_Open sub, then call that file from a batch file, and schedule that batch file with the Task Scheduler application on my PC.

So create the macro you need to run, and place it in the Workbooks section (not a module) with the following format:

Sub Workbook_Open()
'your macro goes here
End sub

Then make a batch file that will open your Excel document. Open Notepad and enter the following code. For the first part of the code, enter the path to your Excel.exe file. For the second part of the code, enter the path to your excel file. If your Excel file is on a network drive, you'll need to path it a little differently though.

"C:\Program Files\Microsoft Office 15\root\office15\EXCEL.EXE" "C:\Users\curtis\Dropbox\National Accounts Customer Database\MakeNewLog.xlsm"

Then save the Notepad document with the .bat file extension. I can't remember, but you might have to change a toggle in your file explorer to be able to do that (the toggle that shows you the file extensions of all your icons).

After you have the batch file, double click on it to verify that it works. If you did everything correctly, the batch file will open a CMD window which will call your Excel file. The Excel file will open, and your macro will automatically fire on the Workbooks_Open command.

Now just schedule the batch file to run on the Task Scheduler. Go to the start bar and search for "Task Scheduler" and open it. Create a basic task, name it, select Daily, set the time, choose "start a program" option, browse and select your batch file, and Finish.

This is where it gets a bit non-elegant. You'll need to schedule your batch file over and over again to get it to run every 30 minutes. There might be a better way to configure the Task Scheduler, but I've only ever needed to run a batch file daily, so this is the extent of my knowledge.

If nothing else, hopefully this sets you towards the right path!