macro to transfer from excel to word

  • Thread starter Thread starter risketto
  • Start date Start date
R

risketto

Hello
I would like to know if it is possible to set up a macro in excel 2003 so it
transfers data to word 2003. Funny enough, I could set up the macro in word
to import the date from excel, but not the way round.

Thanks
 
Hi,

I needed to do somethig similar a while ago (populate a Word Dashboard
Report with XL charts). this worked for me (I'm not a programmer so it's
probably not the best code, but it worked for me :-) ):

Sub Populate_dashboard()

Dim WDApp As Word.Application
Dim WDDoc As Word.Document

' Open Report template - You need a prepared Word template somewer else for
this to work
Set WDApp = CreateObject("Word.Application")
Set WDDoc = WDApp.Documents.Open("<full path to the
template>\Template.dot")

' Copy whatever it is in XL you want to put in the Doc to the clipboard as a
picture (so it cannot be manipulated in Word) - you may want to change this
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen,
Format:=xlPicture

' Position Cursor in Word
WDApp.Selection.MoveDown unit:=wdLine, Count:=1

' Paste Chart as picture in Word
WDApp.Selection.PasteSpecial Link:=False,
DataType:=wdPasteMetafilePicture, Placement:=wdLine, DisplayAsIcon:=False

' Do more stuff

End Sub

Incidentally I've stopped doing these in Word now - I do it all in XL,
mainly because I found it easier to contol the formatting of the output -
especially when the reports are printed at on different size paper

Hope this helps

Dave
 
I've done this many times! In Word, click Insert > Field > Field Name =
DocVariables... name your variable. Enter a few more...
Hit Alt+F9 to see your DocVariables.


In Excel, Insert > Name > Define... name your range... NamedRange in Excel
must equal DocVariable in Word.

In Excel, create a Modeul and copy paste this code into the Module:
Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
'On Error Resume Next

objWord.ActiveDocument.variables("BrokerFirstName").Value =
Range("BrokerFirstName").Value
objWord.ActiveDocument.variables("BrokerLastName").Value =
Range("BrokerLastName").Value

objWord.ActiveDocument.Fields.Update

'On Error Resume Next
objWord.Visible = True

End Sub

Save and run and life is good.

HTH,
Ryan---
 
Back
Top