Results 1 to 5 of 5

VBA code for export to pdf file

  1. #1
    Milan25 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Location
    Slovakia
    Posts
    5

    VBA code for export to pdf file

    hello everyone,

    I want to try something new in my database.
    Well, I have a working VBA code for export my selected datas from database to excel.
    But now I would like to do that again with pdf document.
    The source will be the same like for excel, but I want to export everything to a report called "Položky skladu".
    It is existing report in my database and it´s source is a query called "D_Položky skladu".
    Could somebody help me with this?


    VBA code for Excel export:

    Private Sub Export_XLS_Click()

    Dim MyDB As Database
    Dim qdfTemp As QueryDef
    Dim xSql As String, xPortFileName As String

    xSql = Me.RecordSource

    Set MyDB = CurrentDb

    On Error GoTo xErr

    With MyDB

    xPortFileName = "Excel_Položky skladu"
    Set qdfTemp = .CreateQueryDef(xPortFileName, xSql)

    DoCmd.OutputTo acOutputQuery, qdfTemp.Name, , , -1, , , acExportQualityPrint

    .QueryDefs.Delete xPortFileName

    Exit_Export_XLS:
    Exit Sub
    xErr:

    If Err.Number = 2501 Then
    MsgBox "Export do *.xls zrušený používateľom", vbInformation, "INFO"
    Else
    MsgBox Error$
    .QueryDefs.Delete xPortFileName
    End If

    End With

    Resume Exit_Export_XLS

    End Sub

  2. #2
    NoellaG's Avatar
    NoellaG is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    439
    Hi,

    For Access 2007: be sure you have installed the add-in "Print to PDF" (you can download this for free from the Microsoft site), then try something like:

    DoCmd.OutputTo acOutputReport, [Name report], acFormatPDF, [Name File], False

    gr
    NG

  3. #3
    Milan25 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Location
    Slovakia
    Posts
    5
    hello,

    yes, I have installed the add-in "Print to PDF" component and I have also tried VBA code like yours. It´s really working, but there is a problem when I want to export only selected data (I use for this 3 different filters).
    Recordsource for my database and all filters you can see below:

    Private Sub Filter_start()

    Dim MyForm As Form, xx As String
    Dim xSql As String, xWhr As String

    Set MyForm = Me.Form '[Forms]![D_položky skladu]

    On Error GoTo errdsc
    xSql = "SELECT [D_položky skladu].* FROM [D_položky skladu] WHERE (1=1);"

    'TEST VYPLNENOSTI FILTER POLI
    xx = nt(MyForm.Filter_1.Value)
    If xx <> "" Then
    xWhr = xWhr & IIf(xWhr <> "", " and ", "") & "([D_položky skladu].Skupina = '" & xx & "')"
    End If

    xx = nt(MyForm.Filter_2.Value)
    If xx <> "" Then
    xWhr = xWhr & IIf(xWhr <> "", " and ", "") & "([D_položky skladu].Názov = '" & xx & "')"
    End If

    xx = nt(MyForm.Filter_3.Value)
    If xx <> "" Then
    xWhr = xWhr & IIf(xWhr <> "", " and ", "") & "([D_položky skladu].Dodávateľ = '" & xx & "')"
    End If

    'UPDATE ZDROJA UDAJOV
    If xWhr <> "" Then
    xSql = Replace(xSql, "(1=1)", xWhr, 1, -1, 1)
    End If
    Me.RecordSource = xSql
    Me.Requery

    errdsc:
    Debug.Print Err.Description

    End Sub

  4. #4
    NoellaG's Avatar
    NoellaG is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    439
    Hi,

    a bit difficult to follow: are you building a filter in the report itself? I got the feeling this report is opened from a form. Is it possible to set the report's datasource to a parameter query that draws it's criteria from the form? This way there's no problem exporting the PDF.

    greetings
    NG

  5. #5
    Milan25 is offline Novice
    Windows XP Access 2007
    Join Date
    Jan 2011
    Location
    Slovakia
    Posts
    5
    Hi, I have already a solution using this VBA code:

    Private Sub Export_PDF_Click()

    Dim xSql As String

    xSql = Me.RecordSource
    xSql = Mid(xSql, InStr(1, xSql, "WHERE") + 6, Len(xSql))
    xSql = Mid(xSql, 1, Len(xSql) - 1)
    xSql = Replace(xSql, ";", "", 1, -1, 1)

    DoCmd.OpenReport "Položky skladu", acViewPreview, , xSql, acHidden

    On Error GoTo xErr

    DoCmd.OutputTo acOutputReport, "Položky skladu", , , -1, , , acExportQualityPrint
    DoCmd.Close acReport, "Položky skladu", acSaveNo

    xErr:
    If Err.Number = 2501 Then
    MsgBox "Export do *.pdf zrušený používateľom", vbInformation, "INFO"
    DoCmd.Close acReport, "Položky skladu", acSaveNo
    End If

    End Sub

    This is exactly that what I was looking for and it´s so simply :-)
    Many thanks for your time and help

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export File to another Database
    By maintt in forum Import/Export Data
    Replies: 5
    Last Post: 11-25-2010, 11:07 AM
  2. Use of CurrentProject.Path in export code
    By gg80 in forum Programming
    Replies: 1
    Last Post: 08-02-2010, 07:51 PM
  3. File Export
    By Kencao in forum Import/Export Data
    Replies: 3
    Last Post: 02-01-2010, 03:27 PM
  4. Using code to set export specs
    By SSylvesterSYC in forum Programming
    Replies: 0
    Last Post: 06-08-2009, 09:29 AM
  5. How do you export a file with a unique file name
    By Budman42 in forum Import/Export Data
    Replies: 1
    Last Post: 10-15-2006, 05:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums