CODEWITHSUNDEEP
excelvbaruntime-error
J.schmidt
J.schmidt

Reputation: 719

ExportAsFixedFormat generates run time error: file is read only

When the user clicks the "Print Button" the activesheet should be printed as PDF.

I am having a problem with the .ExportAsFixedFormat method. I am getting the following error message:

Run time error '-2147018887 (80071779)': automation error, file is read only

This is the code:

Sub PrintButtonClick()
'++++Print to PDF Function++++
'For more Information: https://learn.microsoft.com/de-de/office/vba/api/excel.worksheet.exportasfixedformat
'DEPENDS ON LOCATION OF HEADING (Heading row index/column index)
'allows to print material information, NO general list of materials

    DataBaseSheet.Unprotect password:=pw

    'PRINT PROCEDURE:
    If DataBaseSheet.Cells(5, 5).value = "Print" Then

        'Error in following line!!!!!
        DataBaseSheet.ExportAsFixedFormat Type:=xlTypePDF, fileName:= _
          DataBaseSheet.Cells(5, 5) & ".pdf", Quality:=xlQualityStandard, _
          IncludeDocProperties:=True, IgnorePrintAreas:=True, _ 
          OpenAfterPublish:=True

    Else: MsgBox "You cannot print this sheet"
    End If

    DataBaseSheet.Cells(2, 2).Locked = False
    DataBaseSheet.Protect password:=pw

End Sub

Upvotes: 1

Views: 1648

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149315

You are getting that error because you are trying to overwrite a pdf with the same name which is currently open? When I say OPEN, I do not mean open in a web browser but open in applications like Adobe Reader etc... Close the open file and then try again :)

Alternatively, check in the code if the file is open or not and then try to write it. See this example.

Sub PrintButtonClick()
    Dim pdfFileName As String

    With DataBaseSheet
        .Unprotect Password:=pw
        pdfFileName = .Cells(5, 5).Value

        If pdfFileName = "Print" Then
            pdfFileName = .Cells(5, 5) & ".pdf"
            If IsPDFOpen(pdfFileName) Then
                MsgBox "A pdf with the same name is currently open. Please close that and try again"
            Else
                .ExportAsFixedFormat Type:=xlTypePDF, FileName:=pdfFileName, _
                                     Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                                     IgnorePrintAreas:=True, OpenAfterPublish:=True
            End If
        Else
            MsgBox "You cannot print this sheet"
        End If

        .Cells(2, 2).Locked = False
        .Protect Password:=pw
    End With
End Sub

'~~> Function to check if the pdf with same name is open
Function IsPDFOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 70:   IsPDFOpen = True
    Case Else: IsPDFOpen = False
    End Select
End Function

Upvotes: 1

Related Questions