macro to transfer data from excel to specific text boxes in word

[duckie123]

Hi Everyone,
I am have an excel spreadsheet with two columns of data. I would like to transfer that data into text boxes that I have lined down the page in word. I was wondering if this is possible. Any kind of help would be greatly appreciated. thank you!


Here is an example of the data i would like to have in each text box in the word document. For example, in one text box, I would like to have Red 66
In another text box, I would like to have Blue 43.<table width="136" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <col style="width: 54pt;" width="72"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" width="64" height="20">Color</td> <td style="width: 54pt;" width="72">Frequency</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Red</td> <td align="right">66</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Blue</td> <td align="right">43</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Green</td> <td align="right">34</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Gray
</td> <td align="right">93
</td> </tr> </tbody></table>

 

[Andrew Poulsom]

You can get a list of the TextBox's names by typing this into the Immediate window in the VBE:

For i = 1 to activedocument.Shapes.Count:debug.Print activedocument.Shapes(i).Name:next i

and pressing Enter. To change the name of the first shape:

activedocument.Shapes(1).Name = "Text Box 1"

 

[duckie123]

Hi Andrew,
I have entered this code into the vba window and pressed enter and I do not come up with a list of the text box names. Is there another way to do this? Can I manually name all the text boxes? Thanks.

 

[Andrew Poulsom]

Did you do that in the Word Visual Basic Editor while your document was active?

 

[duckie123]

Hi Andrew,
Yes I have pasted it into the word vba while the document was active and pressed enter but nothing happened. Thanks.

 

[Andrew Poulsom]

In the Immediate window? It should return a list of the names below. You could try running this from the module window:

Sub Test()
    Dim i as Long
    For i = 1 to ActiveDocument.Shapes.Count
        Debug.Print ActiveDocument.Shapes(i).Name
    Next i
End Sub

 

[Norie]

Am I missing something here?

Why not just use Word mail merge?

 

[beenbee]

I know this is a while ago but I have had the same issue and hadn't been able to find a proper solution on the web, this one came closest. However I have solved my issue but adding bookmarks in textboxes and adapting the code userforms use to add the data to bookmarks. I am using a word template. I added this code to a button on my excel workbook.

Sub Pallet_Marker()

Dim wrdApp As Word.Application
Dim strTemplateName As String

Set wrdApp = CreateObject("Word.Application")
wrdApp.Visible = True
strTemplateName = "C:Your Extention.dot"
Set wrdDoc = wrdApp.Documents.Add(strTemplateName)
Application.ScreenUpdating = False

With wrdDoc
 .Bookmarks("CustName").Range.Text = Range("C8").Value
 .Bookmarks("Ref").Range.Text = Range("D16").Value
 .Bookmarks("SpecNo").Range.Text = Range("B16").Value
 .Bookmarks("Qty").Range.Text = Range("C16").Value
 .Bookmarks("RCLon").Range.Text = Range("J8").Value
 .Bookmarks("Custon").Range.Text = Range("A16").Value
 .Bookmarks("DeliveryAdd").Range.Text = Range("C13").Value
End With
End Sub

You must add word object library in refrences in VBA editor.

All the best.