Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
I am having an issue where it takes almost a full minute for this code to finish creating a PDF. A publishing window comes up and it gets stuck after about 90% being complete. Then it waits there for what seems like a minute before it finishes. Not sure how to view in the code where it is stopping, but the code is pretty straight forward and I have used it on many other documents before. Here is what it looks like:
Sub OILPDF() Dim lastOILrow As Integer Dim message As Integer Dim Filename As Variant Dim lastPrintrow As Integer Dim viewdate As String viewdate = Format(Now(), "MM-DD-YYYY") Sheet11.Activate lastPrintrow = Sheet11.Cells(Sheet11.Rows.Count, 1).End(xlUp).Row Sheet11.Range(Cells(9, 1), Cells(lastPrintrow, 17)).ClearContents Sheet11.Range(Cells(9, 1), Cells(lastPrintrow, 17)).ClearFormats Sheet3.Activate lastOILrow = Sheet3.Cells(Sheet3.Rows.Count, 2).End(xlUp).Row Sheet3.Range(Cells(4, 1), Cells(lastOILrow, 17)).Copy Sheet11.Activate Sheet11.Cells(4, 1).PasteSpecial Filename = ActiveWorkbook.Path & Application.PathSeparator & Sheet11.Range("b6").Value & " Open Issues " & viewdate & ".pdf" Sheet11.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Filename, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False Sheet3.Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
There may be something of interest in a 5/18/2014 post titled "Excel to
PDF or XPS boilerplate routines". Also, check out Charlotte E's link for another excellent solution... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
This codes does the exact same thing. In the previous code, I am not sure which variable was named as a keyword.
Sub OILPDF() Application.ScreenUpdating = False 'Unprotect sheet so that work can be done Sheet3.Unprotect Password:="Tr2010" Sheet11.Unprotect Password:="Tr2010" Dim lrow As Integer Dim Fname As String Dim todaysDate As String todaysDate = Format(Now(), "MM-DD-YYYY") With Sheet11 lrow = .Cells(.Rows.Count, 1).End(xlUp).Row .Range(.Cells(9, 1), .Cells(lrow, 17)).ClearContents .Range(.Cells(9, 1), .Cells(lrow, 17)).ClearFormats End With With Sheet3 lrow = .Cells(Sheet3.Rows.Count, 2).End(xlUp).Row .Range(.Cells(4, 1), .Cells(lrow, 17)).Copy _ Sheet11.Cells(4, 1) End With Fname = ActiveWorkbook.Path & "\" & Sheet1.Range("C6").Value & " Open Issues " & todaysDate & ".pdf" Sheet11.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False 'Protect sheet so that no work can be done Sheet3.Protect Password:="Tr2010" Sheet11.Protect Password:="Tr2010" Application.ScreenUpdating = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
So I ended up getting it to work better. What I was initially doing was copying the information I wanted to print to another document and then using the Export to PDF function to print this document to PDF. I was doing this initially just so I didn't waste paper as I had 2000 lines called out in the document. Now what I am doing is just hiding everything past the last row and then printing and then unhiding. Its much quicker.
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
Hi Chris,
Am Mon, 15 Feb 2016 12:23:56 -0800 (PST) schrieb Chris Scott: So I ended up getting it to work better. What I was initially doing was copying the information I wanted to print to another document and then using the Export to PDF function to print this document to PDF. I was doing this initially just so I didn't waste paper as I had 2000 lines called out in the document. Now what I am doing is just hiding everything past the last row and then printing and then unhiding. Its much quicker. the code I posted runs in less than 1 second on my machine. Repair your Excel. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
What could be wrong with it and how would I go about repairing it?
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
Hi Chris,
Am Tue, 16 Feb 2016 05:41:13 -0800 (PST) schrieb Chris Scott: What could be wrong with it and how would I go about repairing it? in 2007 you have in the start menu Office Tools = Office Diagnosis. In the newer version go to Control Panel = Deinstall Programms and choose Office = Change and choose Repair. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
"Chris Scott" wrote in message What could be wrong with it and how would I go about repairing it? I've only had a quick glance at this but I notice your un/protect stuff. Be aware since 2013 that takes a long time. If that's what you have try same without any protection and go from there. Regards, Peter T |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
"Chris Scott" wrote in message
What could be wrong with it and how would I go about repairing it? I've only had a quick glance at this but I notice your un/protect stuff. Be aware since 2013 that takes a long time. If that's what you have try same without any protection and go from there. Regards, Peter T I recommend setting the UserInterfaceOnly protection option at each startup so code can make unrestricted changes to locked cells. Startup reset is necessary because this option doesn't persist between sessions. (Not to mention the PITA that toggling protection during runtime is!) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
GS, you are saying to use the UserInterfaceOnly protection option in the Workbook_Open sub and not having to deal with it inside the vb code ever again? This is brilliant. I had actually just put the protect code(without UserInterfaceOnly) into that sub yesterday so I didn't have to protect all my sheets before I closed once I had made changes.You learn something new everyday.
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Issues with Slow Print to PDF Program...
GS, you are saying to use the UserInterfaceOnly protection option in
the Workbook_Open sub and not having to deal with it inside the vb code ever again? This is brilliant. I had actually just put the protect code(without UserInterfaceOnly) into that sub yesterday so I didn't have to protect all my sheets before I closed once I had made changes.You learn something new everyday. No point to apply the UserInterfaceOnly option at shutdown (closing) because it doesn't persist. That's why I specified using it at startup. The Workbook_Open event is a good place, but I prefer using Sub Auto_Open in a standard module for reliability. (Same goes for Sub Auto_Close in place of using event code)... In module named m_OpenClose: Option Explicit 'define all global scope variables/constants here Sub Auto_Open() 'put startup code here End Sub Sub Auto_Close() 'put shutdown code here End Sub HTH -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Has Excel 2010 fixed slow down issues? | Excel Discussion (Misc queries) | |||
Entering data slow program down | New Users to Excel | |||
Slow Program | Excel Programming | |||
New computer with Vista caused Excel function slow down Issues | Excel Discussion (Misc queries) | |||
slow program in a loop | Excel Programming |