Solved Report Export as Pdf, Export as Excel and Export as Word. (1 Viewer)

M

Michelle Michy

Member
Local time
Today, 08:27
Joined
May 11, 2023
Messages
46
Hi all. I am kindly requesting anyone to assist me with vba code to export report as pdf and export as Excel and export as word. If there is any drop-down for all of them will be appreciated. Kindly please I am requesting.

Kindly look at the attached database. Thanks in advance.
 

Attachments

  • Database_091550.zip
    46.9 KB · Views: 83
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 06:27
Joined
Sep 12, 2006
Messages
15,656
I normally have an option group with a small range of choices like that, and then a "Run/Go" command button, but then you need to run different code in the click event for the button depending on the selected option.

Pdf (or just print now) is normally an option for a report and spreadsheet an option for a query. I don't understand why you would want a word output as an option. It may be an option for a report but I can't recall ever using it.

Select case grpOutputchoice
Case 1: code
Case 2: code
Case 3: code
End select

Actually, I tend to have a generic output proc that accepts a queryname, and an option number, so the code can be easily reused.

So the above code is more like
Case 1: processoutput(1,queryname)
Case 2: processoutput(2,reportname)
Case 3: processoutput(3,reportname,sendtostring)

Option 1 might save a query as an excel file
Option 2 might be to prepare a report as pdf only.
Option 3 might be to prepare the pdf and send it in an email.

The number options in the option group are not necessarily the same values in the code block.
 
Last edited:
M

Michelle Michy

Member
Local time
Today, 08:27
Joined
May 11, 2023
Messages
46
gemma-the-husky said:
I normally have an option group with a small range of choices like that, and then a "Run/Go" command button, but then you need to run different code in the click event for the button depending on the selected option.

Pdf (or just print now) is normally an option for a report and spreadsheet an option for a query. I don't understand why you would want a word output as an option. It may be an option for a report but I can't recall ever using it.

Select case grpOutputchoice
Case 1: code
Case 2: code
Case 3: code
End select

Actually, I tend to have a generic output proc that accepts a queryname, and an option number, so the code can be easily reused.

So the above code is more like
Case 1: processoutput(1,queryname)
Case 2: processoutput(2,reportname)
Case 3: processoutput(3,reportname,sendtostring)

Option 1 might save a query as an excel file
Option 2 might be to prepare a report as pdf only.
Option 3 might be to prepare the pdf and send it in an email.

The number options in the option group are not necessarily the same values in the code block.
Click to expand...
Thanks so much. Use the sample database please. I really appreciate.🙏 Or if you have a different sample kindly assist me.🙏
 
M

Michelle Michy

Member
Local time
Today, 08:27
Joined
May 11, 2023
Messages
46
gemma-the-husky said:
I normally have an option group with a small range of choices like that, and then a "Run/Go" command button, but then you need to run different code in the click event for the button depending on the selected option.

Pdf (or just print now) is normally an option for a report and spreadsheet an option for a query. I don't understand why you would want a word output as an option. It may be an option for a report but I can't recall ever using it.

Select case grpOutputchoice
Case 1: code
Case 2: code
Case 3: code
End select

Actually, I tend to have a generic output proc that accepts a queryname, and an option number, so the code can be easily reused.

So the above code is more like
Case 1: processoutput(1,queryname)
Case 2: processoutput(2,reportname)
Case 3: processoutput(3,reportname,sendtostring)

Option 1 might save a query as an excel file
Option 2 might be to prepare a report as pdf only.
Option 3 might be to prepare the pdf and send it in an email.

The number options in the option group are not necessarily the same values in the code block.
Click to expand...
Kindly assist me with codes. Please. Thanks in advance.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:27
Joined
Feb 19, 2002
Messages
43,275
Here's a form
1686088144831.png


