Moving data from Excel to Word

U

Utkarsh

I have a Sheet in Excel that contains some cells that are named
Heading_1, Heading_2, text1, text2 and contain appropriate data. I wish
to automatically create a table in a Word and copy the data in each of
the named cells to appropriate places in Word. I experimented with some
code picked up from Walkenbach's Excel 2002 Power Programming with VBA
book. Unfortunately the code is giving error at:

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=5,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior,
AutoFitBehavior:= _
wdAutoFitFixed

Would appreciate if someone could help correct my code and help my
learning.

Thanks in advance
Utkarsh
***************************
Sub MakeTable()
' Creates memos in word using Automation (late binding)
Dim WordApp As Object
Dim Heading_1, Heading_2, text1, text2 As String


' Start Word and create an object
Set WordApp = CreateObject("Word.Application")

WordApp.Documents.Open Filename:="C:\Doctest1.doc"

' Send commands to Word
With WordApp
..Documents.Add

'Create a Table in word

ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=5,
NumColumns:= _
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
wdAutoFitFixed

With Selection
..Font.Name = "Arial"
..Font.Bold = True
..TypeText Heading_1
End With

Selection.MoveRight Unit:=wdCell
With Selection
..Font.Name = "Arial"
..Font.Bold = True
..TypeText Heading_2
End With

Selection.MoveRight Unit:=wdCell
With Selection
..Font.Name = "Times New Roman"
..Font.Bold = False
..TypeText text1
End With


Selection.MoveRight Unit:=wdCell
With Selection
..Font.Name = "Times New Roman"
..Font.Bold = False
..TypeText Text:=text2
End With

WdApp.ActiveDocument.Save

' Kill the object
WordApp.Quit
Set WordApp = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top