none
ExportAsFixedFormat method throws invalid procedure call error at run time RRS feed

  • Question

  • Public Sub Test()

    ThisWorkbook.Worksheets("sheet1").ExportAsFixedFormat Type:=xlTypePDF

    End Sub

    This code generates error

    Run time error 5

    Invalid procedure call or argument

    ----Please let me know if someone knows the solution.----

    Office Product: MS office professional plus 2013

    Operating system: Windows 7 enterprise -Service Pack 1

    I have tried all following steps, but error still persists

    1) Installing PDF com addin. It just creates Acrobat tab in ribbon.

    2) Repairing/Reinstalling PDF application

    3) Repair/Reinstall office

    4) Reinstall PDF printer Driver

    Note: I do not see PDF options in my Excel (I have XPS), in the list of 'SAVE AS' file types when you try to save an EXCEL file.

    Surprisingly the same code works on one of our vendors laptop and we can not figure out why.

    Any help greatly appreciated

    Thanks

    NJ

    Tuesday, October 25, 2016 4:36 PM

All replies

  •     

    Hi NJ,

    I've tried to export to PDF. It worked.
      1. insert a button (ActiveX control) on "sheet1".
         
      2. write VBA (includes almost all parameters)

    ' --[Export to PDF] button
    Private Sub btn_ExportPDF_Click()
        ThisWorkbook.Worksheets("sheet1").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:="test.pdf", _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                From:=1, To:=1, _
                OpenAfterPublish:=True
    End Sub
    # I've used Excel 2016. Later I'll do it with Excel 2013.

    Regards,

    Ashidacchi


    (added later)
    # I've just succeeded with Excel 2013 using the same file.

    • Edited by Ashidacchi Tuesday, October 25, 2016 11:41 PM
    Tuesday, October 25, 2016 10:26 PM

    Hi NJ2016,

    This error is not related with your code.

    your code is correct and worked without any error when I test it.

    Following are the reasons to occur this error.

    The problem occurs when the function is lost and is not mapped to any drive. As a result, Windows is unable to find and load it and displays the error message. Runtime Error 5 is also caused by other reasons listed below as follows.

    • When files of programs like Visual Basic use bad entries in the registry.
    • When a user attempts to load Internet Explorer and a global cash of browser occurs, this also causes Runtime Error 5.
    • Corrupt download or incomplete installation of Windows Operating System software.
    • Corruption in Windows registry from a recent Windows Operating System-related software change (install or uninstall).
    • Virus or malware infection that has corrupted Windows system files or Windows Operating System-related program files.
    • Another program maliciously or mistakenly deleted Windows Operating System-related files.

    you had already mentioned that you tried to reinstall the MS office but it did not worked.

    you not need to download any addin or software to export file as PDF.

    • you can try to create a new file on trusted location and try to run the above mentioned code.
    • if that not work then you can try to repair the Registry Entries.
    • if any windows or Ms office updates are available then update it.
    • you can try to run windows system file checker ("sfc/scannow") for runtime error 5.
    • if none of the above suggestion work then I am afraid you need to format the machine and install a clean copy of windows operating system will solve the issue.

    Reference:

    Invalid procedure call or argument (Error 5)

    Run-Time Error 5: Invalid Procedure Call or Argument

    Regards

    Deepak

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, October 26, 2016 8:48 AM
    Moderator
  • Hello Ashidacchi, I tried following your steps but I am still getting the error. I think it's not about the code itself but something is off on my machine that I am not able to figure out.  Because as i mentioned the same code is working on my vendors laptop.

    I was wondering if I am missing any office components or any addins.

    Thanks

    NJ

    Wednesday, October 26, 2016 7:46 PM
  • Deepak, thanks a lot for the direction. I will have to try all different options and see what works or not

    Thanks

    NJ

    Wednesday, October 26, 2016 7:47 PM
  • Hi NJ,

    in your last post you had mentioned that,"I was wondering if I am missing any office components or any addins."

    as I mentioned in my last post no any extra software , Addins required to Export Excel sheet as an PDF.

    that one line VBA code is enough to do that.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 27, 2016 4:26 AM
    Moderator
  • I am not sure if you were able to resolve the issue but I came across similar issue and the culprit in my case was that my sheet was hidden.

    So by adding the following code, I was able to print hidden sheets to PDF:

    ThisWorkbook.Worksheets("Sheet1").Visible = True
    
    'Your code to print sheet goes here
    
    ThisWorkbook.Worksheets("Sheet1").Visible = False

    Hope this helps you and others like me.

    Regards,
    Ehtesham


    Friday, October 20, 2017 2:53 AM