Avatar of jwebster77
jwebster77

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

Visual Basic.NETMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
jwebster77
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

to simplify your process even more, have you tried to save the PDF locally instead of network location?
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

also have you tried to create one instance of Excel Application and reuse the same one for every PDF that you need to create?
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

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.
Avatar of Gary Benjamin
Gary Benjamin
Flag of Canada image

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
Avatar of jwebster77
jwebster77

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo