Solved

Change MS Excel Worksheet To PDF Format Using Access VBA

Posted on 2011-03-21
5
955 Views
Last Modified: 2012-06-22
Hello Experts,

I am writing an Access database where I have to program a module in VBA to convert an MS Excel Worksheet into a PDF Format and save it.

I have searched EE and I have obtained various sporadic results with many variations and possibilities including references which is quite confusing.

I guess what I am looking for is a straight link where I can download the reference dll's and/or object files and clean VBA code to change an Excel Worksheet into PDF Format.

All help and pointers are highly appreciated.

With Regards,

ref-IT
0
Comment
Question by:ref-IT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
KGNickl earned 400 total points
ID: 35181351
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 100 total points
ID: 35182588
If you are using Excel 2007 then this code should work
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\temp\excel2pdftest.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
        True

If that doesn't work, you just need this free download from MS then it will.
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=f1fc413c-6d89-4f15-991b-63b07ba5f2e5&displaylang=en
(Note, the code above is just from hitting 'record macro' and saving the sheet as a pdf. Most of those parameters can be left out and are the defaults. I left them in to make it easier to see what options you have).
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 35182611
You may need to add a reference to the Excel library. Just click tools->references and go down to Microsoft Excel x.0 Object Library and click it. But you probably did that already. Replace ActiveSheet in the above code with whatever the variable is for the sheet (not the workbook).
0
 
LVL 2

Author Comment

by:ref-IT
ID: 35191001
Hello Experts,

Sorry for the delay in response. I had to sort the code out from an Access VBA Point of view in order to close the post for future references for anyone - besides other tasks that needed to be taken care of !!

@KGNickl: Thanks alot man for the URLs. The funny thing is, I did come across the Excel Guru Site when searching on Google and stumbled upon the Earyl Binding forum. However, I wasn't sure because of so many confusing directions, with Adobe Distiller, PrimoPDF, CutePDF and then the code - I was just not getting anywhere.

@TommySzalapski: Thanks for the post ... as you rightly mentioned, I did make a reference to the Excel x.0 Object Library. However, in the modified code below, I have used Late Binding.

The URLs posted by KGNickl give code snippets on how to convert to PDF from an Excel Worksheet VBA. I have examined the code, have made the necessary changes to amend it to suit Access VBA.

Both your help is highly appreciated especially KGNickl for the exact URLs.

Below is the Code For Access:



Private Sub ChangeToPDF()
On Error GoTo errHandler

Dim filePath As String
Dim fDialog As Office.FileDialog        'Eary Binding - Change this to Late Binding incase of error

Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

Dim pdfjob As Object
Dim sPDFName As String
Dim sPDFPath As String

'Dim lSheet As Long

'Initialise a fileDialog Picker to select the Excel file that needs to be converted
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
    .InitialFileName = "D:\"
    .AllowMultiSelect = False
    .Title = "Select file to change to pdf"
    .Filters.Add "All Files", "*.*"
    
    If .Show = True Then
        filePath = .SelectedItems(1)
    Else
        GoTo exitSub
    End If
End With


'Get the folder path and set the fileName
'Save PDF Files in folder called PDF Saved Files - Present in the same Directory as the Excel File
sPDFPath = Left(filePath, InStrRev(filePath, "\"))

'Create an Excel Object using Late Binding
'Obtain an already running instance of Excel
On Error Resume Next
Set xlApp = GetObject(, Excel.Application)

'If error exists, then there was no instance of Excel. Create a new instance
If Err <> 0 Then
    Set xlApp = CreateObject("Excel.Application")
End If
Err.Clear

With xlApp
    .Interactive = True
    
    Set xlWB = .Workbooks.Open(filePath, , False)
    Set xlWS = xlWB.Sheets(1)             
    xlWS.Activate
End With

If IsEmpty(xlWB.ActiveSheet.UsedRange) Then GoTo exitSub

Set pdfjob = CreateObject("PDFCreator.clsPDFCreator")

'Check if worksheet is empty and skip if so
If Not IsEmpty(xlWS.UsedRange) Then
    With pdfjob
        If .cStart("/NoProcessingAtStartup") = False Then
            MsgBox "Can't initialize PDFCreator.", vbCritical + _
            vbOKOnly, "PrtPDFCreator"
            GoTo exitSub
        End If
        
        '/// Change the output file name here! ///
        sPDFName = "testPDF.pdf" 
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Print the document to PDF
    xlWS.PrintOut Copies:=1, ActivePrinter:="PDFCreator"
    
    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
        DoEvents
    Loop
    pdfjob.cPrinterStop = False

    'Wait until PDF creator is finished then release the objects
    Do Until pdfjob.cCountOfPrintjobs = 0
        DoEvents
    Loop
End If


pdfjob.cClose
Set pdfjob = Nothing

Set xlApp = Nothing
Set xlWB = Nothing
Set xlWS = Nothing

exitSub:
    Exit Sub
errHandler:
    MsgBox Err.Number & vbCrLf & vbCrLf & Err.Description
    Resume exitSub
End Sub

Open in new window

0
 
LVL 2

Author Closing Comment

by:ref-IT
ID: 35191091
I have accepted KGNickl's solution for the posted URLs as they allowed me to refer to one website to draft the solution and TommySzalapski was quite handy as a guide to help formulate the Access VBA Solution by using Excel objects.

Kudos to both of you and thanks alot !!!

ref-IT
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question