尝试每30分钟运行一次宏

Question

我到处找，似乎找不到我的问题的答案。

我正在尝试每30分钟运行一次宏。工作簿可以保持打开状态，但不必保持打开状态。我确实有另外3个excel工作簿，必须保持全天打开，因为这是为对冲基金，他们提供实时数据使用彭博API。我正在尝试运行的宏发送了一封电子邮件，其中包含我们全天投资组合的PnL更新。在它自己的宏上运行效果很好。宏在这里：

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

我试过插入

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

放入所述宏中。它可以工作，每30分钟发送一次电子邮件。问题是它发送的是当时活动的工作表，而不是我需要它发送的工作表。

我试着写了一个看起来像这样的模块：

Sub callPnLUpdate()

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

这会给我一个错误：“无法运行宏'worksheetname.Xlsx!PnLUpdate‘。宏可能在此工作簿中不可用，或者所有宏都可能被禁用。”

我试着寻找这个问题的解决方案，但所有的解决方案都说要启用编程访问，我已经这样做了，但也不起作用。

需要说明的是:这个问题的解决方案不一定是使用我的宏。我会接受任何解决方案，它可以发送这个特定的电子表格每30分钟(最好从上午9点31分到下午4点05分结束，但这是次要的让这个自动化工作每30分钟)。

任何帮助都是非常感谢的。

谢谢!

Answer 1

关键是在任何Range或Cells调用中完全限定Workbook和Worksheet，否则会隐含ActiveWorkbook和ActiveSheet。

例如：

• Set rng = Range("A1:M300")应该类似于Set rng = ThisWorkbook.Sheets("PnL").Range("A1:M300")。或者，如果您更喜欢使用工作表代码名，比如Set rng = Sheet10.Range("A1:M300").
• And，那么HtmlContent = HtmlContent & "<td>" & Cells(i, j).Value & "</td>"应该是HtmlContent = HtmlContent & "<td>" & rng.Cells(i, j).Value & "</td>"。否则，Cells(i, j)将引用活动工作表上的单元格。

Answer 2

我不知道这是否适用于您的特定环境，但每当我需要以特定的时间间隔运行宏时，我都会在Workbooks_Open子对象上设置宏，然后从批处理文件中调用该文件，并使用PC上的任务调度器应用程序来调度该批处理文件。

因此，创建需要运行的宏，并将其放在工作簿部分(而不是模块)中，格式如下：

Sub Workbook_Open()
'your macro goes here
End sub

然后创建一个批处理文件，用于打开您的Excel文档。打开记事本并输入以下代码。对于代码的第一部分，输入Excel.exe文件的路径。对于代码的第二部分，输入excel文件的路径。如果您的Excel文件位于网络驱动器上，则需要对其进行稍微不同的路径。

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

然后使用.bat文件扩展名保存记事本文档。我不记得了，但你可能必须在你的文件资源管理器中改变一个开关才能做到这一点(这个开关会显示你所有图标的文件扩展名)。

获得批处理文件后，双击它以验证它是否正常工作。如果所有操作都正确，批处理文件将打开一个CMD窗口，该窗口将调用您的Excel文件。Excel文件将打开，宏将根据Workbooks_Open命令自动触发。

现在，只需安排批处理文件在Task Scheduler上运行。进入开始栏，搜索"Task Scheduler“并打开它。创建一个基本任务，命名它，选择Daily，设置时间，选择"start a program“选项，浏览并选择您的批处理文件，然后完成。

这就是它变得有点不优雅的地方。您需要一遍又一遍地安排批处理文件，使其每30分钟运行一次。可能有更好的方法来配置Task Scheduler，但我只需要每天运行一个批处理文件，所以这就是我的知识范围。

如果没有其他事情，希望这能让你走上正确的道路！