Excel Variable pass to Word Text Form Field

  • 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!


  • 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:

    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)

    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.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!