Here's the part of the code from the case statement that is executed when the user double clicks on the name. As you can see by the code, not everything is available in all formats. The form is bound to a table that lists all the outputs and includes other information on what output options are available. For the Export to Excel and List Selected Subscribers, the underlying table includes the name of the query that includes the criteria needed for the export. Separate queries are used when exporting to Excel because the dates and money amounts need formatting.
Code: 
    Select Case frm.fraOutputTo
        Case 1  'open form
            If rs!DisplayType = "Report" Then   'some selections can only be displayed as reports
                DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
            Else
                stDocName = frm.txtFormToOpen
                frm.Visible = False
                DoCmd.OpenForm stDocName, , , , , acWindowNormal, frm.Name
            End If
        Case 2  'preview report
            If rs!DisplayType = "Form" Then 'some selections can only be displayed as forms
                stDocName = frm.txtFormToOpen
                frm.Visible = False
                DoCmd.OpenForm stDocName, , , , , acWindowNormal, frm.Name
            Else
                DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
            End If
        Case 3  'print report
            If rs!DisplayType = "Form" Then 'some selections can only be displayed as forms
                stDocName = frm.txtFormToOpen
                frm.Visible = False
                DoCmd.OpenForm stDocName, , , , , acWindowNormal, frm.Name
            Else
                DoCmd.OpenReport stDocName, acViewNormal, , stLinkCriteria
            End If
        Case 4  'export to Excel
            If IsNull(rs!exportquery) Then      '
                MsgBox "Export is not available for this report.", vbOKOnly + vbInformation
            Else
'                ThisDBPath = Left(CodeDb.Name, InStrRev(CodeDb.Name, "\"))
'                ExportFileName = ThisDBPath & Forms!frmLogin!cboAuditParmsID.Column(4)
                DoCmd.OpenForm "frmGetDirectory2", acNormal, , , , acDialog
                ExportFileName = Forms!frmLogin!txtPath
                If ExportFileName & "" = "" Then
                    MsgBox "Path name was not provided.  Export cancelled.", vbOKOnly + vbCritical
                    rs.Close
                    GoTo lstReports_DblClick_Exit
                End If
                ExportFileName = ExportFileName & "-" & rs!ExportFileName & "-" & Format(Date, "yymmdd") & ".XLS"
                If Dir(ExportFileName) <> "" Then Kill ExportFileName
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rs!exportquery, ExportFileName, True
                MsgBox "File Exported to ---> " & ExportFileName, vbOKOnly + vbInformation
            End If
        Case 5  ' pdf
                DoCmd.OpenForm "frmGetDirectory2", acNormal, , , , acDialog
                ExportFileName = Forms!frmLogin!txtPath
                If ExportFileName & "" = "" Then
                    MsgBox "Path name was not provided.  Export cancelled.", vbOKOnly + vbCritical
                    rs.Close
                    GoTo lstReports_DblClick_Exit
                End If
                ExportFileName = ExportFileName & "-" & rs!ExportFileName & "-" & Format(Date, "yymmdd") & ".pdf"
                If Dir(ExportFileName) <> "" Then Kill ExportFileName
                'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rs!ExportQuery, ExportFileName, True
                DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, ExportFileName, False
                MsgBox "File Exported to ---> " & ExportFileName, vbOKOnly + vbInformation
        Case Else   ' no output type selected
            MsgBox "Please select an output type.", vbOKOnly + vbInformation
            Exit Sub
    End Select
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:27
Joined
Feb 19, 2002
Messages
43,275
PS, you're pretty new here. Welcome. Please keep in mind that everyone here who answers your questions is a volunteer and probably has a paying job so don't expect instantaneous "service". Posters who make the effort to help themselves are always happier with their interactions with forum experts.
 
M

Michelle Michy

Member
Local time
Today, 08:27
Joined
May 11, 2023
Messages
46
Pat Hartman said:
Here's a form
View attachment 108318

