Solved: Copying excel cell content into word template table

[Ray.Mason]

Hi Guys!

I have code that is presently looping through column B in excel and updating column 1 bookmarks in table in word document. I have created bookmarks (A1 to A5) in this table each in separate cell. However this falls beyond my requirements as the data from excel is dynamic. Ideally I would like the code to loop through column B in excel then create word table cells under first column heading based on the number of cells with data in column B of excel. Eg if excel SS Cells, B9, B16, B19,B20 have data then 4 cells are created in word under the ID column. The next step would be each cell that has data in excel column B move 0 rows and move to right cells 1, 7, 8, 5 (i.e column C, I,J,G) and create cells in word table and update under column headings Name, Handover date, Release Date and Total Elapsed respectively.

Below are column headings in word template (with grid lines)

ID Name Handover Date Release Date Total Elapsed


Code I have at the moment:
Sub ReportGenerator()
Dim wdApp As Word.Application
Dim rng As Range
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim SaveFolder As String
Dim i As Integer
Dim counter As Integer

'Saving file to specific location

SaveFolder = "C:\Documents and Settings\\Desktop\MyTools\"
Set wdApp = New Word.Application
Set myDoc = wdApp.Documents.Add(Template:="C:\Documents and Settings\Desktop\Test.dotm")
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With

counter = 1
For Each rng In Range("B8:B65336").SpecialCells(2, 2)
If UCase(rng.Text) = "STOP" Then Exit For
rng.Copy



myDoc.Bookmarks.Item(counter).Select
wdApp.Selection.Goto What:=wdGoToBookmark, Name:=myDoc.Bookmarks.Item(counter)
wdApp.Selection.PasteSpecial
counter = counter + 1

Next

End Sub

[Tinbendr]

You can access Word table similarly to Excel. No need to use bookmarks or selection.

Dim Tbl As Word.Table
Set Tbl = mydoc.Tables(1)
......
For Each Rng In Range("B8:B65336").SpecialCells(2, 2)
If UCase(Rng.Text) = "STOP" Then Exit For
Tbl.Rows.Add
Tbl.Cell(Tbl.Rows.Count, 1).Range.Text = Rng.Value
Tbl.Cell(Tbl.Rows.Count, 2).Range.Text = Rng.Offset(0, 6).Value
Tbl.Cell(Tbl.Rows.Count, 3).Range.Text = Rng.Offset(0, 7).Value
Tbl.Cell(Tbl.Rows.Count, 4).Range.Text = Rng.Offset(0, 4).Value
Next

[snb]

sub snb()
with thisworkbook.sheets("transport")
for each cl in .columns(2).specialcells(2,2)
.cells(rows.count,100).end(xlup).offset(1).resize(,5)=array(cl.value,cl.off set(,1).value,cl.offset(,6).value,cl.offset(,7).value,cl.offset(,5).value)
next
.cells(2,100).currentregion.copy
end with

with getobject("C:\Documents and Settings\Desktop\Test.docx")
.paragraphs(1).range.paste
.saveas "C:\Documents and Settings\Desktop\MyTools\transp.docx"
.close 0
end with
end sub

You won't need to use a Word template. A Word document will do.
In this code a create a new range in column(100), that will be filled with the data you want to 'export'. That range can be copied to a Word document and then the table in Word is complete.

[Ray.Mason]

Many thanks Tinbendr and snb for your code, Much appreciated. I will try this code out sometime tomorrow when I get a chance to sit down. Will update you on progress. I'm not as confident with VBA and will probably have further questions :)

[Ray.Mason]

Yay! Tried and it works.Thanks guys. I have used your code Tinbendr and it works perfectly fine. One question, well 2 actually :). My table column headings have grey background color, When the macro runs it's filling cells with same bg color. Can I change this so that the updated cells have white background? Also I have a second table within same word document i.e the macro will update table 1 then table 2.

How can I add to this so the macro reads into second table
Dim Tbl As Word.Table
Set Tbl = mydoc.Tables(1)

[Tinbendr]

Can I change this so that the updated cells have white background? Tbl.Rows(Tbl.Rows.Count).Shading.BackgroundPatternColor = wdColorAutomatic

Also I have a second table within same word document i.e the macro will update table 1 then table 2.

How can I add to this so the macro reads into second table?
Just set the Tbl to the second table and repeat code.
Set Tbl = mydoc.Tables(2)

[Ray.Mason]

Thanks David. It's now working just the way i want it to work.
Thank you very much for your help!

:)