0

I simply want to unhide a sheet that contains a report then export that sheet as a pdf then hide it once again and go back to another sheet, however when running this code I receive a "RunTime Error 1004 Application-defined or object-defined error"

Sub Print_To_PDF()


Sheets("Report").Visible = True
Sheets("Report").Select

Dim pdfname As String

pdfname = Range("A1").Value


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=pdfname, Quality:=xlQualityStandard, _
IncludeDocProperties:=False, IgnorePrintAreas:=False, OpenAfterPublish:=True

Sheets("Report").Visible = False
Sheets("Summary").Select

End Sub

It works sometimes and even on other computers not sure why I am getting this error. Can someone please help.

Chase Ashley
  • 17
  • 3
  • 1
    What does `Debug.print pdfname` output in the Immediate Window? Likely shouldn't be selecting here or using `ActiveSheet` explicitly or implicitly: `pdfname = Sheets("Report").Range("A1").Value`, `Sheets("Report").ExportAsFixedFormat...` – BigBen Sep 20 '22 at 17:25
  • Hey thanks for your answer, debug print simply prints the value in cell A1 to be used as the name of the pdf file. I tried ur suggestions by redefining pdfname as well as replacing ActiveSheet with Sheets("Report") but I am still receiving the same error, sigh. – Chase Ashley Sep 20 '22 at 17:37
  • Is your filename valid? Does it contain any illegal characters? – BigBen Sep 20 '22 at 17:38
  • No no illegal characters, this same code works on other computers not seeing why it isn't working on mine. – Chase Ashley Sep 20 '22 at 17:45
  • Do you have write permission for the destination folder? – BigBen Sep 20 '22 at 17:46
  • Im not sure, how would i determine the destination folder from this code? – Chase Ashley Sep 20 '22 at 17:48
  • 1
    Maybe you should specify the full path when saving and not just the filename then? – BigBen Sep 20 '22 at 17:51
  • Yes by specifying the path it is now working thanks so much ! – Chase Ashley Sep 20 '22 at 18:07

2 Answers2

0

This works fine for me:

Sub Print_To_PDF()
    
    Dim pdfname As String

    pdfname = Range("A1").Value 'ideally specify a worksheet here!
    
    With ThisWorkbook.Worksheets("Report")
        .Visible = True
        .ExportAsFixedFormat Type:=xlTypePDF, _
           Filename:=pdfname, Quality:=xlQualityStandard, _
           IncludeDocProperties:=False, IgnorePrintAreas:=False, _
           OpenAfterPublish:=True
        .Visible = False
    End With

End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

As best practice in the Excel object library (VBA or any COM interfacing code), always qualify objects such as Workbooks, Worksheets, Ranges, QueryTables, PivotTables, and other objects to the parent context. See this canonical post, How to avoid using Select in Excel VBA for readability, maintainability, and reliability.

Therefore, consider explicitly qualifying each object or use a With...End With block. Additionally, consider proper error handling which can provide further information for your debugging needs.

Sub Print_To_PDF()
On Error Goto ErrHandle:
    Dim pdfname As String

    With ThisWorkbook.Sheets("Report")
        .Visible = True
        pdfname = .Range("A1").Value

        .ExportAsFixedFormat _
             Type:=xlTypePDF, _
             Filename:=pdfname, _
             Quality:=xlQualityStandard, _
             IncludeDocProperties:=False, _
             IgnorePrintAreas:=False, _
             OpenAfterPublish:=True

        .Visible = False
    End With

    ThisWorkbook.Sheets("Summary").Select

ExitHandle:
    Exit Sub

ErrHandle:
    Msgbox Err.Number & ": " & Err.Description, vbCritical
    Resume ExitHandle
End Sub
Parfait
  • 104,375
  • 17
  • 94
  • 125
  • Thanks for the response however I am still getting RunTime Error 1004 Application-defined or object-defined error in the msg box – Chase Ashley Sep 20 '22 at 17:49