Access to save existing Excel workbook as PDF

[LB79]

Hello all,

Does anyone know if it is possible in Access 2007 to select an Excel workbook and create a PDF file of the visible sheets?

Thanks

 

[Trevor G]

In your database use Alt + F11 to go into the VBA screen then select the Tools Menu and search down the list for Microsoft Excel 12.Object Library and click the box then close the dialog box down.

Next click the Insert menu and then Module, in the module screen copy and paste this code, you will have to adjust a few things

Sub OpenEx()
Dim wk As Excel.Application
Set wk = CreateObject("Excel.Application")
With wk
.Visible = True
.Workbooks.Open "Path and name include extension.xls"
.Sheets("Sheet1").Select
.Sheets("Sheet1").Copy
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:="M:\another.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

End With
End Sub

 

[LB79]

Thanks for the reply.
I have multiple sheets whose names may change depending on the report.
How would I go about combining all the sheets of a workbook into a single PDF? Im having a problem with multiple sheet selection.

Thanks

 

[JANR]

Try this:

Function ExportPDF(xlFileName As String, SavePDF As String)
Dim objXl As Object

Const xlTypePDF = 0
Const xlQualityStandard = 0

Set objXl = CreateObject("Excel.Application")

With objXl
    .Workbooks.Open (xlFileName)
       
    .ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "" & SavePDF & "", quality:=xlQualityStandard, _
        includedocproperties:=True, ignoreprintareas:=False, openafterpublish:= _
        True
    .Quit
End With
    
End Function

You call the function and supply fullpath to both excel file and your desired PDF-filename.

ExportPDF("c:\Myexcel.xls", "c:\MyNewFile.pdf")

Uses latebinding so no need to refrence Excel. It should only export visible sheets, but test it

JR

 

[LB79]

Hi again,

I've implemented the above suggestion which is great – Thanks for that.

BUT for some reason (maybe completely obvious by I cant seem to see it), my code is debugging in the following way:

1. Run the code – Works OK
2. Run the code – Debug error 1004
3. Run the code – Works OK from here on – It only debugs the second time of running the code – Closing and reopening the form resets to point 1.

I've noticed Excel isn’t shutting down after the code has run. As far as I can see I'm setting it to close in the code – Am I missing something?

Thanks for any advice J

Public xlApp As Object
Public xlWorkSheet As Worksheet

FilePath = "P:\Analysis.xlsx"
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = False
.Workbooks.Open FilePath
For Each xlWorkSheet In Sheets
If xlWorkSheet.Visible Then xlWorkSheet.Select (False)
Next
ATMTSQL = "P:\Analysis.pdf"
.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=ATMTSQL, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

Set xlWorkSheet = Nothing
xlApp.Quit
Set xlApp = Nothing

 

[JANR]

Diden't my code work to your expectation?

Anyway I think this causes that extra instance of excel.

[FONT=Arial][COLOR=#1f497d]For Each xlWorkSheet In [COLOR=red][B]Sheets[/B][/COLOR][/COLOR][/FONT]

You need to tie every excel-objects and methodes to one of your declared Excel-objects.

try:

[FONT=Arial][COLOR=#1f497d]For Each xlWorkSheet In [COLOR=red][B]xlApp.Sheets[/B][/COLOR][/COLOR][/FONT]

Also clear out any instances of excel from the taskmanager before your run the code, and check it again after to see if any lingering Excel instances.

JR

 

[LB79]

Hi JANR - Sorry I didnt see your original post which actually looks to be far better than the route I was taking. Thanks very much

 

[cstickman]

Hey,
Has anyone else attempted to do this? I have an Access project where the users are requesting this feature. Thanks