[RESOLVED] Error System.Runtime.InteropServices.COMException: ExportAsFixedFormat

[NicoVB.Net]

Hi,
My VB.Net program in Visual Studio creates Excel files and then saves them as PDF but after so many(30, 40 or whatever, not the point) it errors out saying "Error System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'".
Probably I am thinking it is the way Excel gets closed/released?
The error is from this line of code after I open an Excel document and try to save it as PDF. Again, that always happens after some documents have been already saved as PDF(sometimes 30, sometimes 50...):

xwb.ActiveSheet.ExportAsFixedFormat(0, "\\ken-resourcesan\fileshares\fieldshare\IT\nsantagata\ARStatements_CustomerInvoicesPDF\" & originalCustomerName & " " & customerNumber & " " & todaysDate & ".pdf")

Here is the whole code I have:

THIS CREATES THE EXCEL DOCUMENT AND FILLS IT WITH DATA:

Public Sub PopulateSheet(ByVal dt As Data.DataTable, ByVal File As String)
        Dim oXL As Excel.Application = CType(CreateObject("Excel.Application"), Excel.Application)
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range
        oWB = oXL.Workbooks.Add
        oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)

'******Spreadsheet gets populated
......

'******Then
oWB.SaveAs(File)

oRng = Nothing
oXL.Quit()

GC.Collect()
GC.WaitForPendingFinalizers()

Marshal.FinalReleaseComObject(oXL)
Marshal.FinalReleaseComObject(oSheet)
Marshal.FinalReleaseComObject(oWB)

oSheet = Nothing
oWB = Nothing
oXL = Nothing

FINALLY THIS SAVES THE DOCUMENT AS PDF:

Dim xl As Object
Dim xwb As Object
xl = CreateObject("Excel.Application")

dt = CreateTable()

PopulateSheet(dt, "\\ken-resourcesan\fileshares\fieldshare\IT\nsantagata\ARStatements_CustomerInvoicesExcel\" & originalCustomerName & " " & customerNumber & " " & todaysDate & ".xlsx")

'***Open xlsx doc to save as pdf
xwb = xl.Workbooks.Open("\\ken-resourcesan\fileshares\fieldshare\IT\nsantagata\ARStatements_CustomerInvoicesExcel\" & originalCustomerName & " " & customerNumber & " " & todaysDate & ".xlsx")

xwb.ActiveSheet.PageSetup.Zoom = False
xwb.ActiveSheet.PageSetup.FitToPagesWide = 1
xwb.ActiveSheet.PageSetup.FitToPagesTall = False

'***Save as pdf
xwb.ActiveSheet.ExportAsFixedFormat(0, "\\ken-resourcesan\fileshares\fieldshare\IT\nsantagata\ARStatements_CustomerInvoicesPDF\" & originalCustomerName & " " & customerNumber & " " & todaysDate & ".pdf")

xl.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(xl)
xl = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(xwb)
xwb = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()

Any help highly appreciated. Thank you

*****COMMENT: I feel there is a problem with how Excel gets released/closed at the end of the two Excel processes. I thought that because the program runs fine and saves the Excel files as PDF, but every time it never creates all the files. It stops after a number of PDF files get created at the line where there is "ExportAsFixedFormat". It never stops at the same specific file, so I would say there isn't a problem with any specific PDF.

[NicoVB.Net]

In relation to the thread I submitted:
I feel there is a problem with how Excel gets released/closed at the end of the two Excel processes. I thought that because the program runs fine and saves the Excel files as PDF, but every time it never creates all the files. It stops after a number of PDF files get created at the line where there is "ExportAsFixedFormat". It never stops at the same specific file, so I would say there isn't a problem with any specific PDF.

[TysonLPrice]

Take a look at task manager and see if you have multiple Excel applications running.

[NicoVB.Net]

In relation to the thread I submitted:
I feel there is a problem with how Excel gets released/closed at the end of the two Excel processes. I thought that because the program runs fine and saves the Excel files as PDF, but every time it never creates all the files. It stops after a number of PDF files get created at the line where there is "ExportAsFixedFormat". It never stops at the same specific file, so I would say there isn't a problem with any specific PDF.

[NicoVB.Net]

replied in the right area

[NicoVB.Net]

Comment deleted as it was duplicate.

[NicoVB.Net]

Take a look at task manager and see if you have multiple Excel applications running.

Hi,
I added the two blocks of code below and I do not have multiple Excel open in the Task Manager, but it still stops at "ExportAsFixedFormat" line after creating a number of PDF files.

I added this code in the first part of the code that is in my question:

System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng)
oRng = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet)
oSheet = Nothing
oWB.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB)
oWB = Nothing
oXL.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL)
oXL = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()

