Wayne said:
Tim,
I tried your suggestion. It almost worked.
On the file name prompt I got "YC:\Test\mac1.pdf"
So it seems the keys are sent in the wrong order ?
I took out the Y from the macro & it now works. But how do I automate
this ?
Wayne
Another approach that I've found to work very well is to write the filename
to acrobat's section of the registry and to acrobat's ini files. It seems to
need this belt-and-braces approach because of different versions. The print
driver will then pick up the filename and not bother you with dialog
Option Explicit
Private Declare Function GetSystemDirectory Lib "kernel32" Alias _
"GetSystemDirectoryA" _
(ByVal lpBuffer As String, ByVal nSize As Long) As Long
Private Declare Function WritePrivateProfileString Lib "kernel32" Alias _
"WritePrivateProfileStringA" (ByVal lpApplicationName As String, _
ByVal lpKeyName As Any, ByVal lpString As Any, _
ByVal lpFileName As String) As Long
Private Declare Function GetWindowsDirectory Lib "kernel32" Alias _
"GetWindowsDirectoryA" (ByVal lpBuffer As String, _
ByVal nSize As Long) As Long
Sub test()
WriteToINI Application.WorksheetFunction.Substitute(ThisWorkbook.Name, _
".xls", ".pdf")
End Sub
Sub WriteToINI(strSaveAsFileName As String)
'===================================================================
'= Procedure: sWriteToINI =
'= Type: Private Subprocedure =
'= =
'= Purpose: Writes a filename to both the registry and the =
'= Acrobat.ini file. This is picked up by the =
'= PDFWriter print driver to be used as a filename =
'= for the printed document. =
'= REQUIRES A REFERENCE SET TO THE WINDOWS SCRIPTING =
'= HOST OBJECT MODEL LIBRARY. =
'= Parameters: strSaveAsFileName - String - The FULL name of the =
'= file to print to. =
'= Returns: Nothing =
'= =
'= Version: Date: Developer: Action: =
'=---------|---------|---------------|-----------------------------=
'= 1.0.0 |22-Feb-00| Rob Bruce | Created =
'===================================================================
Const REG_ROOT As String = _
"HKEY_CURRENT_USER\Software\Adobe\Acrobat PDFWriter\"
Dim strBuffer As String * 255
Dim strSysDir As String
Dim lngLen As Long
Dim lngRet As Long
Dim objWShell As IWshRuntimeLibrary.WshShell
' New Windows scripting host object.
Set objWShell = New IWshRuntimeLibrary.WshShell
Dim strIniFileName As String
' Use the WSH to write to the registry...
objWShell.RegWrite REG_ROOT & "PDFFileName", strSaveAsFileName
' Write to the ini file...
' Get the location of the system directory...
lngLen = Len(strBuffer)
lngRet = GetSystemDirectory(strBuffer, lngLen)
strSysDir = Left(strBuffer, lngRet) & "\"
' Establish the full name of the ini file...
strIniFileName = strSysDir & "pdfwritr.ini"
' Write the value to the file...
lngRet = WritePrivateProfileString("Acrobat PDFWriter", _
"PDFFileName", """" & strSaveAsFileName & """", _
strIniFileName)
' Get the location of the windows directory...
lngLen = Len(strBuffer)
lngRet = GetWindowsDirectory(strBuffer, lngLen)
strSysDir = Left(strBuffer, lngRet) & "\"
' Establish the full name of the ini file...
strIniFileName = strSysDir & "__pdf.ini"
' Write the value tot he file...
lngRet = WritePrivateProfileString("Acrobat PDFWriter", _
"PDFFileName", """" & strSaveAsFileName & """", _
strIniFileName)
End Sub