Desperate – Excel to PDF major problem

[Manq5230]

Hello everyone, I try desperately to figure things out on my own, but now my job is riding on getting a solution, I have searched the web and have found no solution that works.
Here is the problem, I have a spreadsheet that is approx 5000 or so rows, I have a ton of formulas, functions, and macros involved, up until a month or so ago, everything worked great, then out of the blue, when printing to PDF the sheet started to crawl to get the 151 pages that it prints.

Before this problem started it took less than a minute to finish the printing to PDF, now it shows 1 page every 35 seconds, I used a stop watch to be accurate, this is unacceptable, based on the 35 seconds per page it would take 1 hour 45 minutes approx, to finish the 151 pages. Because this happened once before a year or so ago, I had posted a similar question to Mr. Excel was given a possible solution of using a VBA code that would send the info to PDF before printing, I tried the Code and it did work, but did no faster of a time.

I’m running Windows 7, Office 2007, and Adobe Acrobat 10.1 or more, and I have full version of Adobe CS4 Master Collection.

After spending hours online, both me and my Wife, we could not find any solution.
I like using DoPDF7 which has been a great program, I also have CutePdf, and then Acrobat, I installed the OpenOffice.org program, and a program trial called Nitro, I have yet to find a program that fixes this issue, I have read every thread on Adobe’s site, and

Microsoft’s, none of them describes this issue, of course I have restarted a ton of times, and still no help.

Other programs such as word, Photoshop, and others are all processing documents fine, even multiple documents.

I will be eternally grateful for any ideas on how to fix this issue, when it happened before my Wife says that I found the solution, and now is asking what it was, but for the life of me I can’t remember what I did or changed. This time if I get a solution, I will write it down.

Thanks

 

[Michael M]

This was the last time you asked this question

http://www.mrexcel.com/forum/showthread.php?t=488386

What it is basically doing is saving ALL files to PDF first !
Then you an print them out from windows explorer.

 

[Manq5230]

Thanks Michael, for the response, but I already looked through all my contact with Mr.Excel, and as I stated in my post yesterday, the response I got was the following code.

Sub DoALL()
'Assistance gratefully received from Dom Hill, JBeaucaire, James Vaughan and Snowblizz, APRIL 2010
'For this code to work the Adobe Distiller VBA library must be enabled
'Select Tools...References in the VBA editor and check it from the list
'Printer Preferences for "Adobe PDF" printer need to have "Rely on system Fonts" unchecked

Dim tempPDFFileName, tempPSFileName, tempPDFRawFileName As String, mypdfDist As New PdfDistiller, _
i As Integer, Mail_Object, Email_Subject, nameList As String, o As Variant
tempPDFRawFileName = "G:\Temp\" & ActiveWorkbook.Name ' Change File Path to suit
tempPSFileName = tempPDFRawFileName & ".ps"
tempPDFFileName = tempPDFRawFileName & ".pdf"

'Prints workbook as a pdf, an array of sheets can be printed if required

ActiveWorkbook.PrintOut Copies:=1, preview:=False, ActivePrinter:="Adobe PDF", _
printtofile:=True, Collate:=True, prtofilename:=tempPSFileName
mypdfDist.FileToPDF tempPSFileName, tempPDFFileName, ""
Kill tempPSFileName
Set mypdfDist = Nothing
End Sub


I followed the instructions in this code I had to change the "ActiveWorkbook" references to "ActiveSheet" but it did do what it said, just that it took just as long per sheet to create the PDF.

Here is my exact Procedure

1. I have my Workbook open
2. I do ctrl+P to bring up print dialog box
3. I select DoPDF7, or it brings up its own dialog box to browse where to place the file and name the file.
4. I find the location where I keep these Pdf's and name it, then hit Ok.
5. When I hit ok, the standard excel printing window comes on the screen and says "Now printing page 1 of 151 'Omnia Price List V.33 102311.xlsm' on the doPDF v on 7" then of course it has the Cancel Button.

In the past where it says the "1 of 151" part in less than a minute or so it would start rattling off the count and be to "151" pages and then my PDF would show in Acrobat Reader, now just going to page "2 of 151" takes 35 seconds, and then 35 seconds the rest of the time, until reaching page "151".

This is what I need help figuring out, I have tried running other excel files that have multiple page to DoPDF7 and they scream through the process and are done.

This has something to do with my workbook, the formulas are mostly "Ifs" there are some "Mods" but nothing that hasn't been in the sheet forever. I have deleted everything that I can that is non-essential, and then performed a "save as".

I'm at a loss.

Thanks for anything you can think of.

 

[Manq5230]

Hello everyone, I believe I have solved the mystery, I decided to do a save as test of my sheet, and started to delete all my greyscale line drawings, to see if that had something to do with, although this sheet has had line drawings in it since the beginning and each page has a line drawing in it, so now I will be going back in time and see if it is one specific line drawing that is causing the issue. My guess is, that there is a line drawing file that I inserted that has something Excel doesn't like.

Thanks for all the help.