Control Printer Setting Dialog box in Excel VBA-VBForums
Results 1 to 13 of 13

Thread: Control Printer Setting Dialog box in Excel VBA

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Omaha, NE
    Posts
    259

    Control Printer Setting Dialog box in Excel VBA

    All,
    I'm trying to automate a process in Excel VBA to send out Excel reports to external clients in PDF format. (I'm using Excel 2003 & Acrobat 9 Standard.) When I record the macro to print to PDF, I get the following.
    Code:
    Sub Macro3()
        Application.ActivePrinter = "Adobe PDF on Ne00:"
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
            "Adobe PDF on Ne00:", Collate:=True
    End Sub
    This does indeed create the PDF files, but it opens Acrobat and puts the new file there with a default name (same as the Excel with a .pdf extension). I've found that if I go to File/Print and select the Printer Properties button for the Adobe PDF printer, I can manually set the path & I can deselect the "View Adobe PDF Results" checkbox. Once I've done that, I can cycle through all my Excel files to create them without opening Acrobat and having the user do anything. These two things would solve my problem.

    Is there any VBA code that gets me to the Printer Properties?
    Thanks in advance!!!
    Nate

  2. #2
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Control Printer Setting Dialog box in Excel VBA

    Do you have Adobe Acrobat or Adobe Reader?

    With Adobe Acrobat it is possibe... I am not sure about Adobe reader though...

    Sid
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Omaha, NE
    Posts
    259

    Re: Control Printer Setting Dialog box in Excel VBA

    As I mentioned in my original post, I have Adobe Acrobat 9 Standard not just Reader. If you know it can be done here, do have a code snippet that can access the Properties dialog & set those two options?

    Thanks.
    Nate

  4. #4
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Control Printer Setting Dialog box in Excel VBA

    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Omaha, NE
    Posts
    259

    Re: Control Printer Setting Dialog box in Excel VBA

    It looks interesting. I put it into a module, but it appears that it uses a couple of functions which are not defined (at least not that I can see).
    Those are:

    RegOpenKeyA
    RegSetValueEx
    RegCloseKey

    If these are API calls, I'm in trouble. I've NEVER understood API's. But, if the code is already written to use them and you have their definitions (or know where I can get them), I should be able to at least use them...even if I don't know how they work.

    Thanks for your help!!
    Nate

  6. #6
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,928

    Re: Control Printer Setting Dialog box in Excel VBA

    It's very easy.. Check the link in my signature on "Working with Windows Registry"
    The poster formerly known as koolsid
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - Till Date IMP Links : Acceptable Use Policy, FAQ

    MyGear:
    Sony VGN-FZ27G with a triple boot between (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008) and (Win7+Office 2010+VS2010) || Sony VPCCB-45FN with a Win7+Office 2010+VS2010. VM: (XP+Office 2003+VB6), (VISTA+Office 2007+VS2008), (Win8+Office 2010+VS2012) || Mac Book Pro (10.6.8) with Office 2011

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Omaha, NE
    Posts
    259

    Re: Control Printer Setting Dialog box in Excel VBA

    Koolsid,
    Thanks for your help. I've tried using your code, modified for my application of course. Initially, it wouldn't run because I didn't have the Declare statements for the functions. I had to do a search for those (since two of them weren't included in your link) & I've added them to my project. My current code is below. It appears to run (at least I don't get any error messages ), however, the pdf file never shows up in my folder (which I want to be the same folder as the file that I'm opening in the first statement).

    I thought that's what the last two parameters did in the RegSetValueEx function. Could you review the code & let me know what I'm either NOT DOING or DOING WRONG.

    Thanks,
    Nate Brei
    Code:
    Declare Function RegOpenKeyA Lib "advapi32.dll" ( _
        ByVal Key As Long, _
        ByVal SubKey As String, _
        NewKey As Long) As Long
    Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" ( _
        ByVal hKey As Long, _
        ByVal lpValueName As String, _
        ByVal Reserved As Long, _
        ByVal dwType As Long, _
        lpData As Any, _
        ByVal cbData As Long) As Long
    Declare Function RegCloseKey Lib "advapi32.dll" ( _
        ByVal hKey As Long) As Long
    
    Sub TestPrintPDF()
        Dim strDefaultPrinter As String
        Dim strOutFile As String
        Dim lngRegResult As Long
        Dim lngResult As Long
        Dim dhcHKeyCurrentUser As Long
        Dim PDFPath As String
        
        Workbooks.Open ("\\master\fnshares\bcbcm\Client Management\Client Services\New Account Fees\09 September 2010\3Q10 Rebate Ltrs\Infi\MacroTest\A02.xls")
        Select Case ActiveWorkbook.Sheets.Count
            Case 1
                Sheets(1).Select
            Case 2
                Sheets(Array(Sheets(1).Name, Sheets(2).Name)).Select
            Case 3
                Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name)).Select
            Case 4
                Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name, Sheets(4).Name)).Select
            Case 5
                Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name, Sheets(4).Name, Sheets(5).Name)).Select
            Case 6
                Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name, Sheets(4).Name, Sheets(5).Name, Sheets(6).Name)).Select
        End Select
        dhcHKeyCurrentUser = &H80000001
        strDefaultPrinter = Application.ActivePrinter
        PDFPath = ActiveWorkbook.Path & Application.PathSeparator 'The directory in which you want to save the file
        strOutFile = PDFPath & Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4) & ".pdf" 'Change the pdf file name if required. This should have the fully qualified path
        
        lngRegResult = RegOpenKeyA(dhcHKeyCurrentUser, "Software\Adobe\Acrobat Distiller\PrinterJobControl", lngResult)
        lngRegResult = RegSetValueEx(lngResult, Application.Path & "\Excel.exe", 0&, dhcRegSz, ByVal strOutFile, Len(strOutFile))
        lngRegResult = RegCloseKey(lngResult)
        ThisWorkbook.ActiveSheet.PrintOut copies:=1, ActivePrinter:="Adobe PDF"
        Application.ActivePrinter = strDefaultPrinter
        ActiveWorkbook.Close False
    End Sub

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,998

    Re: Control Printer Setting Dialog box in Excel VBA

    have you checked in the registry, using regedit, to check if the correct values are being applied to the registry keys?

    looks like it should print somewhere, find the default folder for adobe and see if there is a new file there

    note great care must be taken, when working with registry either by code or in regedit, a back up of the registry may be a good idea
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Omaha, NE
    Posts
    259

    Re: Control Printer Setting Dialog box in Excel VBA

    Thanks for the advice on the Registry backup. I did that yesterday before I played with the code.

    Also, I've walked through the code and the filename in strOutFile is "A02.pdf". I've done a Windows Explorer search on that filename on my entire C: drive and the entire two network drives where it could possibly be (one is the drive where my Excel macro file resides and the other is the drive containing the source file I open). That file doesn't exist anywhere. Any other ideas? Does my code work for you or is there something I'm doing or not doing?

    Thanks.
    Nate
    Last edited by NateBrei; Nov 30th, 2010 at 09:04 AM.

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Omaha, NE
    Posts
    259

    Re: Control Printer Setting Dialog box in Excel VBA

    Attached is a picture of my Registry entries for Adobe Distiller. I don't know what they all mean or what should be here, but I do NOT see "A02.pdf" anywhere.

    Thanks in advance for any help!!
    Nate
    Attached Files Attached Files

  11. #11
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,998

    Re: Control Printer Setting Dialog box in Excel VBA

    you do not have a constant declared for dhcRegSz, this will cause it to default to 0, which appears to save binary value, rather than the desired string value

    have a look in the paths listed in the registry entries to see if the excel files have printed to any of those (overwritten the original)
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2002
    Location
    Omaha, NE
    Posts
    259

    Re: Control Printer Setting Dialog box in Excel VBA

    I did some research and changed my code to set the dhcRegSz variable to a value of 1. It now writes my path to the registry (see attached picture). However, no .pdf file shows up. AARRGG!!

    More ideas on how to get the file to ?somewhere? .....

    Nate
    Attached Files Attached Files

  13. #13
    PowerPoster
    Join Date
    Dec 2004
    Posts
    19,998

    Re: Control Printer Setting Dialog box in Excel VBA

    research more on the required settings for adobe, i do not have it so can not give any soultion
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.