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