Exporting Report from Access into PDF

[Gina Whipp]

Erick,

Glad to hear that! So sorry, I didn't get back to you... had a Client
emergency!

Not a chore at all... and for someone who siad I don't know alot about VBA,
you did an excellent job! I would also like to thank you for sharing your
solution as I'm sure it will benefit someone else!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina -
I am happy to report that I have had a breakthrough!
I took the code that I sent you in the last post and messed around
with it for a while. I finally got the code to work. It creates an
individual PDF and adds the AcctNum to the name for each one.
Now I am going to start chipping away at the email part and see if I
can use some of the same stuff in this code for looping and finding
each customer's AcctNum and email address.
Thank you again for all of your patience and work. I know it was a
chore just getting me to this point.

Here is what I ended up using:

'Declare Variables and Objects
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strAcctNum As String
Dim strReportName As String


'Set Variables and Objects
strSQL = "SELECT AcctNum From Customers_To_Email"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)
strReportName = "Sweep_Report_For_Email"


rst.MoveFirst


'Turns off the screen
DoCmd.Echo True
Do While Not rst.EOF
strAcctNum = rst!AcctNum
'If CustomerID is a number change to: "CustomerID="
&strCustID
DoCmd.OpenReport strReportName, acViewPreview, , "[BNY
Acct#]=" & "'" & strAcctNum & "'"


'This is the call to the Stephen Lebans code.
Call ConvertReportToPDF(strReportName, , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" & strAcctNum
& ".pdf", False, False)

DoCmd.Close acReport, strReportName

rst.MoveNext
Loop
DoCmd.Echo True
MsgBox "Done", vbInformation

'Recordset Cleanup Code
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

End Sub

Hi Gina -
I have some more code for you. I found something that is referencing
the same Lebans ConvertReportToPDF. I thought it may help. From the
description I believe it is supposed to loop through different
CustomerIDs:

Private Sub cmdBulkConvertIndividualCustReports_Click()

'Notes:--------------------------------------------------------------------­-----------
'- You need to download this file from Lebans.com:
'(http://www.lebans.com/DownloadFiles/A2000SnapshotToPDFver785.zip
'... then copy the DLL's to your Windows/system32 folder,
'(Or to the same folder this database is in.)
'--------------------------------------------------------------------------­-----------

On Error GoTo Err_cmdEmailIndividualCustReports_Click

'Declare Variables and Objects
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strCustID As String
Dim strEmail As String
Dim strReportName As String
Dim blRet As Boolean

'Set Variables and Objects
strSQL = "SELECT CustomerID,EmailAddress From tblCustomers"
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL)
strReportName = "rptEmailCustReportCustomers"

rst.MoveFirst

'Turns off the screen
DoCmd.Echo False
Do While Not rst.EOF
strCustID = rst!CustomerID
strEmail = rst!EmailAddress
'If CustomerID is a number change to: "CustomerID=" &
strCustID
DoCmd.OpenReport strReportName, acViewPreview, ,
"CustomerID=" & "'" & strCustID & "'"

'This is the call to the Stephen Lebans code.
blRet = ConvertReportToPDF(strReportName,
vbNullString, "C:\YourFolder\" & strReportName & "_Cust_" & strCustID
& ".pdf", False, False, 150, "", "", 0, 0, 0)

DoCmd.Close acReport, strReportName

rst.MoveNext
Loop
DoCmd.Echo True
MsgBox "Done", vbInformation

'Recordset Cleanup Code
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing

Exit_cmdEmailIndividualCustReports_Click:
'Avoids the endless loop
DoCmd.Echo True
On Error Resume Next
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub

Err_cmdEmailIndividualCustReports_Click:

MsgBox "There was an error executing the command." _
& vbCrLf & vbCrLf & "Error " & Err.Number & ": " _
& vbCrLf & vbCrLf & Error, vbExclamation

Resume Exit_cmdEmailIndividualCustReports_Click

End Sub

Erick,

Thanks, it might give me an idea!

"I feel I have been denied critical, need to know, information!" -
Tremors
II

"Erick C" <[email protected]> wrote in message

Hi Gina -
I am searching away on my side to see if I can find anything that may
help out. I will have to check out a few more websites once I get
home.
I do have a code that I use in another database, though the purpose is
different. Maybe some of the code might give you an idea.
The code creates dummy queries for each individual managerID and then
exports a xls file for each managerID. I cannot tell you everything
that the code is doing, but I know that it looks at a managerID table
in the database and adds the managerID field to the name of the
exported file and moves to the next one. Just thought it may possibly
help, or not.

Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic
names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile
rstMgr.MoveNext
Loop

