VBA Runtime Error 5 by ExportAsFixedFormat-CodePudding

I have the following problem, in my VBA code it comes to Runtime Error 5 Invalid Procedure call or argument when I want to save a range as PDF. The code looks like this:

Worksheets(2).Range("A1:G103").ExportAsFixedFormat Type:=xlTypePDF, Filename:=path1_1 & "\Idea" & Worksheets(3).Range("B12").Value & ".pdf", OpenAfterPublish:=False

The sheet that is being accessed is hidden. The error message does not occur if Worksheets(2).Visible = True. How can I write the code so that the error message no longer comes up and I don't have to show the sheets? It would also be good to know why the error occurs. I really appreciate your help.

Worksheets(2).Visible = True
Worksheets(2).Range("A1:G103").ExportAsFixedFormat Type:=xlTypePDF, Filename:=path1_1 & "\Idea" & Worksheets(3).Range("B12").Value & ".pdf", OpenAfterPublish:=False
Worksheets(2).Visible = xlVeryHidden

This is how the VBA code would work but unfortunately this is not an option.

CodePudding user response：

There is no way to use ExportAsFixedFormat on a hidden sheet (however the Microsoft doesn't tell about the fact or why this is). So if you want to export the sheet, there is no alternative to make it visible temporarily. However, you can prevent that the user sees the sheet during that time by setting Application.ScreenUpdating = False. To be sure that the sheet is set to invisible in any case (and ScreenUpdating is enabled again), use an error handler.

Sub exportSheet()
    Application.ScreenUpdating = False
    With ThisWorkbook.Sheets(2)
        On Error GoTo ExportError
        .Visible = True
        .Range("A1:G103").ExportAsFixedFormat Type:=xlTypePDF, Filename:="(your path here)", OpenAfterPublish:=False
ExportError:
        .Visible = False
        Application.ScreenUpdating = True
    End With
End Sub

CodePudding user response：

Export to PDF: Very Hidden Worksheet

Sub ExportToPDF()
    
    Const path1_1 As String = "C:\Test"
    
    ' Reference the range.
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets(2)
    Dim rg As Range: Set rg = ws.Range("A1:G103") 
    
    Dim FilePath As String: FilePath = path1_1 & Application.PathSeparator _
        & "Idea" & CStr(wb.Worksheets(3).Range("B12").Value) & ".pdf"
    
    ' Store the row heights and column widths in arrays.
    
    Dim rCount As Long: rCount = rg.Rows.Count
    Dim cCount As Long: cCount = rg.Columns.Count
    
    Dim rgHeights() As Double: ReDim rgHeights(1 To rCount)
    Dim rgWidths() As Double: ReDim rgWidths(1 To cCount)
    
    Dim n As Long
    
    For n = 1 To rCount
        rgHeights(n) = rg.Cells(n, 1).RowHeight
    Next n
    
    For n = 1 To cCount
        rgWidths(n) = rg.Cells(1, n).ColumnWidth
    Next n
    
    ' In a new instance of Excel, add a new workbook
    ' and reference its 1st worksheet and the range.
    
    Dim xlApp As Excel.Application: Set xlApp = New Excel.Application
    'xlApp.Visible = True ' use while developing; default is 'False'
    Dim dwb As Workbook: Set dwb = xlApp.Workbooks.Add
    Dim dws As Worksheet: Set dws = dwb.Sheets(1)
    Dim drg As Range: Set drg = dws.Range("A1").Resize(rCount, cCount)
    
    ' Copy
   
    rg.Copy
    dws.Paste ' copies to cell 'A1'
    Application.CutCopyMode = False
    
    ' Apply the stored row heights and column widths to the copied range.
    
    For n = 1 To rCount
        drg.Rows(n).RowHeight = rgHeights(n)
    Next n
    
    For n = 1 To cCount
        drg.Columns(n).ColumnWidth = rgWidths(n)
    Next n
    
    ' Export
    
    drg.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FilePath ' , _
        OpenAfterPublish:=True ' use while developing; default is 'False'

    ' Close & quit.
    
    dwb.Close SaveChanges:=False
    xlApp.Quit

End Sub