Export from access to excel then convert to PDF

[claven123]

I have VBA cmd button that exports data from access to excel sheets. This works great, but I need to then make PDF's. Can this be done all from the access side on the export. Or, do I have to set up a macro or vba on the excel side to convert to pdf?

This is a sample code I have already on the access vba side.

Private Sub CmdOpenJawsCover_Click()
On Error GoTo SubError
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim SQL2ndAsst As String
Dim rs2ndAsst As DAO.Recordset
SQL2ndAsst = " SELECT TblMembers.LastName, TblMembers.FirstName, TblMembers.Position, '2nd Asst Chief:' & "" "" & [FirstName] & "" "" & [LastName] AS SecondAsstChief " & _
    " FROM TblMembers " & _
    " WHERE TblMembers.Position='2nd Asst Chief' "
Set rsChief = CurrentDb.OpenRecordset(SQLChief, dbOpenSnapshot)
Set rs1stAsst = CurrentDb.OpenRecordset(SQL1stAsst, dbOpenSnapshot)
Set rs2ndAsst = CurrentDb.OpenRecordset(SQL2ndAsst, dbOpenSnapshot)
Set xlApp = New Excel.Application
Set xlWkb = xlApp.Workbooks.Open(CurrentProject.Path & "\Master\BookCover_Jaws.xlsx")
xlApp.Visible = True
    With xlApp.ActiveWorkbook.Sheets("Jaws")
        .Range("R7").Value = (rs2ndAsst!SecondAsstChief)
    End With
SubExit:
On Error Resume Next
rsChief.Close
rs1stAsst.Close
Set rs2ndAsst = Nothing
Exit Sub
SubError:
MsgBox "Error Number: " & Err.Number & "=" & Err.Description, vbCritical + vbOKOnly, "An error occured"
 GoTo SubExit
End Sub

Dennis

 

[Sam_D_Ben]

Hi Dennis,

Just see if you can alter this code.
<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Private Sub Create_PDF_Click()

Dim myPath As String
Dim strReportName As String

DoCmd.OpenReport "Invoices", acViewPreview

myPath = "C:\Documents and Settings"
strReportName = Report_Invoices.[Client Organisations_Code] + "-" +
Report_Invoices.Clients_Code + "-" + Report_Invoices.Invoices_Code + "-" +
Format(Report_Invoices.[Invoice Date], "yyyy") + ".pdf"

DoCmd.OutputTo acOutputReport, "", acFormatPDF, myPath + strReportName, True
DoCmd.Close acReport, "Invoices"

End Sub

Thanks,
Muz</code>

 

[claven123]

I'm not sure that will work, that looks like it's for a report.

I would need the excel sheet to open then convert to PDF or I guess just convert in the background and then open the pdf.

That code looks like it saves the pdf.

Thanks though.....


D

 

[welshgasman]

Save the Excel file as a pdf or Print to a pdf driver.


I use this(albeit in Excel VBA)

    ActiveWorkbook.SaveAs fileName:=strPaymentsPath & strInvoice
    'Save a PDF version for email
    strPDFFile = strPaymentsPath & strInvoice & ".pdf"
    SaveAsPDF (strPDFFile)


Sub SaveAsPDF(pstrFile As String)
'
Dim strPDFFile As String
    strPDFFile = pstrFile
    Application.StatusBar = "Saving " & strPDFFile
    
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:=strPDFFile _
        , Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
End Sub