Solved Report Export as Pdf, Export as Excel and Export as Word.

[Michelle Michy]

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.

 

[gemma-the-husky]

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.

 

[Michelle Michy]

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.

Thanks so much. Use the sample database please. I really appreciate. Or if you have a different sample kindly assist me.

 

[Michelle Michy]

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.

Kindly assist me with codes. Please. Thanks in advance.

 

[Michelle Michy]

Hi all

 

[Michelle Michy]

What is happening??

 

[Michelle Michy]

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.

    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

Thanks so much.

 

[Michelle Michy]

You're welcome. Give that example a try and show us what you have if you need more help.

Okay

 

[Michelle Michy]

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.

 

[Michelle Michy]

Please

 

[Michelle Michy]

@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.

Noted

 

[mike60smart]

Please

Hi Michelle

How many people are needing to run these Exports?

 

[The_Doc_Man]

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.

 

[Michelle Michy]

Hi Michelle

How many people are needing to run these Exports?

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]

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.

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

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

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]

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.

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

 

[Michelle Michy]

Thanks. I found the following code and a function but i get an error
"GetUsername"

Function FileExist(FileFullPath As String) As Boolean
  Dim Value As Boolean
  Value = False
  If Dir(FileFullPath) <> "" Then
    Value = True
  End If
  FileExist = Value
End Function
Private Sub cmd_exportPDF_Click()
    Dim fileName As String, fldrPath As String, filePath As String, LValue As String
    Dim answer As Integer
    Dim rst As Recordset
    
    fileName = "ALL BOYS(MALES)-"                     'filename for PDF file*
    fldrPath = "C:\Users\" & GetUserName() & "\Desktop\SCHOOL REPORTS\ALL PUPILS" 'folder path where pdf file will be saved *
    LValue = Now
    
    filePath = fldrPath & "\" & fileName & Format(Date, "dd mmmm yyyy") & ".pdf"
    
    'check if file already exists
    If FileExist(filePath) Then
        answer = MsgBox(Prompt:="PDF file already exists: " & vbNewLine & filePath & vbNewLine & vbNewLine & _
                        "Would you like to replace existing file?", Buttons:=vbYesNo, Title:="Existing PDF File")
        If answer = vbNo Then Exit Sub
    End If
    
    On Error GoTo invalidFolderPath
    DoCmd.OutputTo objecttype:=acOutputReport, objectName:=Me.Name, outputformat:=acFormatPDF, outputFile:=filePath
    
    MsgBox Prompt:="PDF File exported to: " & vbNewLine & filePath, Buttons:=vbInformation, Title:="Report Exported as PDF"
    Exit Sub
    
invalidFolderPath:
    MsgBox Prompt:="ERROR!!!!!", Buttons:=vbCritical
    
End Sub

 

[Michelle Michy]

When I use the following function to
"GetUsername" i get an error to use 64 bit

Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If lngX <> 0 Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = ""
    End If
End Function

 

[Michelle Michy]

Code to check folder but ultimately i need to
"GetUsername"

Private Sub Command135_Click()
MakeDir ("C:\Users\" & GetUserName() & "\Desktop\SCHOOL REPORTS\WHOLE SCHOOL REPORT")
    Dim fileName As String, fldrPath As String, filePath As String, LValue As String
    Dim answer As Integer
    Dim rst As Recordset
    
    fileName = "-"                     'filename for PDF file*
    fldrPath = "C:\Users\" & GetUserName() & "\Desktop\SCHOOL REPORTS\WHOLE SCHOOL REPORT" 'folder path where pdf file will be saved *
    LValue = Now
    
    filePath = fldrPath & "\" & fileName & ""
    
    'check if file already exists
    If FolderExist(filePath) Then
        answer = MsgBox(Prompt:="SCHOOL REPORTS file already exists: " & vbNewLine & filePath & vbNewLine & vbNewLine & _
                        "Would you like to replace existing file?", Buttons:=vbYesNo, Title:="Existing folder File")
        If answer = vbNo Then Exit Sub
    End If
    

    
    MsgBox Prompt:="SCHOOL REPORTS FOLDER HAS BEEN CREATED ON THE DESKTOP OF THIS COMPUTER. YOU WILL ACCESS ALL REPORTS ON SCHOOL REPORTS FOLDER ON THE DESKTOP!!! " & vbNewLine & filePath, Title:="SCHOOL REPORTS FOLDER CREATED SUCCESSFULLY(CONFIRM ON THE DESKTOP)"
    Exit Sub
End Sub

Public Function MakeDir(ByVal STRPATH As String) As Boolean
If Right(STRPATH, 1) = "\" Then
STRPATH = Left(STRPATH, Len(STRPATH) - 1)
End If
Dim SPLITSTRPATH() As String
SPLITSTRPATH = Split(STRPATH, "\")
Dim VAR1 As Integer
Dim MERGE As String
For VAR1 = 0 To UBound(SPLITSTRPATH)
If VAR1 <> 0 Then
MERGE = MERGE & "\"
End If
MERGE = MERGE & SPLITSTRPATH(VAR1)
If Dir(MERGE, vbDirectory) = "" Then
MkDir MERGE
End If
Next
MakeDir = True
Exit Function
End Function

 

[The_Doc_Man]

If you have a lot of file and folder operations, you might find it easier in the long run to use the File System Object, which has simplified calls for a lot of what you are doing. The link gets you to the top of a documentation "tree" for FSO.

FileSystemObject object

Office VBA reference topic

learn.microsoft.com

However, there is another question. I looked at your posts but didn't see it mentioned. Are you using 32-bit or 64-bit Access? The problem you report sounds like you tried to use a 32-bit function in a 64-bit Access environment. If you search the forum for 64-bit Access, you will find lots of posts on using PtrSafe and other methods to keep everything quiet and happy.