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