Here's the part of the code from the case statement that is executed when the user double clicks on the name. As you can see by the code, not everything is available in all formats. The form is bound to a table that lists all the outputs and includes other information on what output options are available. For the Export to Excel and List Selected Subscribers, the underlying table includes the name of the query that includes the criteria needed for the export. Separate queries are used when exporting to Excel because the dates and money amounts need formatting.
Code: 
    Select Case frm.fraOutputTo
        Case 1  'open form
            If rs!DisplayType = "Report" Then   'some selections can only be displayed as reports
                DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
            Else
                stDocName = frm.txtFormToOpen
                frm.Visible = False
                DoCmd.OpenForm stDocName, , , , , acWindowNormal, frm.Name
            End If
        Case 2  'preview report
            If rs!DisplayType = "Form" Then 'some selections can only be displayed as forms
                stDocName = frm.txtFormToOpen
                frm.Visible = False
                DoCmd.OpenForm stDocName, , , , , acWindowNormal, frm.Name
            Else
                DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria
            End If
        Case 3  'print report
            If rs!DisplayType = "Form" Then 'some selections can only be displayed as forms
                stDocName = frm.txtFormToOpen
                frm.Visible = False
                DoCmd.OpenForm stDocName, , , , , acWindowNormal, frm.Name
            Else
                DoCmd.OpenReport stDocName, acViewNormal, , stLinkCriteria
            End If
        Case 4  'export to Excel
            If IsNull(rs!exportquery) Then      '
                MsgBox "Export is not available for this report.", vbOKOnly + vbInformation
            Else
'                ThisDBPath = Left(CodeDb.Name, InStrRev(CodeDb.Name, "\"))
'                ExportFileName = ThisDBPath & Forms!frmLogin!cboAuditParmsID.Column(4)
                DoCmd.OpenForm "frmGetDirectory2", acNormal, , , , acDialog
                ExportFileName = Forms!frmLogin!txtPath
                If ExportFileName & "" = "" Then
                    MsgBox "Path name was not provided.  Export cancelled.", vbOKOnly + vbCritical
                    rs.Close
                    GoTo lstReports_DblClick_Exit
                End If
                ExportFileName = ExportFileName & "-" & rs!ExportFileName & "-" & Format(Date, "yymmdd") & ".XLS"
                If Dir(ExportFileName) <> "" Then Kill ExportFileName
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rs!exportquery, ExportFileName, True
                MsgBox "File Exported to ---> " & ExportFileName, vbOKOnly + vbInformation
            End If
        Case 5  ' pdf
                DoCmd.OpenForm "frmGetDirectory2", acNormal, , , , acDialog
                ExportFileName = Forms!frmLogin!txtPath
                If ExportFileName & "" = "" Then
                    MsgBox "Path name was not provided.  Export cancelled.", vbOKOnly + vbCritical
                    rs.Close
                    GoTo lstReports_DblClick_Exit
                End If
                ExportFileName = ExportFileName & "-" & rs!ExportFileName & "-" & Format(Date, "yymmdd") & ".pdf"
                If Dir(ExportFileName) <> "" Then Kill ExportFileName
                'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, rs!ExportQuery, ExportFileName, True
                DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, ExportFileName, False
                MsgBox "File Exported to ---> " & ExportFileName, vbOKOnly + vbInformation
        Case Else   ' no output type selected
            MsgBox "Please select an output type.", vbOKOnly + vbInformation
            Exit Sub
    End Select
Click to expand...
Thanks so much.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:27
Joined
Feb 19, 2002
Messages
43,275
You're welcome. Give that example a try and show us what you have if you need more help.
 
M

Michelle Michy

Member
Local time
Today, 08:27
Joined
May 11, 2023
Messages
46
I have been trying for the last two days but I am defeated. I am still begging for someone to assist me with vba using the attached database. Thanks in advance.
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:27
Joined
Feb 19, 2002
Messages
43,275
@Michelle Michy You're getting off to a bad start here. The folks who answer your questions are unpaid volunteers. If you aren't willing to jump in and try to help yourself when given the code you need, you probably won't find anyone willing to work for you for free. The DoCmd you need to export to PDF is there. The same command will export to word or Excel but you won't like the results.
 
M

Michelle Michy

Member
Local time
Today, 08:27
Joined
May 11, 2023
Messages
46
Pat Hartman said:
@Michelle Michy You're getting off to a bad start here. The folks who answer your questions are unpaid volunteers. If you aren't willing to jump in and try to help yourself when given the code you need, you probably won't find anyone willing to work for you for free. The DoCmd you need to export to PDF is there. The same command will export to word or Excel but you won't like the results.
Click to expand...
Noted
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:27
Joined
Feb 28, 2001
Messages
27,186
One VERY big issue in a three-way export is this: Access is not Excel and it is not Word. Their internal data structures are all very different. Therefore, exporting an Access report to something that isn't Access will go through a filter that will remove some of the things you have in Access. Yes, you CAN INDEED do all three exports. But two of them will not look as good as the Access report, which is native to the export process.
 
M

Michelle Michy

Member
Local time
Today, 08:27
Joined
May 11, 2023
Messages
46
mike60smart said:
Hi Michelle

How many people are needing to run these Exports?
Click to expand...
The users of the database may wish to analyze reports via Excel and Pdf. I would have wished somebody to help me with vba code to export as Excel and Pdf only. Thanks.
 
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:27
Joined
Feb 28, 2001
Messages
27,186
Michelle, as Pat noted, you ARE getting help. You just want it handed to you on a platter, but we don't always do that here. We don't all have the time to write code for everyone who comes along. Here is a link to a page that tells you the command you need and how to use it.

learn.microsoft.com

DoCmd.OutputTo method (Access)

Office VBA reference topic
learn.microsoft.com

And here is a different page.

OutputTo | Microsoft Access VBA Programming for the Absolute Beginner

Microsoft Access VBA Programming for the Absolute Beginner,2006, (isbn 1598633937, ean 1598633937), by Brown C. E., Petrusha R.
flylib.com flylib.com

If you have a report you could use supply the name of the report.

Code: 
DoCmd.OutputTo acOutputReport, report-object-name, acFormat, etc.

You can read the links to figure out what else you need for each of the arguments. The acFormat option allows you to pick the format you want for output, in the sense of as an Access report or an XLS file or a PDF, etc. To find out what acFormat options you can use, open your VBA page and open the Object Browser (which is on the menu bar of the VBA page). Look (using ALL LIBRARIES - it's easier) for acFormat. Object Browser will show you the names you can use for the various output types. With a word of warning... using the Object Browser tells you what your version of Access could possibly do. If you don't see the option you want, it isn't defined for your version of Access. (And depending on which version you have, some newer formats might not be available.)
 
mike60smart

mike60smart

Registered User.
Local time
Today, 06:27
Joined
Aug 6, 2017
Messages
1,905
Michelle Michy said:
The users of the database may wish to analyze reports via Excel and Pdf. I would have wished somebody to help me with vba code to export as Excel and Pdf only. Thanks.
Click to expand...
Hi Michelle
In the attached I have added Code to Export to PDF.

You need to create a folder on your C: Drive as follows:

C:\temp\PDFs

Hope this helps
 

Attachments

  • Export.zip
    58 KB · Views: 83
You must log in or register to reply here.

Similar threads

A
Trouble Exporting Sorted Data from Access Form with Subforms to Excel
Replies
2
Views
99
ebs17
E
Gasman
Solved Exporting a form as text and then importing.?
Replies
15
Views
154
cheekybuddha
cheekybuddha
S
Solved Export from Ms Access form in to Excel
Replies
5
Views
303
skdn71
S
E
Filter
Replies
16
Views
413
strive4peace
strive4peace
R
Solved Importing and calculating the data
Replies
12
Views
514
Robrobby
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom