Ray.Mason
06-23-2012, 07:49 AM
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
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