VBA code for export to pdf file

[Milan25]

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

[NoellaG]

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

[Milan25]

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

[NoellaG]

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

[Milan25]

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