Macro launch - Button vs Manual launch , has different results.

The following Macro behaves differently when manually started from the menu,
or when assigned to a Control button.
It does not work correctly when started from the button. It works OK from
the menu ( Tools;Macro;Macro <macro name> Run)
The macro is supposed to create a adobe pdf file from a selected print
range. Using Excel 2000 , Adobe 6.0 Prof.
I have searched the trouble shooting section without sucess.
Can somebody help?

Sub Macro1()
Dim FILENAM
Sheets("Schedules").Select
ActiveSheet.PageSetup.PrintArea = "$A$230:$L$274"
Application.ActivePrinter = "Adobe PDF on Ne02:"
FILENAM = "C:\Test\mac1.pdf"
Application.SendKeys FILENAM, True
SendKeys "{Enter}"
SendKeys "Y"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF on Ne02:"
Application.Wait (Now + TimeValue("0:00:01"))
Sheets("Notes").Select
End Sub

What does "does not work correctly mean"? Do you get an error?

Guess: if you're using a button from the Control toolbox (not the
forms toolbar) try setting the property "Take focus on click" to
False.

Tim.


"Wayne" <[email protected]> wrote in message
news:[email protected]...
> The following Macro behaves differently when manually started from
> the menu,
> or when assigned to a Control button.
> It does not work correctly when started from the button. It works OK
> from
> the menu ( Tools;Macro;Macro <macro name> Run)
> The macro is supposed to create a adobe pdf file from a selected
> print
> range. Using Excel 2000 , Adobe 6.0 Prof.
> I have searched the trouble shooting section without sucess.
> Can somebody help?
>
> Sub Macro1()
> Dim FILENAM
> Sheets("Schedules").Select
> ActiveSheet.PageSetup.PrintArea = "$A$230:$L$274"
> Application.ActivePrinter = "Adobe PDF on Ne02:"
> FILENAM = "C:\Test\mac1.pdf"
> Application.SendKeys FILENAM, True
> SendKeys "{Enter}"
> SendKeys "Y"
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=
> _
> "Adobe PDF on Ne02:"
> Application.Wait (Now + TimeValue("0:00:01"))
> Sheets("Notes").Select
> End Sub

Tim,
Thanks for your reply.

The sendkeys that I have set up to give adobe
1. A file name
2. <enter> to accept & save
3. "Y" if the file already exists
The problem when starting from a button is that the file name does not come
up, it is somehow lost. In debug mode I have not worked out how to see the
sequence of send keys.
Hope this helps
I will try your suggestion & let you know.

"Tim Williams" wrote:

> What does "does not work correctly mean"? Do you get an error?
>
> Guess: if you're using a button from the Control toolbox (not the
> forms toolbar) try setting the property "Take focus on click" to
> False.
>
> Tim.
>
>
> "Wayne" <[email protected]> wrote in message
> news:[email protected]...
> > The following Macro behaves differently when manually started from
> > the menu,
> > or when assigned to a Control button.
> > It does not work correctly when started from the button. It works OK
> > from
> > the menu ( Tools;Macro;Macro <macro name> Run)
> > The macro is supposed to create a adobe pdf file from a selected
> > print
> > range. Using Excel 2000 , Adobe 6.0 Prof.
> > I have searched the trouble shooting section without sucess.
> > Can somebody help?
> >
> > Sub Macro1()
> > Dim FILENAM
> > Sheets("Schedules").Select
> > ActiveSheet.PageSetup.PrintArea = "$A$230:$L$274"
> > Application.ActivePrinter = "Adobe PDF on Ne02:"
> > FILENAM = "C:\Test\mac1.pdf"
> > Application.SendKeys FILENAM, True
> > SendKeys "{Enter}"
> > SendKeys "Y"
> > ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=
> > _
> > "Adobe PDF on Ne02:"
> > Application.Wait (Now + TimeValue("0:00:01"))
> > Sheets("Notes").Select
> > End Sub
>
>
>

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


"Tim Williams" wrote:

> What does "does not work correctly mean"? Do you get an error?
>
> Guess: if you're using a button from the Control toolbox (not the
> forms toolbar) try setting the property "Take focus on click" to
> False.
>
> Tim.
>
>
> "Wayne" <[email protected]> wrote in message
> news:[email protected]...
> > The following Macro behaves differently when manually started from
> > the menu,
> > or when assigned to a Control button.
> > It does not work correctly when started from the button. It works OK
> > from
> > the menu ( Tools;Macro;Macro <macro name> Run)
> > The macro is supposed to create a adobe pdf file from a selected
> > print
> > range. Using Excel 2000 , Adobe 6.0 Prof.
> > I have searched the trouble shooting section without sucess.
> > Can somebody help?
> >
> > Sub Macro1()
> > Dim FILENAM
> > Sheets("Schedules").Select
> > ActiveSheet.PageSetup.PrintArea = "$A$230:$L$274"
> > Application.ActivePrinter = "Adobe PDF on Ne02:"
> > FILENAM = "C:\Test\mac1.pdf"
> > Application.SendKeys FILENAM, True
> > SendKeys "{Enter}"
> > SendKeys "Y"
> > ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=
> > _
> > "Adobe PDF on Ne02:"
> > Application.Wait (Now + TimeValue("0:00:01"))
> > Sheets("Notes").Select
> > End Sub
>
>
>

Wayne wrote:
> 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

--
Rob

http://www.asta51.dsl.pipex.com/webcam/: