Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Issues with Slow Print to PDF Program...

Hi Christopher,

Am Mon, 15 Feb 2016 06:37:32 -0800 (PST) schrieb
:

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:


don't name a variable as a keyword and use Activate only if it is
necessary.
Try this code

Sub OILPDF()
Dim LRow As Long
Dim FName As Variant
Dim viewdate As String

viewdate = Format(Now(), "MM-DD-YYYY")

With Sheets("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 Sheets("Sheet3")
LRow = .Cells(.Rows.Count, 2).End(xlUp).Row
.Range(.Cells(4, 1), .Cells(LRow, 17)).Copy _
Sheets("Sheet11").Range("A1")
End With

FName = ActiveWorkbook.Path & "\" & Sheets("Sheet11").Range("B6") & _
" Open Issues " & viewdate & ".pdf"

Sheets("Sheet11").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=True, OpenAfterPublish:=False

Application.Goto Sheets("Sheet3").Range("A1")

End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default Issues with Slow Print to PDF Program...

What could be wrong with it and how would I go about repairing it?
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 162
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Has Excel 2010 fixed slow down issues? Kevryl Excel Discussion (Misc queries) 3 May 5th 23 11:46 AM
Entering data slow program down JMM New Users to Excel 2 April 11th 09 04:44 PM
Slow Program AD[_2_] Excel Programming 4 March 3rd 08 06:57 PM
New computer with Vista caused Excel function slow down Issues Matt Parker Excel Discussion (Misc queries) 0 January 5th 08 02:33 AM
slow program in a loop chris Excel Programming 4 October 2nd 03 07:58 AM


All times are GMT +1. The time now is 02:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"