Exporting Excel Data to a Word using VBA

mew0717

New Member
Joined
Feb 17, 2006
Messages
18
Okay, so I tried (for hours) to find a solution to this and have finally gotten frustrated to the point where i think asking here will be my best bet.


Basically I want to export data from excel worksheets to various places on a word document I had. I have created a word template as well as bookmarks for that template, as that seems to be the recommendation for performing such a task with excel.

My problem is ... Now what?

I basically have 1 constant worksheet that contains all my affiliates info (Name, Address etc), which i'd like to use to populate some bookmarks in my word template, and then i have another worksheet for the previous month, indicating sales etc for that affiliate. I intend on adding a new worksheet every month that i would use to create new invoices.

Furthermore, i'd really like to be able to update a new file (or perhaps a new page on the same file) for each company with the use of a button, similar to the one's you'd see in an access form.

Can anyone help? Let me know if you need me to clarify.

Thanks!

Matt
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mew0717

New Member
Joined
Feb 17, 2006
Messages
18
Okay I took a look at that post, it was helpful but I am still very confused. When I listed each cell individually and paired that to a bookmark, that worked great. However, Im definitely going to need a loop because I need a different file for each "Company". I tried to copy his loop but I dont really undertsand enough VBA to make it work for me. Particularly Loops/Next Statements. The Error I'm getting is "Runtime Error 1004" , Application-defined or object-defined error.

My code is bleow. Any suggestions?

Sub Test2()

Dim sBkmks() As String
Dim wdBkmk As Word.Bookmark
Dim rBkmk As Word.Range
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim iBkmk As Integer

Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Add("C:\Documents and Settings\mwildrick\Desktop\TEST\Publisher Payment form.dotm")

ReDim sBkmks(1 To wdDoc.Bookmarks.Count)
' loop to get names
For iBkmk = 1 To wdDoc.Bookmarks.Count
sBkmks(iBkmk) = wdDoc.Bookmarks(iBkmk).Name
Next
' loop to populate bookmarks
For iBkmk = 1 To wdDoc.Bookmarks.Count
Set rBkmk = wdDoc.Bookmarks(sBkmks(iBkmk)).Range
rBkmk.Text = _
ActiveWorkbook.Names(sBkmks(iBkmk)).RefersToRange.Value
' omit this if you don't need the bookmarks again
' the above step obliterates the bookmark
' the following step restores the bookmark
wdDoc.Bookmarks.Add sBkmks(iBkmk), rBkmk
Next
End Sub
 

mew0717

New Member
Joined
Feb 17, 2006
Messages
18
I thought i'd put a more indepth explanation of what i'm looking for. I posted this in another forum as well.

Okay, basically I have a Word template, which I want to populate with data from excel in predertimined positions on that word template. Currently I'm using bookmarks in word and some VBA in excel to acheive this effect.

My excel document has 2 worksheets.


worksheet1 has contact information:

A - Name
B - Address 1
C - Address 2
D - City

etc. etc.

The names of these fields (i.e. "Name") are also the names of the ranges containing all the data in that field which I want to correspond with a bookmark in word where this data should prepopulate (the bookmark "Name" for example).


worksheet 2 has revenue generating information

A - Name
B - Date Range (12/01/07 - 12/31/07)
B - Product Sold
C - Number of product sales
D - Commission Due Per sale of Product.

Now, Some people sell different products than others, and not every person receives the same payout for their product sold.

My invoice form in word is essentially a table with three columns.

- The first column is the date range.
- The second column is what product they sold at whatever rate their commision is (X sales of Y product at z/sale)
- The third column is the total commison owed for that product.

A new row in the table is formed for every product that person has earned commision on. The final row of the table has the total amount owed for that person (all the other numbers summed).

The revenue data naturally must match the data from the contact info worksheet as well.

Can anyone provide some code examples. I am not really sure how to use loops to acheive this effect. I'll show to examples of code below...

the first one does not use a loop, but I was able to prepopulate the contact information for one person.

The second one uses a loop, but i get an error as i pulled it off another BB looking for help.

I wish to create a different invoce for each person, as they then would be printed and mailed out. Any ideas or help would be greatly appreciated.

===== CODE EXAMPLE 1 ======
Sub test()

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim BMRange As Word.Range
Dim company As Range
Dim address As Range
Dim address2 As Range
Dim city As Range
Dim state As Range
Dim zip As Range
Dim CuurentDate As Date


Set wdApp = CreateObject("Word.Application") 'Create an instance of word
Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\mwildrick\Desktop\TEST\Publisher Payment Form.dotm") 'Open word file



'now set your excel ranges
Set company = ThisWorkbook.Sheets("CompanyInfo").Range("A2")
Set address = ThisWorkbook.Sheets("CompanyInfo").Range("B2")
Set city = ThisWorkbook.Sheets("CompanyInfo").Range("D2")
Set state = ThisWorkbook.Sheets("CompanyInfo").Range("E2")
Set zip = ThisWorkbook.Sheets("CompanyInfo").Range("F2")




'Set your word bookmark
Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="company")
BMRange.Text = company

Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="Address")
BMRange.Text = address

Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="City")
BMRange.Text = city

Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="State")
BMRange.Text = state

Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:="Zip")
BMRange.Text = zip

'Save your word doc
With wdApp.ActiveDocument
.SaveAs ThisWorkbook.Path & "\" & company & ".doc"
.Close
End With

'Close out word
wdApp.Quit

Set BMRange = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing

End Sub


==== CODE EXAMPLE 2 ======

Sub Test2()

Dim sBkmks() As String
Dim wdBkmk As Word.Bookmark
Dim rBkmk As Word.Range
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim iBkmk As Integer

Set wdApp = CreateObject("Word.Application")
Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\mwildrick\Desktop\TEST\Publisher Payment form.dotm")

ReDim sBkmks(1 To wdDoc.Bookmarks.Count)
' loop to get names
For iBkmk = 1 To wdDoc.Bookmarks.Count
sBkmks(iBkmk) = wdDoc.Bookmarks(iBkmk).Name
Next
' loop to populate bookmarks
For iBkmk = 1 To wdDoc.Bookmarks.Count
Set rBkmk = wdDoc.Bookmarks(sBkmks(iBkmk)).Range
rBkmk.Text = _
ActiveWorkbook.Names(sBkmks(iBkmk)).RefersToRange.Value
' omit this if you don't need the bookmarks again
' the above step obliterates the bookmark
' the following step restores the bookmark
wdDoc.Bookmarks.Add sBkmks(iBkmk), rBkmk
Next

'Save your word doc
With wdApp.ActiveDocument
.SaveAs ThisWorkbook.Path & "LOOPTEST.doc"
.Close
End With

'Close out word
wdApp.Quit
Set rBkmk = Nothing
Set wdApp = Nothing
Set wdDoc = Nothing

End Sub
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,612
Messages
5,765,419
Members
425,286
Latest member
CazzaBabes

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
Top