And I put this code in the second part of the code:

xwb.Close(False)
xwb = Nothing
xl.Quit()
xl = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()

[NicoVB.Net]

Duplicate

[techgnome]

Comment deleted as it was duplicate.

Duplicate

If you're getting these dupes because of the "Please wait ## seconds between posts"... just stop... if you get that, and scroll down... you'll see that the post DID go through... it's a known issue... there's no need to hit the submit again on posts.

-tg

[ChrisE]

if you have a Datatable then pass the Datatable directly to .PDF
use iTextSharp.dll

there are many examples, here one
https://www.c-sharpcorner.com/Upload...-vb-and-the-i/

[NicoVB.Net]

if you have a Datatable then pass the Datatable directly to .PDF
use iTextSharp.dll

there are many examples, here one
https://www.c-sharpcorner.com/Upload...-vb-and-the-i/

Hi, right now I create an Excel file then open it and save it as PDF. The datatable does not have all the data

[ChrisE]

another idea would be to send the Excel File to a Pdf-Printer
I think I have a Powershell script the searches in a Folder for Excel files and saves them as Pdf.


EDIT:
try this way in .NET

 Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim Path As String = "E:\vbExcel.xlsx" 'Datei mit Pfadangabe
        Dim Excel As ApplicationClass = New ApplicationClass
        Dim WorkBook As Workbook = Excel.Workbooks.Open(Path)
        Dim WorkSheets As Sheets = WorkBook.Sheets
        Dim WorkSheet As Worksheet = CType(WorkSheets(1), Microsoft.Office.Interop.Excel.Worksheet)


        WorkSheet.ExportAsFixedFormat(XlFixedFormatType.xlTypePDF, Filename:="D:\ExcelFolder\Test.pdf", Quality:=XlFixedFormatQuality.xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=True)

 

        Excel.Application.Quit()
    End Sub

[NicoVB.Net]

I mainly changed "ExportAsFixedFormat" with "Printout" and now it seems to be working fine. Thanks to all for your help!

THIS IS THE CODE i HAVE NOW:

Dim Path As String = "\\ken-resourcesan\fileshares\fieldshare\IT\nsantagata\ARStatements_CustomerInvoicesExcel\" & originalCustomerName & " " & customerNumber & " 050720.xlsx"
                Dim Excel As Excel.Application = New Excel.Application
                Dim WorkBook As Excel.Workbook = Excel.Workbooks.Open(Path)
                Dim WorkSheets As Excel.Sheets = WorkBook.Sheets
                Dim WorkSheet As Excel.Worksheet = CType(WorkSheets(1), Microsoft.Office.Interop.Excel.Worksheet)
                Excel.DisplayAlerts = False
                Excel.Visible = False
                'SAVE AS PDF
                Dim totalFileName As String = "\\ken-resourcesan\fileshares\fieldshare\IT\nsantagata\ARStatements_CustomerInvoicesPDF\" & originalCustomerName & " " & customerNumber & " " & todaysDate & ".pdf"
                Excel.ActiveSheet.Printout(Copies:=1, Preview:=False, ActivePrinter:="Microsoft Print to PDF", PrintToFile:=True, Collate:=True, PrToFileName:=totalFileName, IgnorePrintAreas:=False)