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

▲
0
▼

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

excel
vb.net
asked on Stack Overflow Jan 13, 2017 by user7415753 • edited Jan 13, 2017 by user7415753

2 Answers

▲
0
▼

Save it as .PDF like that:

xlWorkSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, "d:\test\test.pdf")
answered on Stack Overflow Jan 13, 2017 by jonathana
▲
0
▼

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!

answered on Stack Overflow Jan 13, 2017 by user7415753

User contributions licensed under CC BY-SA 3.0