Export Excel worksheets to individual pdfs using VBA code

Question

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

Sorry, I missed it. Yes I did refer to workbooks in earlier code. I added it above — Daniel L. VanDenBosch, Aug 01 '16 at 20:43
move Set xls = CreateObject("Excel.Application") to be after last Dim — M.Hassan, Aug 01 '16 at 21:39
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. https://photos.google.com/share/AF1QipPqJOFdXeJoJ1KX59xPzImrGe4MYv_UAkDd3M5IvnhKQAjAuELTI5P_i9QC0e3vtQ?key=NWFPcHcwamo4RVJZX0M0cUFMUlB0QU5QS2E3V3pR — Daniel L. VanDenBosch, Aug 02 '16 at 12:29

score 2 · Accepted Answer · answered Aug 02 '16 at 14:14

2

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

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.

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.
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.

answered Aug 02 '16 at 14:14

HansUp

95,961
11
77
135

1

Hey awesome and it worked! I have the full code posted here https://drive.google.com/open?id=1LIoAthYnRDYft8hcDZN9yLhgpO19qs98Lbz1SkHe_9g – Daniel L. VanDenBosch Aug 02 '16 at 16:09
Another thing I did was I added to the code at the top before creating all the PDFs is ---wkb.CheckCompatibility = False--- and another line ---xls.DisplayAlerts = False--- this is to prevent all those warnings that keep the process from being fluid. – Daniel L. VanDenBosch Aug 08 '16 at 15:14
Sorry to keep adding to this. I also found out how to figure out the given values of the constants. Just go to excel VBA and type debug.print xlTypePDF then hit enter and it will return 0 so we can know what to declare our CONST values as. – Daniel L. VanDenBosch Aug 10 '16 at 19:03

MatthewD · Answer 2 · 2016-08-01T17:11:23.693

1

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

edited Aug 01 '16 at 17:11

answered Aug 01 '16 at 16:56

MatthewD

6,719
5
22
41

The issue is that when I take that approach I end up with compile errors. – Daniel L. VanDenBosch Aug 01 '16 at 17:19
sorry about that. It says compile error variable not defined. – Daniel L. VanDenBosch Aug 01 '16 at 17:27
https://photos.google.com/share/AF1QipPqJOFdXeJoJ1KX59xPzImrGe4MYv_UAkDd3M5IvnhKQAjAuELTI5P_i9QC0e3vtQ?key=NWFPcHcwamo4RVJZX0M0cUFMUlB0QU5QS2E3V3pR – Daniel L. VanDenBosch Aug 01 '16 at 17:28
2

ok, this will work if I early bind the excel reference to my database, which might work for a while, but it is best practice to late bind because it makes support easier. Is it possible to late bind your solution? – Daniel L. VanDenBosch Aug 01 '16 at 17:51
1

You can still use late binding - just find the value of the Excel constants and set them up yourself. You only have three here to find - but if your Excel code makes extensive use of xl constants in can take awhile to set them all up. (xlTypePDF, xlQualityStandard, xlQualityStandard) – dbmitch Aug 01 '16 at 20:53

Export Excel worksheets to individual pdfs using VBA code

2 Answers2