Printing from Excel to PDF - multiple PDF jobs?

Hi, using a list of data I am creating singular spreadsheets for each row, and then once each spreadsheet has been created, I am wanting to print the data to PDF (without displaying the PDF file on screen), closing the individual excel file, and then looping to do the exact same process for all other rows on screen.

Unfortunately though for some files pdfFactory Pro opens on screen, and as soon as this happens, once the macro attempts to create the next PDF file, since the previous PDF is still open on screen, the jobs are just added on top of each other and the PDF files aren't created - ANY IDEAS?? This is driving me nuts!!

The PDF code I am using is as follows:

Public Sub Generate_PDF_File()
Dim PDF_FileName As String
Dim lpNewVal, i As Long
Dim PrintOut As Boolean
Dim ch As Characters
Dim UsedPrinter As String

     If RegOpenKeyEx(HKEY_CURRENT_USER, "Software\FinePrint Software\pdfFactory2\FinePrinters\,,michprn01,pdfFactory\PrinterDriverData", 0&, KEY_QUERY_VALUE + KEY_SET_VALUE, hKey) = ERROR_SUCCESS And _
       RegOpenKeyEx(HKEY_CURRENT_USER, "Software\FinePrint Software\pdfFactory2", 0&, KEY_QUERY_VALUE + KEY_SET_VALUE, hKey2) = ERROR_SUCCESS Then
   
        i = RegQueryValueEx(hKey, "ShowDlg", ByVal 0&, lpType, lpData, lpLen)        'dummy to initialize
         If RegQueryValueEx(hKey, "ShowDlg", ByVal 0&, lpType, lpData, lpLen) = ERROR_SUCCESS Then
            lpNewVal = 4                               '1 view UI, 4 no UI, no e-mail, no view pdf
           
                If IntermediaryType = "" Then PDF_FileName = "J:\Trail\IRIC\Investor IRIC Confirmations\IRIC Confirmation " & TheYear & "." & TheMonth & "." & TheDay & " " & InvestorID & " " & Gaining_Int_PCID & ".pdf"
                If IntermediaryType = "Losing" Then PDF_FileName = "J:\Trail\IRIC\Losing Intermediary IRIC Confirmations\IRIC Confirmation " & TheYear & "." & TheMonth & "." & TheDay & " " & IntermediaryPCID & ".pdf"
                If IntermediaryType = "Gaining" Then PDF_FileName = "J:\Trail\IRIC\Gaining Intermediary IRIC Confirmations\IRIC Confirmation " & TheYear & "." & TheMonth & "." & TheDay & " " & IntermediaryPCID & ".pdf"
           
            'change settings
            RegSetValueEx_DWord hKey, "ShowDlg", 0&, REG_DWORD, lpNewVal, 4
            RegSetValueEx_String hKey2, "OutputFile", 0&, REG_SZ, PDF_FileName, Len(PDF_FileName)
           
                PrintOut = Worksheets("Sheet1").PrintOut(copies:=1, preview:=False, ActivePrinter:="\\michprn01\pdfFactory", PrintToFile:=False, Collate:=True, PrToFileName:=PDF_FileName)    'Print worksheet to PDF
               
                'MySheet.Range("MyRange").PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=False, Collate:=True, PrToFileName:=PDFFileName
               
                If IntermediaryType = "" Then Application.Wait (Now + TimeValue("0:00:03"))   'Apply Waiting Time
                If IntermediaryType = "Losing" Then Application.Wait (Now + TimeValue("0:00:06"))   'Apply Waiting Time
                If IntermediaryType = "Gaining" Then Application.Wait (Now + TimeValue("0:00:06"))   'Apply Waiting Time
               
            lpNewVal = 1
            RegSetValueEx_DWord hKey, "ShowDlg", 0&, REG_DWORD, lpNewVal, 4

        Else
            ch = MsgBox("No PDF Printer found !", vbOKCancel, "Error")
        End If
        RegCloseKey hKey
        Else
            ch = MsgBox("No PDF Printer found !", vbOKCancel, "Error")
        End If

End Sub
Chris_KirkcaldyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Richard DanekeTrainerCommented:
Please specifiy your Excel version and PDF features vary greatly.
Chris_KirkcaldyAuthor Commented:
Thanks for the helpful comment!
Richard DanekeTrainerCommented:
Sorry, there was a typo.  It should have read:
Please specifiy your Excel version as PDF features vary greatly.
In 2003 versions and earlier, many people used a third party app for PDF creation.  Some installed the standard Adobe for PDF creation.  
In 2007 (and 2010), a Microsoft add-in makes PDF creation easy with an OutputTo option.   I was not being glib.  I was trying to better understand your technical environment.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Chris_KirkcaldyAuthor Commented:
Hi DoDadD,
I'm using Excel 2003, and PDF Factory Pro. Does this help?
Thanks,
Chris
Richard DanekeTrainerCommented:
Looking at the code, why is PrintToFile FALSE? If you make this TRUE will the dialog box not open?
PrintOut = Worksheets("Sheet1").PrintOut(copies:=1, preview:=False, ActivePrinter:="\\michprn01\pdfFactory", PrintToFile:=False, Collate:=True, PrToFileName:=PDF_FileName) 'Print worksheet to PDF

'MySheet.Range("MyRange").PrintOut copies:=1, preview:=False, ActivePrinter:="Adobe PDF", PrintToFile:=False, Collate:=True, PrToFileName:=PDFFileName
Richard DanekeTrainerCommented:
Also, PDFFactory has a dialog box option:
Open the pdfFactory Printing Preferences (right-click the pdfFactory icon)and turn off the dialog box display.
Chris_KirkcaldyAuthor Commented:
Sorry DoDahD, someone else had responded to my question with "I don't know", hence my "Thanks for the helpful comment!" message - this wasn't directed at you!!
Yes, I had tried both PrintToFile = False and True, but on both I received the same message :-(  I'll give the printing preferences a go and get back to you shortly...
Chris_KirkcaldyAuthor Commented:
Hi DoDahD,
I've tried playing around with the pdfFactory Pro dialog settings - when ticking the "Do not show dialog" box this enables 4 options. Selecting the "save the file in the AutoSave folder" doesn't work (gives me the same pop-up PDF window), and I'm not sure which of the other options to select - any ideas?
I also have the option to have Nitro PDF to be installed onto my computer instead of PDF Factory Pro - do you know if this would make any difference.
Many thanks
Richard DanekeTrainerCommented:
No, I am maxed out on ideas.   Others are welcome to opine.
Chris_KirkcaldyAuthor Commented:
No worries, thanks anyway
Chris_KirkcaldyAuthor Commented:
DoDahD you mentioned excel 2007 having an extra OutputTo option - if I were to utilise Excel 2007 could this problem be overcome? Happy trying anything...
Richard DanekeTrainerCommented:
Yes, PDF file creation is part of VBA in 2007 and 2010. (2010 Beta is still available for free download although the release is just weeks away) Here is reference on command from Help file:
Excel Developer Reference Workbook.ExportAsFixedFormat Method
The ExportAsFixedFormat method is used to publish a workbook to either the PDF or XPS format.   Version Information   Version Added:  Excel 2007
Syntax
expression.ExportAsFixedFormat(Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr)

expression   A variable that represents a Workbook, Sheet, Chart, or Range object.
Parameters Name Required/Optional Data Type Description
Type Required XlFixedFormatType Can be either xlTypePDF or xlTypeXPS.
 Filename Optional Variant A string that indicates the name of the file to be saved. You can include a full path or Excel 2007 saves the file in the current folder.
Quality Optional Variant Can be set to either xlQualityStandard or xlQualityMinimum
. IncludeDocProperties Optional Variant Set to True to indicate that document properties should be included or set to False to indicate that they are omitted.
IgnorePrintAreas Optional Variant If set to True, ignores any print areas set when publishing. If set to False, will use the print areas set when publishing.
From Optional Variant The number of the page at which to start publishing. If this argument is omitted, publishing starts at the beginning.
To Optional Variant The number of the last page to publish. If this argument is omitted, publishing ends with the last page
OpenAfterPublish Optional Variant If set to True displays file in viewer after it is published. If set to False the file is published but not displayed.
 FixedFormatExtClassPtr Optional Variant Pointer to the FixedFormatExt class.
 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.