Copy Excel Contents to Specific Word Table Cells using VBA

[sleepymish]

Hi,

I wrote a number of scripts using VBA for Excel and they all worked
pretty well. But this is the first time I'm writing VBA scripts to go
across different applications, i.e. Word and Excel, and I'm hoping
someone can give me some pointers.

Here's what I want to accomplish:

I have a huge Excel file with one spreadsheet that has many many rows x
columns of data. There's a specific column called "reference" and I
want to copy data from that column into a table in Word.

On the Word side, I have a template (not a .dot) file with front matter
information such as title, date, change history, and reference
information. The reference information is a pre-made table with
different headings.

Now what I want to do is copy specific cells from the Excel "reference"
column into the Word "Reference" table. I know how to copy and paste
data from Excel to Word, the code is below:

Sub invoke_word()
Dim word_app As Object
Set word_app = CreateObject("Word.Application")
word_app.Visible = True

With word_app
'Set myDoc = .Documents.Add
Set myDoc = .Documents.Open(Filename:="C:\test.doc")

With .Selection
For Each c In Worksheets("sheet1").Range("A1:B5")
.insertafter Text:=c.Value
Count = Count + 1
If Count Mod 2 = 0 Then
.insertafter Text:=vbCr
Else
.insertafter Text:=vbTab
End If
Next c

'.Range.ConvertToTable Separator:=wdSeparateByTabs
'.Tables(1).AutoFormat Format:=wdTableFormatClassic1
End With
'myDoc.SaveAs Filename:="c:\tempdoc.doc"
myDoc.Save
End With

word_app.Quit

End Sub


My question is how do I specify which cell in which table I want the
Excel content to be pasted, i.e. I have multiple tables in the Word
template file.

One way I was thinking is actually turning the Word template file
(.doc) into an actual Word Template file (.dot). Maybe there is a way I
can walk through all the placeholders in the .dot file and fill in the
excel content that way. But that would require I know how many rows and
columns I need ahead of time, which this information may change
dynamically.

Anyone else has a better idea? I really appreciate the help and I hope
this is not a hard problem to solve.

Thanks,
Michelle

 

[Helmut Weber]

Hi Michelle,

you don't have to copy anything.
Just the principle:

Sub Test444()
'reference to word library
Dim oWrd As Word.Application
Dim oDoc As Word.Document
Set oWrd = CreateObject("word.application")
Set oDoc = oWrd.Documents.Open("c:\test\testdoc.doc")
' oWrd.Visible = False
oDoc.Tables(2).Cell(3, 3).Range.Text = _
ActiveWorkbook.Worksheets(1).Cells(3, 3).Value
oDoc.Save
oDoc.Close
Set oDoc = Nothing
oWrd.Quit
Set oWrd = Nothing
End Sub

Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[sleepymish]

Thanks Helmut! I tried your code and it does exactly what I need. I
appreciate your help.