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

 

[Damon Ostrander]

Hi jwsmith,

This is a job for Automation (used to be called OLE Automation). You can easily transfer data from Excel to Word and control all the Word formatting from Excel VBA using Automation. You could also do it the other way--pulling the data from Excel using VBA in Word. There are some helpful examples in the VBA helps (both in Excel and Word), as well as lots of Excel-Word Automation examples posted on this board. Check out the VBA help topic "Understanding Automation."

 

[ak_swedegirl]

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?

If were you, I'd create a template in Word containing fields/bookmarks for the data you want to import from Excel. Then use VBA in Excel to create new documents based on the template, plugging in the relevant data where needed.

I don't have time right now to elaborate, but I'm sure searching the archives will produce something. :wink:

 

[dtaylor]

Hi There - this is very possible, as already stated. and i am currently working a file do just this - using bookmarks as ak_swedegirl suggested.

build your word template and place your bookmarks where needed, i would also change the names of the bookmarks.

in excel set a reference to
Microsoft Word 9.0 Object Library

Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim BMRange As Word.Range
Dim SalesPer As Range

Set wdApp = CreateObject("Word.Application") 'Create an instance of word
Set wdDoc = wdApp.Documents.Open(File Path and word file name)'Open word file

'now set your excel ranges
Set SalesPer = ThisWorkbook.Sheets("WordRange").Range("c17")

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

'Pass your excel range to the word bookmark
BMRange.Text = SalesPer 

'Save your word doc
With wdApp.ActiveDocument
    .Save
    .Close
End With

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

this should get you started, depending on the amount of data you need to pass to word it could be alot of coding..

Good Luck

Dan

 

[jwsmith]

Wow! Thanks a lot everyone, especially dtaylor and ak_swedegirl! I didn't realise that I could import the reference to the word api. Now it's all working perfectly exactly how I wanted.

What I did is create a word template, as suggested, with various bookmarks in, then in my Excel sheet added the following code:

Dim wdApp As Word.Application
Dim wdDoc As Word.Document

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

' Open word template
Set wdDoc = wdApp.Documents.Add("C:\projects\excel\invoice.dot") 

' Set each bookmark text to data from an array
wdDoc.Goto(what:=wdGoToBookmark, name:="companyName").Text = companies(C).name

' Set the document to be visible for manual changes
wdApp.Visible = True

I put this in a function which will generate a word invoice from a template for a particular invoice.

Thanks again everyone, you've been a great help.

James

 

[immerse]

Hi all,

I appreciate this topic, as its very important and less covered.. especially for novice..

I'd like to suggest we can try to advance this discussion.. I' in a similiar situation.. what about importing a chart from excel into word, how would i control that in a word document..

i don't want to link, just want to do it via code, so i'm formatting a nice looking report with tables and charts.. how is this possible with vba and transferring it across?

 

[dtaylor]

immerse -
this is something that i wanted to do for awhile now. the last time i tried the path i took was -
create a .gif out of an excel chart then insert onto a word doc as a picture.....i almost had it down but was running into chart position issues in word..neverless i did run out of time on the project and stayed with old true excel (moving data from word to excel)...

but i did come across a site that covers what you are looking for, not tested though.

http://www.peltiertech.com/Excel/

goto the link 'Using Excel with Other Office Applications'
then find 'Paste the Active Excel Chart at the Cursor in the Active Word Document'

good luck.. i do not have time to incorp this into my process - i am buried currently ..but if you do get it to work please let us know.

Dan

 

[zmaros]

Hi, I have a very simple question (but not for me).

I would like to call from EXCEL a WORD procedure.
How could I do that?

Thanks in advance for your help.

 

[zmaros]

Hi, I really need your help!!!

I have with the next code and when I call this code a have two messages from the system. If the Word file is opened, then I have no messages from the system. What do I do wrong?

Sub nyit1()
Dim chN As Long, nd As String, wd As String
nd = "C:\pro.doc"
wd = "WinWord"
chN = Application.DDEInitiate(wd, nd)
Application.DDEExecute chN, "[CALL ZOLI]"
Application.DDETerminate chN
End Sub

First message:

Remote data not accesible.
To access this data Excel needs to start another appication. ...
Start application "WinWord.exe"?
Yes, No

Second message:

Run-time error: 13
Type mismatch

 

[Damon Ostrander]

Hi again Zmaros,

If you are trying to use this with your other code (in your previous posting), it is incompatible with it. The code you just posted uses DDE to establish a connection with Word, while your previous posting used Automation. I suggest you stick with Automation since it is easier to use and more capable.

To call a macro in Word from Excel using Automation you can do the following:

wdApp.Run "MyMacro"

where MyMacro is the name of the macro residing in an open Word document, and where wdApp is the name of the object variable you have assigned the Word application to (via CreateObject or GetObject).

Damon