Prev: localeSpecificCellName
Next: Launching PowerPoint from an Excel application
From: Frustrated on 15 Sep 2008 13:12
Hi,

I'm trying to write a macro that will print a number of worksheets to
different PDF files. I've used the macro recorder to generate sample code,
and it uses ExecuteExcel4Macro to execute the print function.

My problem is that when the code runs, the print dialog box waits for me to
give it a file name (I don't want the default) and to click 'Save'. This is
what I want to automate so that there is no user interaction. This is the
code that the macro recorder made for me:

ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne01:"",,TRUE,,FALSE)"

I'm assuming that one or more of the missing parameters from the Print call
are what I need to set, but I have no idea what the missing parameters do.
In the past, I've used SendKeys to interact with dialog boxes, but that isn't
working either. I'm running Office 2007, if that makes a difference.

Can anyone help with this? Thanks!


From: Jim Thomlinson on 15 Sep 2008 13:39
Not sure if this helps...

Public Sub PageSetupXL4M( _
Optional LeftHead As String, Optional CenterHead As String, _
Optional RightHead As String, Optional LeftFoot As String, _
Optional CenterFoot As String, Optional RightFoot As String, _
Optional LeftMarginInches As String, Optional RightMarginInches As
String, _
Optional TopMarginInches As String, Optional BottomMarginInches As
String, _
Optional HeaderMarginInches As String, Optional FooterMarginInches As
String, _
Optional PrintHeadings As String, Optional PrintGridlines As String, _
Optional PrintComments As String, Optional PrintQuality As String, _
Optional CenterHorizontally As String, Optional CenterVertically As
String, _
Optional Orientation As String, Optional Draft As String, _
Optional PaperSize As String, Optional FirstPageNumber As String, _
Optional Order As String, Optional BlackAndWhite As String, _
Optional Zoom As String)

'based on a post by John Green in
'microsoft.public.excel.programming
'on 21 January 2001:
'http://google.com/groups?selm=VA.00000b2f.0028c7e5%40mara9"
Const c As String = ","
Dim pgSetup As String
Dim head As String
Dim foot As String
If LeftHead <> "" Then head = "&L" & LeftHead
If CenterHead <> "" Then head = head & "&C" & CenterHead
If RightHead <> "" Then head = head & "&R" & RightHead
If Not head = "" Then head = """" & head & """"
If LeftFoot <> "" Then foot = "&L" & LeftFoot
If CenterFoot <> "" Then foot = foot & "&C" & CenterFoot
If RightFoot <> "" Then foot = foot & "&R" & RightFoot
If Not foot = "" Then foot = """" & foot & """"

pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
End Sub
--
HTH...

Jim Thomlinson


"Frustrated" wrote:

> Hi,
>
> I'm trying to write a macro that will print a number of worksheets to
> different PDF files. I've used the macro recorder to generate sample code,
> and it uses ExecuteExcel4Macro to execute the print function.
>
> My problem is that when the code runs, the print dialog box waits for me to
> give it a file name (I don't want the default) and to click 'Save'. This is
> what I want to automate so that there is no user interaction. This is the
> code that the macro recorder made for me:
>
> ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne01:"",,TRUE,,FALSE)"
>
> I'm assuming that one or more of the missing parameters from the Print call
> are what I need to set, but I have no idea what the missing parameters do.
> In the past, I've used SendKeys to interact with dialog boxes, but that isn't
> working either. I'm running Office 2007, if that makes a difference.
>
> Can anyone help with this? Thanks!
>
>
 | 
Pages: 1
Prev: localeSpecificCellName
Next: Launching PowerPoint from an Excel application