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

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

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

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.

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

What could be wrong with it and how would I go about repairing it?

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