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

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...):

Code:

---

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:

Code:

---

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:

Code:

---

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.

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.

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

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.

replied in the right area

Comment deleted as it was duplicate.

Quote:

---

Originally Posted by TysonLPrice

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:

Code:

---

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:

Code:

---

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

---

Duplicate

Quote:

---

Originally Posted by NicoVB.Net

Comment deleted as it was duplicate.

---

Quote:

---

Originally Posted by NicoVB.Net

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

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/

Quote:

---

Originally Posted by 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/

---

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

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

Code:

---

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

---

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:

Code:

---

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)

---