Print Excel 2010 Sheet to PDF from Access 2010
I have an Access app that I need to do the following:
1. Open an Excel file
2. Change a value
3. Print a sheet to pdf
4. Close Excel file
Because the production machine is Office 2007, I am using late binding and don't have a reference to the Excel object library.
Because of this, the code is failing at Step 3:
xl.Activesheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=TargetFile, OpenAfterPublish:=False
I get a "Variable not defined" error highlighting the "xlTypePDF" value. Assoon as I add a reference to the Excel object library the code compiles and runs correctly.
Full code is as follows:
Dim xl As Object
'INDIVIDUAL STORES FIRST
dbRS.Open "SELECT ReportGroup2.TemplateFile, ReportGroup3.ReportGroupName, ReportGroup3.DeliveryList FROM ReportGroup3 INNER JOIN ReportGroup2ON ReportGroup3.ReportGroup2 = ReportGroup2.ID WHERE ReportGroup3.Inactive = FALSE", CurrentProject.Connection, adOpenStatic, adLockReadOnly
If dbRS.RecordCount > 0 Then
While Not dbRS.EOF
ReportFile = dbRS!TemplateFile
Set xl = CreateObject("Excel.Application")
.Range("StoreName") = dbRS!ReportGroupName
.Activesheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=CurrentProject.Path & "\PDFs\" & dbRS!ReportGroupName & ".pdf", OpenAfterPublish:=False
Set xl = Nothing
If possible, I would like the code to be able to run without adding the Excel reference.