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

D

duckie123

New Member
Joined
Dec 7, 2010
Messages
24
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>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Try something like this:

Code: 
Sub Test()
    Dim wdApp As Object
    Dim wdDoc As Object
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim r As Long
    Set wdApp = GetObject(, "Word.Application")
    Set wdDoc = wdApp.ActiveDocument
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
    With Rng
        For r = 1 To .Rows.Count
            wdDoc.Shapes("Text Box " & r).TextFrame.TextRange.Text = .Cells(r, 1).Value & " " & .Cells(r, 2).Value
        Next r
    End With
End Sub

The code assumes that the TextBoxes are named Text Box 1 to Text Box 4.
 
Upvote 0
What you could do is use BookMarks in Word and then code to place the cell value to the BookMark, you should set the References in the Excel VBA screen to use Word Commands.

Select the excel workbook then use ALt + F11 on the keyboard (this takes you into VBA), then Select the Tools Menu, then References, then scroll down the list until you come to Microsoft Word XX. Object Library (XX is the number), click the box and select OK

Code you need is something like this:
Code: 
Sub OpenWordx()
Dim wrdApp as Word.Application
Set wrdApp = CreateObject("Word.Application")
with wrdApp
.documents.open "Enter path and document name.doc"
.Selection.GoTo What:=wdGoToBookmark, Name:="BookMarkName1"
.Selection.TypeText Text:=Range("CellID").Value
end with
End Sub

You would add the other bookmark names and reference each cell
 
Upvote 0
Hi Trevor,
Thank you for posting your response. I tried to follow your suggestion with the bookmarks but I get the message "Object doesn't support this property or method" when I try to run the macro after bookmarking the text box. Do you know what might be the problem? I am new with vba and have tried looking up how to bookmark in word and I can't figure out what I am doing wrong because it seems like I did it right. Any help would be appreciated. Thank you! :)

Also..thank you for your response Andrew...I am new to vba so I could not really follow your code as well :eeek:
 
Upvote 0
If you are receiving errors try doing this.

Open Word and create a document with some text box then add your bookmarks, then look to record a macro.

You can then look to use the goto command to find the bookmark this will then give you the code to find the bookmark. You could then adapt the code to run from Excel.

Andrew has given you the best code to work with, but if you aren't a programmer or have a sound understanding of VBA it is a little daunting to follow.

If you can set bookmarks in your document would it be necessary to use textbox shapes?

The following shows the recorded code to find the bookmark and add some text.

Code: 
[FONT=Calibri][SIZE=3]Sub mcrBookmark1()[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]   Selection.GoTo What:=wdGoToBookmark, Name:="Title"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]   Selection.TypeText Text:="Mr"[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

Then you would adapt that as per my suggestion to get the cell value to place in the bookmark in the document. You do need to set the References in the VBA screen of Excel to use Microsoft Word (Version you are using). Object Library.

Can you show your code you have created so far !
 
Upvote 0
Hi Andrew,
I am not sure how to use your code. Could you please explain the second half of it please? I am having trouble bookmarking the text inside the textbox. I have a lot of text boxes all over the page that I need to enter data into from specific cells from my excel sheet. I keep coming across the "Bookmark doesn't exist" error. Thanks for your help.
 
Upvote 0
My code doesn't use bookmarks. What are the names of your TextBoxes and in what order do you want to populate them?
 
Upvote 0
Hi Andrew,
I haven't named my text boxes and am not sure how to do so. I only have just bookmarked them. I would just like the text boxes to contain whatever is in one cell in excel. So textbox 1 would contain cell A1 etc. Thank you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
P
  • Question Question
Extract the word which carries the specific text
Replies
5
Views
218
Cubist
Cubist
C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
127
Kaper
Kaper
N
  • Question Question
Excel VBA copy data to a worksheet in list form
Replies
0
Views
123
NewbieExcel123
N
I
  • Solved
Excel Filtering for a Summary Tab
2 3 4
Replies
35
Views
653
ItalianPlatinum
I

Forum statistics

Threads
1,224,602
Messages
6,179,848
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top