任务计划程序不运行Excel VBA代码以PDF格式发送电子邮件附件 [英] Task Scheduler does not run Excel VBA Code to send PDF as Email Attachment
问题描述
这是我使用的软件/系统：
Microsoft Office 2010;
任务计划程序;
Windows Server 2008 R2标准
我正在Excel文件中运行一些VBA代码，执行以下操作： / p>
1。通过SQL / ODBC连接从数据库检索数据
2。将数据上传到工作簿中的原始数据表中，并使用现在的功能
3对单元格中的工作簿进行时间戳记。刷新并格式化工作簿
中的每个数据透视表
4。导出并将指定的页面保存为PDF文档，并使用步骤2中的时间戳保存文档名称
5。保存工作簿
6。通过电子邮件将特定的PDF文档作为Excel中的电子邮件附件创建。
7。关闭Excel应用程序
我在一个名为Workbook_Open的专用子系统中运行整个系列，它检查当前时间是否与指定的运行时间匹配。如果是这样，它运行步骤1-7，如果是一个小时后，它关闭工作簿（这样我可以工作，除了那个两个小时的窗口）。
这是正在使用的代码：
*请注意，以下代码在ThisWorkbookExcel对象中运行。
'此宏将使用检查来查看是否在特定时间打开工作簿，如果您这样做，那么它将运行下面的报告自动化宏。
Private Sub Workbook_Open（）
HourRightNow = Hour（Now（））
如果HourRightNow = 13然后
调用RefreshDataTables
调用RefreshPivotTables
调用SaveWorkbook
调用ExportToPDFFile
调用EmailPDFAsAttachment
调用CloseWorkbook
ElseIf HourRightNow = 14然后
Call CloseWorkbook
如果
End Sub
Sub RefreshDataTables（）
'
'RefreshDataTables Macro
'此宏用于刷新Dentrix Tables中的数据。
'
'这将选择该表并刷新它。
表格（raw）选择
范围（D4）选择
Selection.ListObject.QueryTable.Refresh BackgroundQuery：= False
工作表（ NomenclatureVBA）Range（A2）。Formula == now（）
End Sub
Sub RefreshPivotTables（）
'
'RefreshPivotTables宏
'此宏刷新文档中的每个数据透视表。
'
'这遍历每张表格，并刷新每个数据透视表。
表（D0150 VS D0330 BY BIZLINE）数据透视表（D0150 vs D0330 by BIZLINE）。PivotCache.Refresh
列（B：DD）。选择
选择
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
结束
表格（D0150 VS D0330 ）.PivotTables（D0150 COMP EXAM vs D0330 PANO）。PivotCache.Refresh
列（B：DD）。选择
选择
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
E nd with
'Formnats to the specific date format below below。
End Sub
'----------- -------------------------------------------------- -------------------------------------------------
Sub SaveWorkbook（）
'保存活动（打开）工作簿
ActiveWorkbook.Save
End Sub
'********************** READY ***************** *******
'更简化和测试版的Export To PDF格式
'确保更新filePaths，工作表
Sub ExportToPDFFile（）
Dim strFilename As String
'考虑到Sheet1需要选择文件名
strFilename = Worksheets（NomenclatureVBA）。Range（C2）
表格（Array（D0150 VS D0330，D0150 VS D0330 BY BIZLINE））。选择
表格（D0150 VS D0330）。激活
ActiveSheet.ExportAsFixedFormat类型：= xlTypePDF，文件名：= _
\\ ****（ServerNameGoesHere）**** \UserFolders\_Common\DentrixEntrpriseCust omReports\Public\Owner Reports\DataAnalystAutomatedReports\Reports\D0150 COMP EXAM vs D0330 PANO\& strFilename& .pdf_
，质量：= xlQualityStandard，IncludeDocProperties：= True，IgnorePrintAreas _
：= False，OpenAfterPublish：= False
表格（NomenclatureVBA
'这是导出结束的地方，现在我们将继续向文件发送电子邮件。
'--------------------------------------------- --------------------------------
'电子邮件从这里开始
'这说明如果有一个文件名存储在strFileName变量中，那么....
End Sub
'此宏关闭工作簿..请注意，它会关闭您选择的非常具体的工作簿。关闭SaveChanges：= False
Application.DisplayAlerts = False
Application.Quit
End Sub
然后我也有在VBA的Modules部分发送PDF文件的宏。它看起来像这样：
Sub EmailPDFAsAttachment（）
'适用于Excel 2000，Excel 2002，Excel 2003，Excel 2007，Excel 2010，Outlook 2000，Outlook 2002，Outlook 2003，Outlook 2007，Outlook 2010.
'此示例发送最后保存的Activeworkbook对象的版本。
Dim OutApp As Object
Dim OutMail As Object
Dim FilePath As String
'这部分是将字符串和对象设置为事物。 （例如FilePath设置本身等于我们计划设置每个报告的文本）
FilePath =\\ ***（ServerGoesHere）*** \UserFolders\_Common\\ \\ DentrixEntrpriseCustomReports\Public\Owner Reports\DataAnalystAutomatedReports\Reports\D0150 COMP EXAM vs D0330 PANO\_
&工作表（NomenclatureVBA）。范围（C2）& .pdf
设置OutApp = CreateObject（Outlook.Application）
设置OutMail = OutApp.CreateItem（0）
错误恢复下一步
'在运行它之前更改宏中的邮件地址和主题。
'
带OutMail
.To =email@example.com
.CC =
.BCC =
。主题=工作表（NomenclatureVBA）。范围（C2）
.HTMLBody =Hello all！ &安培; <峰; br> 中&安培; _
这是本周针对Comp考试与Pano的报告。 &安培; <峰; br> 中&安培; _
让我知道你的想法或者你有什么意见或问题！ &安培; <峰; br> 中&安培; _
vbNewLine&签名& .HTMLBody
.Attachments.Add FilePath
'代替以下语句，您可以使用.Display
'显示邮件。
。发送
结束
出现错误GoTo 0
设置OutMail = Nothing
设置OutApp = Nothing
End Sub
当我在第13个小时（下午1点）打开工作簿时，这一切都运行正常，但是当我尝试在第13个小时内在任务计划程序中运行此操作，它运行所有操作，直到EmailPDFAsAttachment宏/子程序，并将其挂起在宏的某个位置并停止运行。我还应该声明我在Outlook和Excel中都有信任中心设置：
TrustCenterSettings
当我个人打开时，任何人都知道什么导致宏运行完美文件，然后当我尝试通过任务计划程序打开文件它停在同一个地方？
任何人都知道如何通过任务计划程序正常运行？
谢谢！
解决方案我们意识到服务器限制了我在任务调度程序中的权限。当我去IT主管将我的权限转移到管理员时，它完全执行任务调度！
对于错误的闹钟抱歉...我原本不会发布问题，但是我上周花了整整一个星期的时间。感谢大家寻找！
Here is the software/systems I am using:
Microsoft Office 2010;
Task Scheduler;
Windows Server 2008 R2 Standard
I am running some VBA code within an Excel file that does the following:
1. Retrieves Data from our Database via SQL/ODBC connections
2. Uploads data to a raw data table within the workbook and time stamps the workbook in a cell with the now function
3. Refreshes and formats each pivot table in the workbook
4. Exports and saves specified sheets as a PDF document and saves the document name with the time stamp from step 2
5. Saves the workbook
6. Emails that specific PDF document just created as an email attachment in Excel.
7. Closes the Excel Application
I run this whole series in a private sub called Workbook_Open which checks to see if current time matches the specified runtime. If it does, it runs steps 1-7, if it is an hour later, it closes the workbook (that way I can work on it other than that two hour window).
Here is the code being used:
*Note, this code below is run in the "ThisWorkbook" Excel Object.
'This Macro will use check to see if you opened the workbook at a certain time, if you did, then it will run the Report Automation Macros below.
Private Sub Workbook_Open()
HourRightNow = Hour(Now())
If HourRightNow = 13 Then
Call RefreshDataTables
Call RefreshPivotTables
Call SaveWorkbook
Call ExportToPDFFile
Call EmailPDFAsAttachment
Call CloseWorkbook
ElseIf HourRightNow = 14 Then
Call CloseWorkbook
End If
End Sub
Sub RefreshDataTables()
'
' RefreshDataTables Macro
' This Macro is used to refresh the data from the Dentrix Tables.
'
'This selects the table and refreshes it.
Sheets("raw").Select
Range("D4").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
Worksheets("NomenclatureVBA").Range("A2").Formula = "=now()"
End Sub
Sub RefreshPivotTables()
'
' RefreshPivotTables Macro
' This Macro refreshes each Pivot Table in the document.
'
'This goes through each sheet and refreshes each pivot table.
Sheets("D0150 VS D0330 BY BIZLINE").PivotTables("D0150 vs D0330 by BIZLINE").PivotCache.Refresh
Columns("B:DD").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("D0150 VS D0330").PivotTables("D0150 COMP EXAM vs D0330 PANO").PivotCache.Refresh
Columns("B:DD").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Formnats to the specific date format below.
End Sub
'--------------------------------------------------------------------------------------------------------------
Sub SaveWorkbook()
' Saves Active (Open) Workbook
ActiveWorkbook.Save
End Sub
'**********************READY************************
'More simplified and tested version of the Export To PDF format
'Make sure to update the filePaths, worksheets,
Sub ExportToPDFFile()
Dim strFilename As String
'Considering Sheet1 to be where you need to pick file name
strFilename = Worksheets("NomenclatureVBA").Range("C2")
Sheets(Array("D0150 VS D0330", "D0150 VS D0330 BY BIZLINE")).Select
Sheets("D0150 VS D0330").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"\\****(ServerNameGoesHere)****\UserFolders\_Common\DentrixEntrpriseCustomReports\Public\Owner Reports\DataAnalystAutomatedReports\Reports\D0150 COMP EXAM vs D0330 PANO\" & strFilename & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Sheets("NomenclatureVBA").Select
'This is where the exporting ends, now we will proceed to email the file.
'-----------------------------------------------------------------------------
'The emailing begins here
'This says that if there is a file name stored in the strFileName variable, then....
End Sub
'This Macro Closes the workbook... Note that it closes the very specific workbook you choose.
Sub CloseWorkbook()
'Workbooks("Automated D0150 COMP EXAM vs D0330 PANO.xlsm").Close SaveChanges:=False
Application.DisplayAlerts = False
Application.Quit
End Sub
Then I also have the macro that emails the PDF file in the Modules section of VBA. It looks like this:
Sub EmailPDFAsAttachment()
' Works in Excel 2000, Excel 2002, Excel 2003, Excel 2007, Excel 2010, Outlook 2000, Outlook 2002, Outlook 2003, Outlook 2007, Outlook 2010.
' This example sends the last saved version of the Activeworkbook object .
Dim OutApp As Object
Dim OutMail As Object
Dim FilePath As String
'This part is setting the strings and objects to be things. (e.g. FilePath is setting itself equal to the text where we plan to set up each report)
FilePath = "\\***(ServerGoesHere)***\UserFolders\_Common\DentrixEntrpriseCustomReports\Public\Owner Reports\DataAnalystAutomatedReports\Reports\D0150 COMP EXAM vs D0330 PANO\" _
& Worksheets("NomenclatureVBA").Range("C2") & ".pdf"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
' Change the mail address and subject in the macro before you run it.
'
With OutMail
.To = "email@example.com"
.CC = ""
.BCC = ""
.Subject = Worksheets("NomenclatureVBA").Range("C2")
.HTMLBody = "Hello all!" & "<br>" & _
"Here is this week's report for the Comp Exam vs. Pano." & "<br>" & _
"Let me know what you think or any comments or questions you have!" & "<br>" & _
vbNewLine & Signature & .HTMLBody
.Attachments.Add FilePath
' In place of the following statement, you can use ".Display" to
' display the mail.
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
So this all runs fine when I open up the workbook at the 13th hour (1pm), however, when I try to run this in Task Scheduler during the 13th hour, it runs everything up until the EmailPDFAsAttachment macro/sub and it gets hung up somewhere in the macro and stops running.
I should also state that I have the Trust Center Settings to the following in both Outlook and Excel:
TrustCenterSettings
Anyone know whats causing the macro to run perfectly when I personally open the file and then when I try and open the file via Task Scheduler it stalls in the same spot?
And anyone know how to make it run correctly via Task Scheduler?
Thanks!
解决方案 We realized that the server limited my permissions in the task scheduler. When I went my IT Director switched my permissions to Admin, it ran the task scheduler perfectly!
Sorry for the false alarm... I wouldn't have posted the question originally, but I spent all last week working on it. Thanks everybody for looking!
这篇关于任务计划程序不运行Excel VBA代码以PDF格式发送电子邮件附件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
