PDA

View Full Version : Exporting Excel Data to Word Using VBA



mew0717
01-10-2008, 10:40 AM
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

Dave
01-11-2008, 06:15 AM
You didn't mention to much detail re. your Excel data. Here's a guess on a loop (untested). Not sure if you need to keep opening and closing the template (which this code does) OR just open the template once and replace the bookmark data with each loop and then close it when done (this would make more sense) HTH. Dave

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
Dim Cnt As Integer
Dim LastRow As Integer
LastRow = Sheets("sheet1").UsedRange.End(xlDown).Row
Set wdApp = CreateObject("Word.Application") 'Create an instance of word
For Cnt = 2 To LastRow
Set wdDoc = wdApp.Documents.Open("C:\etc") 'Open word file
'now set your excel ranges
Set company = ThisWorkbook.Sheets("CompanyInfo").Range("A" & Cnt)
Set address = ThisWorkbook.Sheets("CompanyInfo").Range("B" & Cnt)
Set city = ThisWorkbook.Sheets("CompanyInfo").Range("D" & Cnt)
Set state = ThisWorkbook.Sheets("CompanyInfo").Range("E" & Cnt)
Set zip = ThisWorkbook.Sheets("CompanyInfo").Range("F" & Cnt)
'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
Next Cnt
'Close out word
wdApp.Quit
Set BMRange = Nothing
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

mew0717
01-11-2008, 11:49 AM
Dave, This worked perfectly for what I am trying to accomplish for the first part. Let me try to clarify a little more on my excel spreadsheet and the word template. I've attached some images for clarification.

The first sheet is called CompanyInfo, and that is what we're using so far in the code. Its populating the address information in my word template.


The second Sheet is called CompanyRev. It essentially contains all the sales/invoicing information for our companies. Keep in mind, however, that not every company sells the same products, nor do each company have the same payout for each product.

I've zipped up some images that might help clarify a little more.

Could you offer some insight on how to get the information from the company revenue page over to word? That would just about finish up the project.

Thanks!

Matt
7613

rlv
01-11-2008, 01:46 PM
This has the feel of being homework for some class. Is that the case?

You can manipulate the windows clipboard to move the data programatically, such as

SomeExcelRange.Copy
Then something along the lines of

Word.Application.Selection.Paste
as a very basic paste operation, though there are more ways than that. BMRange.Paste might work too.

For example, I have used statements of the form

Word.Application.Selection.PasteSpecial DataType:=wdPasteRTF
to paste excel ranges into word documents as tables.

mew0717
01-11-2008, 02:10 PM
I wish it were for a class... Then i could get the hot chick in my class to help me. Unfortunately, I'm trying to come up wtih this for work. One of my colleagues spends literally 3 whole days updating this information manually, when it could be done in seconds with the right VBA code.


Not sure how to tell it where to paste in word with the clipboard manipulations.

Dave
01-12-2008, 07:47 AM
mew... I'm not sure of your output format (ie. companyA:widget1, widget2, etc OR widget1: companyA, companyB, etc. You first need to do some manipulation on the companyrev sheet to sum together your needed output. I'm not sure if you actually want to reformat the data on companyrev sheet using VBA or just do the calculations within VBA without changing the data on the CompanyRev sheet? It would be easier to transfer the data To the Word template if it was first reformatted on the companyrev sheet. You should consider using a table in the Word template(if it's not already a table?) for the companyrev data (may be the only way to put variable amounts of data into a template?). Here's some code that may help get you started.
To sort the CompanyRev sheet based on product. (Change "B2" to "C2" to sort by company)

Dim sortrange As Range, LastRow As Integer
LastRow = Sheets("CompanyRev").UsedRange.End(xlDown).Row
Set sortrange = Sheets("CompanyRev").Range("A1:F" & LastRow)
With sortrange
.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End With


I have some relevant table code somewhere if that's what you need. HTH. Dave

mew0717
01-14-2008, 10:10 AM
Some Table code for Word may help. I guess I'd like it to insert a new row in the table for each product, but i would like the size of the table to be a determined size (that is, the table in word looks the same regardless of how many products a compnay sells. It would just have those cells filled in with data.)

Could you give some more direction on where to put that code you posted earlier? Comments maybe? Here is the code I have thus far. Thanks for all the help you've given currently.





Sub test3()

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 Cnt As Integer
Dim LastRow As Integer
LastRow = Sheets("CompanyInfo").UsedRange.End(xlDown).Row


Set wdApp = CreateObject("Word.Application") 'Create an instance of word

For Cnt = 2 To LastRow
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("A" & Cnt)
Set address = ThisWorkbook.Sheets("CompanyInfo").Range("B" & Cnt)
Set city = ThisWorkbook.Sheets("CompanyInfo").Range("D" & Cnt)
Set state = ThisWorkbook.Sheets("CompanyInfo").Range("E" & Cnt)
Set zip = ThisWorkbook.Sheets("CompanyInfo").Range("F" & Cnt)

'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 & "\stats\" & company & ".doc"
.Close

End With
Next Cnt

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

End Sub

Dave
01-15-2008, 12:46 AM
I guess I'd like it to insert a new row in the table for each product, but i would like the size of the table to be a determined size (that is, the table in word looks the same regardless of how many products a compnay sells

Mew a table that adds new rows yet retains it's size... seems interesting. Are you suggesting that the font and table cell size would get smaller with each product added? What about a company that has lots of products that make the font to small/annoying to view? You have to know exactly what the output format is before you can make it. It is very important to decide if you want to group the output by company or product... it really is dependant upon your needs. I think the notion will be to insert/change a variable sized table into the template (don't know if it's possible) and then fill it with the data that is pre-formatted on the comprev sheet. Code may be needed to accomodate page split/continuation of the table if that's needed and/or possible. I'm guessing that it won't be needed as the table will never be that big. Perhaps abit more info Mew. Dave
ps. Just make that code a sub and call it to trial sorting your data.... note it does not sort your data back to it's original state when your done trialling so don't save your changes if needed.
pps. Please shorten that file directory line of code. It's annoying.

mew0717
01-18-2008, 03:08 PM
Hey dave, sorry for the delayed response. What I mean is that the general table is a fixed size (that can grow if more data makes it so, but does not shrink if there is only one or two rows of data) The font-size and text will be consistent throughout. I basically have a main table and would insert a column within that cell when necessary. Any table code to help with this? I apologize if that is even more confusing.

Dave
01-22-2008, 08:01 AM
Mew I'm fairly close to a solution for you (I think) but it would be a whole lot better if you could post a workbook and the relevant Word file. If you haven't found a solution yet, I'll spend some more time on it. Dave

mew0717
01-22-2008, 09:10 AM
Dave, I don't have an actual workbook set up yet, just a theoretical one. I do have the word doc that i'd like to use though. If i zipped those, would that be enough for you?

Dave
01-22-2008, 10:30 AM
Mew the Word doc would be most helpful. I have a wb set up. Dave

mew0717
01-24-2008, 10:46 AM
Dave,

I've uploaded a sample of what the document looks like. In the "Description" is where something along the lines of "X sales of Y product @ $Z.00 a sale"
Would go. I originally used a template to prepopulate the address and company info with bookmarks, but I am not sure how to add more "descriptions" and dates/prices that go along with those descriptions. The document should clarify what i'm talking about as that probably sounds terribly confusing.

Sorry for the delay, I have been sick the past few days and out of the office.

Dave
01-24-2008, 09:47 PM
Mew I'm glad to hear your well. If you could post the original template file that includes all of the bookmarks would be better. I think changing the number of table columns/row won't be that hard. You want the data combined in the "descriptions" cell rather than seperate table cells? What about the original data...does it matter if it gets sorted and stays sorted? It's not necessary to sort it... just abit more VBA without sorting it. Stay well. Dave

mew0717
01-25-2008, 09:16 AM
Mew I'm glad to hear your well. If you could post the original template file that includes all of the bookmarks would be better. I think changing the number of table columns/row won't be that hard. You want the data combined in the "descriptions" cell rather than seperate table cells? What about the original data...does it matter if it gets sorted and stays sorted? It's not necessary to sort it... just abit more VBA without sorting it. Stay well. Dave
Hey Dave,

Im going to zip up the template for office 2007 and a 97-03 compatible word doc incase you dont have office 2007. (I've added a few more bookmarks so you can see what else i'm hoping to automate). I only added one bookmark in the description field, because I dont know how to add data for each company where it's necessary, as there data differs. Does that make senes?

The original data can be sorted, that's fine. I assume it will be sorted first by the company name, then by offers for that company?

If we could use VBA to sort that would be best, because the person im working on this for will most likely just cut and paste the data (that he gets from different spreadsheets) into one. Then i'd just have him hit a button to generate the reports in word.

The only thing is, is it possible to have the company information in one worksheet in the workbook and the revenue information in a different worksheet in that same workbook?

In regards to how the descriptions are displayed in the word file... It would probably be best to keep that as one cell and use carriage returns, but i assumed it might be easier to try to insert new cells. I need to have those date ranges and prices match up with the start of each descriptions, some of which take up multiple lines.

If I could ask one more thing ( I know i know, im probably killing you with requests!), could you comment out your VBA for me? I really learn a lot of how VBA works through comments and understanding the logic of the code really makes a difference. Kind of a "give a man a fish, feed him for a day, teach a man how to fish feed him for a lifetime" mantra there... It could be me offering this advice to someone the next time the question pops up.

I've made additional notes on the word doc to hopefully clear up any confusion.

Thanks!

Matt

Dave
01-25-2008, 06:51 PM
I like Matt better than Mew. :hi: I'm interested in learning abit more about XL and Word bookmarks and tables. I've put together some code below that probably outlines a solution for you. It copies the blank .doc file and then makes changes to the copied file... hmm seems almost like a template (but hate those things and I couldn't open your dot. file.). The code can include a loop for multiple companies and docs. Just need "abit" of code for the starred sections below. I'm not sure if an existing table can be altered for row length OR a new table with new dimensions added to accomodate the variable data? Maybe someone else knows this or abit of searching will answer this. Company info and revenue info on different sheets is perfect. Ask questions if my comments aren't clear (I often have trouble myself). To trial the code the source doc and the transfer directory must exist... so some editting for your locations will be needed before trialling. Have a nice wkend. Dave

Option Explicit
Public Sub FillDoc()
Dim Wapp As Object, wdDoc As Object, Fs As Object
Dim Sourcefile As String, Transferfile As String
'*sort data code here
'!!sourcefile must exist!!
Sourcefile = "D:\Publisher Payment form.doc"
Set Wapp = CreateObject("Word.Application")
'****loop companies here. Create new ouput doc names.
Transferfile = "D:\TEST1.doc" 'for test
'copy blank file to transfer file
On Error GoTo RetErr
Set Fs = CreateObject("Scripting.FileSystemObject")
Fs.copyfile Sourcefile, Transferfile
Set Fs = Nothing
'Open transfer file
On Error GoTo RetErr2
Set wdDoc = Wapp.documents.Open(Transferfile)
'*add bookmarks data and variable size table to file
'*add data to the existing table cells eg....
With wdDoc
.Tables(3).Cell(3, 1).Range.Text = "Matt's Dates"
.Tables(3).Cell(3, 2).Range.Text = "Matt's Description"
.Tables(3).Cell(3, 3).Range.Text = "Matt's Prices"
End With
'close and save transfer file with changes
Wapp.activedocument.Close savechanges:=True
Set wdDoc = Nothing
'****end document creation loop
'Wapp.documents.Open Filename:=Transferfile, ReadOnly:=False
'Wapp.Visible = True
Wapp.Quit 'comment out when using above 2 lines of code
Set Wapp = Nothing
Exit Sub
'handle errors
RetErr: On Error GoTo 0
MsgBox "File copy Error"
Set Fs = Nothing
Wapp.Quit
Set Wapp = Nothing: Exit Sub
RetErr2: On Error GoTo 0: MsgBox "Open file Error"
Set wdDoc = Nothing
Set Fs = Nothing
Wapp.Quit
Set Wapp = Nothing
End Sub