Erick,
I am going to have to admit this one has me stumped... Anything I do
to
loop thru the data to extract the CustomerID still only grabs the
first
CustomerID. I am trying to find a way to identify the report so you
know
which eMail to attach it to without opening each report and checking
it.
Not giving up just yet... just not sure to get it to do what I want it
to
do...
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina -
No problem. I am just happy that I was not going crazy, or I had
entered something incorrectly.
Erick,
My fault, it's not cycling the combo box to get CustomerID. Let me
think
on
this...
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Hi Gina -
Here is the code that I currently have. This code exports files with
names like "-Sweep_To_Email000.pdf"
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![Combo24]
For intCounter = 0 To Combo24.ListCount - 1
DoCmd.OpenReport "Sweep_Report_For_Email", acViewPreview, ,
"[AcctNum] = '" & cboCode.ItemData(intCounter) & "'"
DoEvents
Call ConvertReportToPDF("Sweep_Report_For_Email", , "C:\Documents
and Settings\ecrump\My Documents\Access Test\Test Files\" &
Me.Combo24
& "-Sweeps_Report_For_Email" & Format(intCounter, "000") & ".pdf",
False, False)
DoCmd.Close acReport, "Sweep_Report_For_Email"
Next
Erick,
AccNum-Sweep_To_Email001.pdf
Please copy/paste the code you are using here... maybe I missed
something!
--
Gina Whipp
2010 Microsoft MVP (Access)
"I feel I have been denied critical, need to know, information!" -
Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Or I should clarify, I do not get anything before the -
Sweep_To_Email.pdf. So where we would expect to see the AcctNum,
there is nothing if nothing is selected in the combo box. But the
pdf
still gets created, just missing the beginning part of the name.
I also tried Me.Combo24, but it only gives a name

...

read more »- Hide quoted text -

- Show quoted text -

 

[Ken Snell]

Hey, I recognize this code! <grin>
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Hi Gina -
I am searching away on my side to see if I can find anything that may
help out. I will have to check out a few more websites once I get
home.
I do have a code that I use in another database, though the purpose is
different. Maybe some of the code might give you an idea.
The code creates dummy queries for each individual managerID and then
exports a xls file for each managerID. I cannot tell you everything
that the code is doing, but I know that it looks at a managerID table
in the database and adds the managerID field to the name of the
exported file and moves to the next one. Just thought it may possibly
help, or not.

Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic
names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile
rstMgr.MoveNext
Loop

 

[Gina Whipp]

Ken,

He actually gave you credit in the first post of this thread!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Hey, I recognize this code! <grin>
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Hi Gina -
I am searching away on my side to see if I can find anything that may
help out. I will have to check out a few more websites once I get
home.
I do have a code that I use in another database, though the purpose is
different. Maybe some of the code might give you an idea.
The code creates dummy queries for each individual managerID and then
exports a xls file for each managerID. I cannot tell you everything
that the code is doing, but I know that it looks at a managerID table
in the database and adds the managerID field to the name of the
exported file and moves to the next one. Just thought it may possibly
help, or not.

Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset, rstMgr1 As DAO.Recordset
Dim strSQL As String, strTemp As String, strMgr As String
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean
strBrowseMsg = "Select the folder where the detail files will be
created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
' Create temporary query that will be used for exporting data;
' we give it a dummy SQL statement initially (this name will
' be changed by the code to conform to each manager's identification)
strTemp = dbs.TableDefs(0).Name
strSQL = "SELECT * FROM [" & strTemp & "] WHERE 1=0;"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close
strTemp = strQName
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
' Get list of ManagerID values -- note: replace my generic table and
Field names
' with the real names of the EmployeesTable table and the ManagerID
Field
strSQL = "SELECT DISTINCT Subinventory FROM [Subinv Subtotal By
Transaction Type];"
Set rstMgr = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)
' Now loop through list of ManagerID values and create a query foreach
ManagerID
' so that the data can be exported -- the code assumes that the actual
names
' of the managers are in a lookup table -- again, replace generic
names with
' real names of tables and fields
If rstMgr.EOF = False And rstMgr.BOF = False Then
rstMgr.MoveFirst
Do While rstMgr.EOF = False
' *** code to set strMgr needs to be changed to conform to your
' *** database design -- ManagerNameField, ManagersTable, and
' *** ManagerID need to be changed to your table and field names
' *** be changed to your table and field names
strMgr = DLookup("Subinventory", "Stores for Export", _
"Subinventory = '" & rstMgr!Subinventory.value &
"'")
' *** code to set strSQL needs to be changed to conform to your
' *** database design -- ManagerID and EmployeesTable need to
' *** be changed to your table and field names
strSQL = "SELECT * FROM [Subinv Subtotal By Transaction
Type] WHERE " & _
"Subinventory = '" & rstMgr!Subinventory.value & "';"
Set qdf = dbs.QueryDefs(strTemp)
qdf.Name = "q_" & strMgr
strTemp = qdf.Name
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
strFile = Format$(Date, "Medium Date") & " " & strMgr &
".xls"
strPathFile = strPath & "\" & strFile
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, strTemp, strPathFile
rstMgr.MoveNext
Loop

 

[Ken Snell]

Yeah, I'd read that in his first post. Erick C and I had a long thread for
getting that code defined a few months back. <smile>

Thanks, Erick, for the mention!

Haven't had much time lately to prowl the newsgroups.....

 

[Gina Whipp]

You are missed!

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Yeah, I'd read that in his first post. Erick C and I had a long thread for
getting that code defined a few months back. <smile>

Thanks, Erick, for the mention!

Haven't had much time lately to prowl the newsgroups.....

 

[Ken Snell]

You are missed!

Thanks, I do appreciate your note!