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:Code
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
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.