VB.net Excel ExportAsFixedFormat fails Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))

0
votes

I am trying to write a very simple VB.net app which would open an excel file and save it as excel.

The environment I am working on is as follows:

  • Windows 10
  • Visual Studio 2016
  • Office 2016

I have successfully managed to open the excel sheet and save to another location. However when trying to open the excel and save as pdf, I get the following error message

The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))

Looking into the details of the error, I can see the following:

System.Runtime.InteropServices.COMException {"The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT))"}

error code: -2147417851

The code I am using to do this is as follows:

Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    xlApp.Visible = False
    xlApp.AlertBeforeOverwriting = False

    Dim sheetname As String = "d:\test\test.xlsx"

    xlWorkBook = xlApp.Workbooks.Open(sheetname)


    xlWorkBook.Activate()
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")
    xlWorkSheet.Activate()
    xlApp.DisplayAlerts = False
 xlWorkSheet.ExportAsFixedFormat(Type:=Excel.XlFixedFormatType.xlTypePDF, Filename:=
    "d:\test\test.pdf", Quality:=Excel.XlFixedFormatQuality.xlQualityStandard _
    , IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=
    False)





    xlWorkBook.Close(SaveChanges:=False)
    xlApp.Quit()
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp) : xlApp = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook) : xlWorkBook = Nothing
    System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet) : xlWorkSheet = Nothing

Help!

issue seem to arise due to the Excel sheet having formulas, copying and pasting the content into a new sheet (paste special values and number formatting only) and running the code above works

2
excelvb.net
I have managed to replicate your issue with Excel 2016. I first tried it with a blank sheet. I got the error. I then popped some data in (as normally you would have data) and the error did not occur. Have you any data in your worksheet? – Bugs
Yes, there's data in there. Did you manage to get it working (with data in sheet) with the code above or did you need to change anything? – user7415753
No I didn't. Only thing was I installed the PDF Add on for Excel 2007 which I didn't think would be relevant but I did nonetheless (and rebooted machine). – Bugs
Is D on your local PC or on another? I used my C drive. I'm guessing D is local. – Bugs
I'll try to install the add-in and yes the d drive is a local drive – user7415753

2 Answers

0
votes

Save it as .PDF like that:

xlWorkSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, "d:\test\test.pdf")
0
votes

Right! Thank you to all that tried to help. By reading your responses I kept trying till I figured out the issue.

It appears that ExportAsFixedFormat doesn't like Excel sheets with formulas in. To work around this issue, I just created a new blank worksheet and copied and pasted (values only) the content from my main sheet into it. This seems to work perfectly. I then automated this in my code as follows:

    Dim xlworksheet_static As Excel.Worksheet = xlWorkBook.Worksheets(2)
    xlWorkSheet.Range("A1", "H35").Copy()
    xlworksheet_static.Activate()
    xlworksheet_static.Range("A1", "H35").Select()
    xlworksheet_static.PasteSpecial(Excel.XlPasteType.xlPasteValuesAndNumberFormats)

    xlworksheet_static.ExportAsFixedFormat(Type:=Excel.XlFixedFormatType.xlTypePDF, Filename:=
     "d:\test\test.pdf", Quality:=Excel.XlFixedFormatQuality.xlQualityStandard _
    , IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=
    False)

Probably not the most straight forward way but I couldn't get it to work otherwise!