Export excel Workbook to PDF error

troyh68

New Member
Joined
Nov 14, 2016
Messages
18
To start this is not my Macro but a macro that I have "inherited" and need to fix the macro.
With that said the macro is supposed to take all of the different Tabs in the workbook (5 tabs total) and create a PDF of the results of the data. The error is attached as an image as is the Module Window with the error pointed to the line. The full code is below. When I debug the code it is breaking on the last line of the PDF export process on the OpenAfterPublish:=True Line. I have tried a couple of different things.
1. I chose False for OpenAfterPublish The PDF didn't open and it did not save the pdf to the target location.
2. I tried With Application.DisplayAlerts = False The PDF file was still not opened or saved to the target location
3. I tried the With Application.DisplayAlerts and OpenAfterPublish:= True same error

VBA Code:
Sub make_report()

    Dim companyname, reportname As String
    Dim bli, bli2 As Integer
    
    bli = 0
    bli = 0
 
    companyname = Worksheets("Report").Range("C2:C2")
    'bli YES=6=vbYes NO=7=vbNo
    bli = MsgBox(companyname, vbYesNo, "Company Name correct?")
    'MsgBox bli, 0, "bli"
    
    If (bli = 6) Then
        reportname = Names.Parent.Path & "\" & Worksheets("Report").Range("C2:C2") & "_" & _
                Worksheets("Report").Range("B4:B4") & "_Fraud_Report.pdf"
        
        'bli OK=1=vbOK Cancel=2=vbCancel
        bli2 = MsgBox(reportname, vbOKCancel, "Report FileName:")
      
        If (bli2 = 1) Then
            ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
            Filename:=reportname, _
            Quality:=xlQualityStandard, _
            From:=1, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True
        Else
            MsgBox "NO Fraud Report made", 0, "Report Cancelled"
        End If
    Else
            MsgBox "NO Fraud Report made", 0, "Report Cancelled"
    End If
    

End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,655
Office Version
  1. 365
Platform
  1. Windows
I have tried your print code and in principle the code works fine. I suspect your issue is likely to be with the filename.

After your reportname = statement put in the statement

VBA Code:
debug.print reportname

and see if what you get in the immediate window makes sense.

If you are not sure copy what you get a post it back in a reply.
Onedrive or Sharepoint file paths are definitely going to be an issue if that is where you are trying to send it.

PS: Unless you really want the pdf that was created open to be viewed you might want to change OpenAfterPublish:=True back to False
 
Solution

troyh68

New Member
Joined
Nov 14, 2016
Messages
18
I have tried your print code and in principle the code works fine. I suspect your issue is likely to be with the filename.

After your reportname = statement put in the statement

VBA Code:
debug.print reportname

and see if what you get in the immediate window makes sense.

If you are not sure copy what you get a post it back in a reply.
Onedrive or Sharepoint file paths are definitely going to be an issue if that is where you are trying to send it.

PS: Unless you really want the pdf that was created open to be viewed you might want to change OpenAfterPublish:=True back to False
Alex,
Thank you for the help. I will try your suggestions and post results.

Troy
 

troyh68

New Member
Joined
Nov 14, 2016
Messages
18
Alex,
Thank you for the help. I will try your suggestions and post results.

Troy
Alex,
I ran the reportname through the Immediate window and it returned 'False'. I will change the file name creation. In the back of my mind I didn't like that bit of code anyway so I will fix that.

Thank you,
Troy
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,655
Office Version
  1. 365
Platform
  1. Windows
Thanks for letting me know. Glad you were able to get it working.
 

Forum statistics

Threads
1,140,993
Messages
5,703,606
Members
421,305
Latest member
ambuj Thakur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top