Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing arguments from Excel to Word macros (XP/Excel 2002)

    I am trying to control a Word application from a macro in Excel as follows:

    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = True
    Set wrdDoc = wrdApp.Documents.Open("S:Plots for dossierPlotsInWord.doc")
    wrdApp.Run "Project.NewMacros.Macro1()"

    and initiate a macro in Word which will add successive pages to the document with a table (1 row, 2 columns) in which to place plots generated in Excel and line of text underneath the table on each page. Since the text and number of pages varies I wanted to call with arguments indicating the number of pages and text to be inserted e.g.

    wrdApp.Run "Project.NewMacros.Macro1(N, Title)"

    however this fails each time. With the above approach I have a blank Word document containing the macro in a code module rather than in the Normal.dot template code module. For maintenance issues on our network I have chosen to take this approach. The Word macro works fine when run from Word alone. I have two questions, firstly is it possible to modify the code to pass arguments in this way and secondly is there a way to open a Word document then place a macro into a code module using VBA then execute the macro. The Word macro code is:

    Sub Macro1()
    ' Dim Title As String
    ' Dim N As Integer
    ' Title = "Graph title"
    ' N = 6
    With ActiveDocument.Styles(wdStyleNormal).Font
    If .NameFarEast = .NameAscii Then
    .NameAscii = ""
    End If
    .NameFarEast = ""
    End With
    With ActiveDocument.PageSetup
    .Orientation = wdOrientLandscape
    .TopMargin = CentimetersToPoints(3.1)
    .BottomMargin = CentimetersToPoints(2)
    .LeftMargin = CentimetersToPoints(4)
    .RightMargin = CentimetersToPoints(2.5)
    .PageWidth = CentimetersToPoints(29.7)
    .PageHeight = CentimetersToPoints(21)
    End With

    For I = 1 To N

    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:= _
    2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
    wdAutoFitFixed

    With Selection.Tables(1)
    .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    .Borders(wdBorderRight).LineStyle = wdLineStyleNone
    .Borders(wdBorderTop).LineStyle = wdLineStyleNone
    .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
    .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
    .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    .Borders.Shadow = False
    .AllowAutoFit = False
    .AllowPageBreaks = False
    .AllowAutoFit = False
    .Rows.HeightRule = wdRowHeightExactly
    .Rows.Height = CentimetersToPoints(13.5)
    .Columns.PreferredWidth = CentimetersToPoints(11)
    End With

    ActiveDocument.Content.Select
    With Selection
    .Collapse Direction:=wdCollapseEnd
    End With
    Selection.TypeParagraph

    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    With Selection.Font
    .Name = "Times New Roman"
    .Size = 16
    .Bold = True
    End With
    Selection.TypeText Text:=Title & " Subject - " & I

    If I < N Then
    Selection.InsertBreak Type:=wdPageBreak
    End If
    Next I
    End Sub

    Anyone with any suggestions?

    Graeme

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Passing arguments from Excel to Word macros (XP/Excel 2002)

    The way to pass arguments is a little bit different:

    Application.Run MacroName, Argument1, Argument2, ...

    In your example, assuming that N and Title are variables:

    wrdApp.Run "Macro1", N, Title

    For this to work, your procedure must have arguments, of course:

    Sub Macro1(N As Long, Title As String)

    Do you really want to put a macro in each document? Why not execute the code directly from Excel?

  3. #3
    New Lounger
    Join Date
    Sep 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing arguments from Excel to Word macros (XP/Excel 2002)

    Hi Hans

    I would much prefer to run the code directly from an Excel macro. When I tried that approach I could open the Word document, insert a table but couldn't set the table properties i.e. the formatting wouldn't work. What I was trying to set up was landscape pages (N of those), insert a table with 1 row and 2 columns and fix those to a set size and prevent resizing then insert a line of text underneath as a title. Later there would be 2 plots copied from Excel (by executing a loop N times) and placed on each page. I assumed that control had to be passed to a Word macro. How could I solve that? The Word macro is above to show what I was trying to achieve. I have assumed that a copy and paste would work effectively for transfer of the plots using table (and cell) indices to select the correct page.

    Thanks

    Graeme

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Passing arguments from Excel to Word macros (XP/Excel 2002)

    While I have no experience manipulating tables copied from Excel to Word, if you have code that works in Word, it should work when automating Word from Excel. The only trick is to "fully qualify" all the objects/properties/methods so that Excel knows exactly what it is manipulating. Thus, for example, any references to ActiveDocument in Word would have to be replaced with a specific document object reference (similarly, Selection would have to be qualified back to the docObject.ActiveWindow.Selection, or something like that).

  5. #5
    New Lounger
    Join Date
    Sep 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing arguments from Excel to Word macros (XP/Excel 2002)

    I think I follow what you are suggesting. I will experiment and see if it works.

    Thanks

    Graeme

Posting Permissions

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