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 protected]"
.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!
On Error Resume Next. Maybe you'll get a clue as to what error is occurring. – xidgel