jwebster77
asked on
VB.Net convert Excel file to PDF: error at ExportAsFixedFormat
ERROR MESSAGE: System.Runtime.InteropServ
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:
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()
Last Comment
Éric Moreau
to simplify your process even more, have you tried to save the PDF locally instead of network location?
also have you tried to create one instance of Excel Application and reuse the same one for every PDF that you need to create?
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:
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)
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
In this section, I am not sure why you are waiting
In this section
Dim xl As Object
xl = CreateObject("Excel.Application")
Dim xwb As Object
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)
xl.Wait(Now() + xl.TimeValue("0:00:10"))
ASKER CERTIFIED SOLUTION
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.
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
TRUSTED BY
