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
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?
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.
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
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
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 10:04 AM.
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.
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
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? .....
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