Creating Word Reports From Excel Data

[jwsmith]

Hi, I have loads of data in an excel spreadsheet which needs to be used for invoice generation, I've been told that the invoices need to be produced in Word, but for my purposes I can't work out how this can be done.

Mail-merge isnt suitable for my job, I want to be able to press a button on my form in excel and for a Word invoice to appear, any ideas of anything that might be useful to me?

I have knowledge of VBA and have used it frequently before but can't find a VBA solution for this!

Thanks

James

 

[zmaros]

Dear Damon,

It's working!!!!!!!!

Thank you so much!!!

Zoltan

 

[UHsoccer]

In the code from dtaylor, how can I do a "save as" of the word document. This way I can keep using the original one

 

[dtaylor]

hi there - wow i am glad to see this post still being used!

to do a save as on an active doc

With wdApp.ActiveDocument
    .SaveAs ThisWorkbook.path & "\Pricing Recommendations v1"
    .Close
End With

hope this helps - i it out of a project i recently finished.

dan

 

[UHsoccer]

I looked at dozens of posts and tried numerous VB codes, but they all failed for me (really more my problem then theirs).

Your code ran on the first pass! Did not know that bookmarks could be used like mailmerge codes.

Thanks for your posts

 

[dtaylor]

to anyone concerned...on my last post, as part of the word doc saveas line, i included 'ThisWorkbbok'.....

i just took this out of an old project neglecting to take out this line

it's is ok if this code is used in conjunction with an excel app (what i had used it for) but with word only change this to your path and document name.

sorry to all board members for that!

dan

 

[UHsoccer]

Reference code from dtaylor:

How can I avoid a fatal error on the statement

Set BMRange = wdDoc.Goto(what:=wdGoToBookmark, Name:=BookMarkName)

I have more bookmarks in Excel than a particular work doc would have

 

[Jon Peltier]

Rather than try to maintain a list in Excel, just use the actual bookmarks in the Word template. First you need to put the bookmark names into an array, then put the Excel data into the bookmark range. To make it easier, use the same text for the bookmark name and the Excel range name. Here's some code, watch out cause I'm working form memory:

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:\temp\mydoc.dot")
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(sBkmk(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 sBkmk(iBkmk), rBkmk
Next

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[UHsoccer]

I understand what you are suggesting and I will experiment with that.

I still have the data input via Excel that is intended to feed several unique documents. So whether I transfer them as I do now or put them in an array, I still need to be able to intercept the placement error when I reference a bookmark in the word document that is not there.

 

[Jon Peltier]

In the project that I lifted the example from, the Excel workbook contains pricing information and calculates the prices of the options selected by the user. The user clicks a button, selects a Word template, which is then populated by a procedure much like I posted. The workbook has many more ranges defined than any of the Word templates, and the purpose of the looping is to collect bookmark names so the information is pulled into Word rather than to pushed from Excel.

- Jon

 

[UHsoccer]

That is a clever way to do it, hope I can figure out how to set it up.

One of my Excel data fields is SEX Male or Female

I then create a entry for word that will correctly substitute "his" or "her". That substitution shows up in several places. As far as I can tell, I will need a unique bookmark for each one, because a given bookmark can only be placed one time.

Is there an alternative?