asked on

VB.Net convert Excel file to PDF: error at ExportAsFixedFormat

ERROR MESSAGE: System.Runtime.InteropServices.COMException: 'Exception from HRESULT: 0x800A03EC'

I narrowed down my problem but I still get an error:
I have a loop that opens up each excel file from a location and then saves it as PDF. It works and it creates a number of PDFs and then errors out at the ExportAsFixedFormat line. The number of PDFs files that get created varies, therefore I would not think there is a problem with the Excel file.

Also, I added DoEvents and Wait(hopefully in the right spots) but I still get the error.

I also noticed that after I get the error I move the debugger back a few lines and run again the ExportAsFixedFormat and it runs fine. It creates that PDF and continues to produce more.

Please help as I have been trying to find a solution for days.
This is the code I have now:

'OPEN XLSX DOC TO SAVE AS PDF
Dim xl As Object
xl = CreateObject("Excel.Application")
Dim xwb As Object
Dim workBooks = xl.Workbooks


System.Windows.Forms.Application.DoEvents()


xwb = workBooks.Open("\\ken-resourcesan\fileshares\fieldshare\IT\nsantagata\ARStatements_CustomerInvoicesExcel\" & originalCustomerName & " " & customerNumber & " 050720.xlsx")

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

           
NewDate = DateAndTime.Now.AddSeconds(10)
Do Until DateAndTime.Now > NewDate
     Application.DoEvents()
Loop


Dim newHour = Hour(Now())
Dim newMinute = Minute(Now())
Dim newSecond = Second(Now()) + 10
Dim waitTime = TimeSerial(newHour, newMinute, newSecond)
xl.Wait(waitTime)


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


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

Open in new window

Éric Moreau

to simplify your process even more, have you tried to save the PDF locally instead of network location?

Éric Moreau

also have you tried to create one instance of Excel Application and reuse the same one for every PDF that you need to create?

Ryan Chong

how big is the source file you trying to convert to PDF?

I just tried the codes and saved the file locally, and it worked.

And the codes below can be removed:

'NewDate = DateAndTime.Now.AddSeconds(10)
        'Do Until DateAndTime.Now > NewDate
        '    Application.DoEvents()
        'Loop

        'Dim newHour = Hour(Now())
        'Dim newMinute = Minute(Now())
        'Dim newSecond = Second(Now()) + 10
        'Dim waitTime = TimeSerial(newHour, newMinute, newSecond)
        'xl.Wait(waitTime)

Open in new window

I agreed with Eric to save file to local drive first, and then move it to your network drive. Try to treat these as 2 different processes.

Gary Benjamin

A few questions and ideas which might help. (I am not saying you are wrong by any of this. I just looked at your code and these were some ideas I had if I were trying to debug your issue.)

In this section

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

Open in new window

why are you casting as Object instead of Excel.Application and Excel.Workbook?

In this section, I am not sure why you are waiting

Dim newHour = Hour(Now())
Dim newMinute = Minute(Now())
Dim newSecond = Second(Now()) + 10
Dim waitTime = TimeSerial(newHour, newMinute, newSecond)
xl.Wait(waitTime)

Open in new window

but is it possible that this wait fails when Second(Now()) > = 50? Could this be why your program seems to fail randomly? Would this work no matter what time it currently is, (and be briefer)

xl.Wait(Now() + xl.TimeValue("0:00:10"))

Open in new window

ASKER CERTIFIED SOLUTION

jwebster77

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial