Help Passing Excel Variable to Word:

[MattDW]

Would there be someone who could help me with a problem I am having trying to pass an Excel variable over to a Microsoft Word document?

I have an Excel document that contains entered data and I have a Word document that is setup as a mail merge document and VBA code. Currently, I enter data into the Excel spreadsheet and I run the Word document. The VBA in the Word document prompts me to enter a row number and it then pulls data from that row and merges. I have two such Word documents like this I use. One to generate a ticket and the other to generate an email.

In order for this to be a little more automated I would like to do is to change this to use an intersect so that when an X is placed in a cell in a specific column this triggers the code to launch the Word document. I'd like to pass the active row number to Word as a variable so that I can get rid of the message box prompt system I'm using currently.

I've researched this quite a bit today online and the closest I've come so far is this code:

Public StrRecordNumber As String

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim WatchRange As Range
    Dim IntersectRange As Range

    'When an "X" Checkbox Has Been Placed in a Column V Cell, Add Date/Time Timestamp
    Set WatchRange = Range("V" & ActiveCell.Row & ":" & "V" & ActiveCell.Row & "")
    Set IntersectRange = Intersect(Target, WatchRange)
    If Not (IntersectRange Is Nothing) And (Target = "x") Then
        Cells(ActiveCell.Row, 22).Value = "X"
    End If
    If Not (IntersectRange Is Nothing) And (Target = "X") Then
        Cells(ActiveCell.Row, 22).Value = Cells(ActiveCell.Row, 22).Value & vbCrLf & " (" & Now & ")"
        'Launch "Template For Computer Repair Ticket in SolarWinds Mail Merge.docm" Mail Merge Document
        Dim WordApp
        Dim WordDoc
        StrRecordNumber = ActiveCell.Row
        Set WordApp = CreateObject("Word.Application")
        Set WordDoc = WordApp.Documents.Open(Filename:="\\srvr\tech\Netbook and Laptop Check-In-Out Log\Outlook 2010 Templates For Items Going To Repair or Ready to Be Picked Up\Sent Into Repair Templates\Template For Computer Repair Ticket in SolarWinds Mail Merge Auto.docm", ReadOnly:=True)
        WordApp.Visible = True
        WordApp.Application.Run "MailMergeDisplayinNewDoc", StrRecordNumber
        WordDoc.Close
        WordApp.Quit
        Set WordDoc = Nothing
        Set WordApp = Nothing
    End If

This almost works. However, the "MailMergeDisplayinNewDoc", StrRecordNumber syntax is generating the error message "Run-time error '450' Wrong number of arguments or invalid property assignment" and I'm not entirely sure why. If I remove the attempt to pass variable/data the error goes away and the Word document launches. However, the variable data isn't passed in any way.

I'm sure I'm missing something in my code.

Any help would be greatly appreciated! Thanks much!

Matt

 

[Kenneth Hobson]

You need to specify the Module it is in. Here an example Application.Run to run a macro in another workbook.

Application.Run "'" & ThisWorkbook.Path & "\RunExample.xlsm'!Module2.Button807_Click", 3

 

[Macropod]

Re-posting following a MrExcel system crash. Since multiple posts in this thread were lost and I'm having to recreate the following from memory of the lost discussion, some details may vary.

Instead of automating Word to run a mailmerge for a single record, it would be more efficient to automate Word to create a new document from a template and to populate a series of bookmarks in that document. For example:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WatchRange As Range, IntersectRange As Range, r As Long
Dim WordApp As Object, WordDoc As Object, xlWkSht As Worksheet
Const wdFormatXMLDocument As Long = 12

'When an "X" Checkbox Has Been Placed in a Column V Cell, Add Date/Time Timestamp
Set WatchRange = Range("V" & ActiveCell.Row & ":" & "V" & ActiveCell.Row & "")
Set IntersectRange = Intersect(Target, WatchRange): r = ActiveCell.Row
If Not (IntersectRange Is Nothing) And (Target = "x") Then
  Cells(r, 22).Value = "X"
End If
If Not (IntersectRange Is Nothing) And (Target = "X") Then
  Set xlWkSht = ActiveSheet
  xlWkSht.Cells(r, 22).Value = xlWkSht.Cells(r, 22).Value & vbCrLf & " (" & Now & ")"
  'Launch "Template For Computer Repair Ticket in SolarWinds Mail Merge.docm" Mail Merge Document
  Set WordApp = CreateObject("Word.Application")
[B]  With WordApp
    'The next line can be deleted/commented out once you're sure the code is working
    .Visible = True
    Set WordDoc = .Documents.Add("\\srvr\tech\Netbook and Laptop Check-In-Out Log\" & _
      "Outlook 2010 Templates For Items Going To Repair or Ready to Be Picked Up\" & _
      "Sent Into Repair Templates\Computer Repair Ticket in SolarWinds.dotm")
    With WordDoc
      .Bookmarks("Ticket").Range.InsertAfter xlWkSht.Range("A" & r).Value
      .Bookmarks("Name").Range.InsertAfter xlWkSht.Range("B" & r).Value
      .SaveAs2 ThisWorkbook.Path & "\" & xlWkSht.Range("A" & r).Value & ".docx", _
        wdFormatXMLDocument, , , False
      .Close False
    End With
  .Quit
  End With
  Set WordDoc = Nothing: Set WordApp = Nothing: Set xlWkSht = Nothing[/B]
End If
End Sub

Note the bolded code. That code creates a new document from a template named 'Computer Repair Ticket in SolarWinds.dotm', populates bookmarks named 'Ticket' and 'Name' with the corresponding Excel data for columns A & B, respectively, in the active row, then saves the document this created to the workbook's folder, named according to the value in column A.

 

[MattDW]

Thank you Paul for taking the time to rewrite and recreate the content of your original post. Unfortunately I hadn't had a chance to revisit this thread until today and I didn't realize until I pulled up the post that the MrExcel servers had crashed and posts in the threads had been lost. I'm still very interested in looking at you code and the alternative method of achieving this.

I also wanted to include here, for anyone who comes upon this post that has a similar question to the one I had (as I find so many answers and I learn a lot simply by reading the threads of others, in this forum and others, that have questions similar to the one that I have) that I found the answer to that question in a February 19, 2010 post by a RichardW in this Ozgrid thread: Pass Variables Between Procedures & Applications He also provided a working sample of an Excel document and a Word document in that thread whereas the Excel document passed variable information over to Word.

Thanks much,

Matt