Export Excel worksheets to individual pdfs using VBA code

2
votes

I have a script that I want to run in MS Access to export all the worksheets in a late bound selected workbook to individual PDfs in a specific location with the PDF file names being the names of the worksheets. Below is what I have so far, but I am having trouble figuring out what the export code is. I have tried modifying Excel VBA code (Excel VBA to Export Selected Sheets to PDF) to export, but it kept returning compile errors at every step.

Dim xls  As Object
Dim wkb  As Object
Dim wks1 As Object
Dim wks2 As Object
Dim wks3 As object
Set xls = CreateObject("Excel.Application")
Set wkb = xls.Workbooks.Open("\\EXCHSVR1\stone\Mold_Books\" & mold_id & "\" & Mid(Mid(strFlpath, InStrRev(strFlpath, "/") + 1), InStrRev(strFlpath, "\") + 1))
Set wks1 = wkb.Worksheets(1)
 'code to export the first worksheet to "C:\test\" & wks1.name
Set wks2 = wkb.Worksheets(2)
 'code to export the second worksheet to "C:\test\" & wks2.name
Set wks3 = wkb.Worksheets(3)
 'code to export the third worksheet to "C:\test\" & wks3.name
2
excelms-accessvba
Sorry, I missed it. Yes I did refer to workbooks in earlier code. I added it above – Daniel L. VanDenBosch
move Set xls = CreateObject("Excel.Application") to be after last Dim – M.Hassan
Sorry, the code above doesn't. the compile error occurred when I attempted to use late binding and access vba couldn't find the objects like xlTypePDF. photos.google.com/share/… – Daniel L. VanDenBosch

2 Answers

2
votes

Based on your comment, this is one example of the compile errors you're facing:

enter image description here

That happens because xlTypePDF is an Excel constant and is unknown to Access unless you set a reference to the Microsoft Excel Object Library.

Since you want to use late binding, you probably don't want to add that reference to your VBA project. In that case there are two things you can do.

  1. Declare the constant in your Access code: Const xlTypePDF As Long = 0 Then you can continue to use that constant's name elsewhere in your code without triggering the compile error.
  2. Or use the constant's value (0) instead of its name in your Access code.

And you will have to deal with the other Excel constants, such as xlQualityStandard in the same way.

1
votes

The post you are looking at is using the active sheet. You are creating worksheet objects. Use the ExportAsFixedFormat on each worksheet object.

wks1.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=myPDFFilePathAndName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

wks2.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=myPDFFilePathAndName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

wks3.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=myPDFFilePathAndName, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False