Print Excel 2010 Sheet to PDF from Access 2010

[rael...@gmail.com]

Hi,
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. As soon 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 ReportGroup2 ON ReportGroup3.ReportGroup2 = ReportGroup2.ID WHERE ReportGroup3.Inactive = FALSE", CurrentProject.Connection, adOpenStatic, adLockReadOnly
If dbRS.RecordCount > 0 Then
dbRS.MoveFirst
While Not dbRS.EOF
ReportFile = dbRS!TemplateFile
Set xl = CreateObject("Excel.Application")
xl.Workbooks.Open (ReportFile)
With xl
.Range("StoreName") = dbRS!ReportGroupName
.Sheets("Practice").Select
.Activesheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=CurrentProject.Path & "\PDFs\" & dbRS!ReportGroupName & ".pdf", OpenAfterPublish:=False
.ActiveWorkbook.Close SaveChanges:=False
End With
Set xl = Nothing
dbRS.MoveNext
Wend
End If

If possible, I would like the code to be able to run without adding the Excel reference.

Rael

[Patrick Finucane]

xltypepdf is 0. xlTypeXPS is 1. Instead of the word xltypepdf what
happens if substitured with the value?

[rael...@gmail.com]

Works perfectly Patrick. Thanks for that.

Rael