Although never tried, inclined to say yes. I have code behind a workbook that saves worksheet as PDF. This should be adaptable to run from Access to act on the Excel object that Access opened in VBA.
Here is my workbook code:
Code:
Private Sub btnPDF_Click()
Dim FName As Variant
If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "More than the GEOReport sheet is selected, printing canceled."
Else
Call PrintSetup
'Test If the Microsoft Add-in is installed
If Dir(Environ("commonprogramfiles") & "\Microsoft Shared\OFFICE" _
& Format(Val(Application.Version), "00") & "\EXP_PDF.DLL") = "" Then
MsgBox "Error: MS Add-in not found. Contact Administrator"
Else
ChDrive (Left(ActiveWorkbook.Path, 1))
ChDir (Left(ActiveWorkbook.Path, 1) & ":\")
'Open the GetSaveAsFilename dialog to enter a file name for the pdf
FName = Application.GetSaveAsFilename(gstrStateNum & "GeoReport.pdf", _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Create PDF")
'If you cancel this dialog Exit the function
If FName <> False Then
'test if PDF already exists in the folder
If Dir(IIf(Not FName Like "*.pdf", FName = FName & ".pdf", FName) & "") <> "" Then
If MsgBox("Overwrite existing file?", vbYesNo) = vbNo Then
FName = InputBox("Enter file name.")
End If
End If
If Not FName Like "*.pdf" Then FName = FName & ".pdf"
'Now the file name is correct we Publish to PDF
On Error Resume Next
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
FileName:=FName, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
On Error GoTo 0
End If
End If
End If
Private Sub PrintSetup()
Dim intPages As Integer
Dim intRows As Integer
Dim strCell As String
Dim i As Integer
'number of records extracted
intRows = Worksheets("Samples").Range("C4").Value
'Calculate number of pages based on number of records extracted
If intRows / 6 - Int(intRows / 6) <> 0 Then
intPages = Int(intRows / 6) + 1
Else
intPages = Int(intRows / 6)
End If
With Worksheets("GEOReport").PageSetup
' .PrintTitleRows = "$1:$4"
' .PrintTitleColumns = ""
.PrintArea = "$A2:$H" & intPages * 40 + 4
' .LeftHeader = ""
' .CenterHeader = ""
' .RightHeader = ""
' .CenterFooter = _
' "Gradation tests based on minus 3"" material." & IIf(optAASHTO, " AASHTO Class may be inappropriate if Organic Content > 5%.", "") _
' & vbLf & vbLf & "Page &P of " & intPages
.LeftFooter = "Gradation tests based on minus 3"" material."
.RightFooter = "Page &P of " & intPages
' .LeftMargin = Application.InchesToPoints(0.75)
' .RightMargin = Application.InchesToPoints(0.25)
' .TopMargin = Application.InchesToPoints(1)
' .BottomMargin = Application.InchesToPoints(1)
' .HeaderMargin = Application.InchesToPoints(0.5)
' .FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .CenterHorizontally = False
' .CenterVertically = False
' .Orientation = xlPortrait
' .Draft = False
' .PaperSize = xlPaperLetter
' .FirstPageNumber = xlAutomatic
' .Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = 100
' .PrintErrors = xlPrintErrorsDisplayed
End With
End Sub