asked on
'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()
'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)
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.
Dim xl As Object
xl = CreateObject("Excel.Application")
Dim xwb As Object
why are you casting as Object instead of Excel.Application and Excel.Workbook?Dim newHour = Hour(Now())
Dim newMinute = Minute(Now())
Dim newSecond = Second(Now()) + 10
Dim waitTime = TimeSerial(newHour, newMinute, newSecond)
xl.Wait(waitTime)
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"))
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
TRUSTED BY