Excel Variable pass to Word Text Form Field

Joe Derr · Apr 5th 2011

All,

Does anyone know how to pass an Excel Variable to Word? I have two "Text Form Fields" that I want to pass from Excel to Word. One is a Report Number, the other is the Report Date. The Text Form Fields do not have a name, nor can I see how to assign them one. I am at a loss in the MS Word realm, so any help would be appreciated.

The way I have it set up on the Excel file side is I double click in Column A of the Excel Sheet, the Row that contains a QA Failure. It populates two Variables.. RCN (Report Control Number) and RDATE (Report Date) I have the Excel part ready to pass to Word, but I cant figure out how to fill the "Text Form Fields" since they don't have an assigned name... if they do, like I said, I can't figure out where to find it.

Attached is an example of the word doc... if for some reason it says something a accessing a database, or QATracker.xls it's because I tried to mailmerge it with the Tracking Sheet.. but found out that isn't what I was trying to do.. or at least I couldn't get it to work.

Please help.. if you can.

Thanks in Advance!

Joe

iLurk · Apr 6th 2011

Re: Excel Variable pass to Word Text Form Field

The way I do it is by searching for and replacing the "variables" in the Word document with the true figures. Please add these add these as your default values in your text Form Fields

Report Number: [Rep#]
Date of Discrepancy: [DoD]

You add these so that Excel knows what to search for in your Word doc.

Then in your code in Excel add the following:

Code

Dim wrdApp As Word.Application
Dim wrdDoc as word.document


'change file location to where your file is
set wrdDoc = wrdApp.Documents.Open("E:\Testing\Test QA Blank Form - Copy.doc")
Set wrdDoc = copy_data_to_word(wrdDoc, "[Rep#]", RCN.Text)
Set wrdDoc = copy_data_to_word(wrdDoc, "[DoD]", RDATE)

Please note that the Function code below goes outside of your sub (You probably know that, but just in case someone else needs to know)

Code

Function copy_data_to_word(wrdDoc As Word.Document, strFind As String, strReplace As String) As Word.Document
    
    Dim mySelection As Word.Selection
    
    Set mySelection = wrdDoc.Application.Selection
    
    With mySelection.Find
        .Text = strFind
        .Replacement.Text = strReplace
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    
    mySelection.Find.Execute Replace:=wdReplaceAll
    
    Set copy_data_to_word = wrdDoc


End Function

Display More

I'm far from being an expert and there's always better ways of doing things. But this method works for me.

Hope this is helpful.

Excel Variable pass to Word Text Form Field

Participate now!

Share