help writing loop

[robhargreaves]

Hi I have been working on a problem i have had for a few days now and have come up with an easy solution to the problem.

I need a bit of help with the code but I have some of it worked out.

Here goes -

I have a word document template I am opening with code like this -



code:
--------------------------------------------------------------------------------

Option Explicit
Sub Merge()
Dim WordApp As Object
Dim WordDoc As Object
Dim wb As Excel.Workbook
Dim Path As String

Dim rng As Range
Dim c As Range

Set wb = ActiveWorkbook
'change this line if you dont want the template to be called lotsofpaper. - Only change the filename.
Path = wb.Path & "\lotsofpaper.dot"

'set word
Set WordApp = CreateObject("Word.Application")

'open work template
Set WordDoc = WordApp.Documents.Add(Path)

' code to copy will go here

'display the word doc
With WordApp
.Visible = True
.ActiveWindow.WindowState = 0
.Activate
End With

Set WordApp = Nothing

End Sub

--------------------------------------------------------------------------------




My word document is structured in such a way to recieve all the information and all insertion points have the same line and column numbers in common just the page numbers differ.

So the word doc is waiting to recieve values from the workbook active sheet

Example -

for each row with a value in column a in active worksheet in excel take row number and output to matching page number in word.

So row 304 would have the values in the ranges found in page 304 of the doc template.

Each row of data in excel contains the ranges in columns to populate each page. They are as follows -

Column B = Ln(1) Col(18)
Column C = Ln(2) Col(13)
Column E = Ln(3) Col(19)
Column I = Ln(4) Col(23)
Column K = Ln(5) Col(24)

I hope I have explained it well enough if someone can explain to me how to write the loop and tell excel to copy values from row to pages I will be really greatful I cant work out another way to set it up.

Thanks

Rob

[Henry]

Rob,

In the places that you want to put data into your Word Doc, put formfields
with unique bookmarks.

WordApp.ActiveDocument.FormFields("Bookmark1").Select
WordApp.ActiveDocument.FormFields("Bookmark1").Result = Range("B1").Value

I've used bookmark1 as the name, but it would be sensible to use more
meaningful names for your bookmarks.

Before you set WordApp to Nothing you will need to put the line
WordApp.Quit


Henry



"robhargreaves" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi I have been working on a problem i have had for a few days now and
> have come up with an easy solution to the problem.
>
> I need a bit of help with the code but I have some of it worked out.
>
> Here goes -
>
> I have a word document template I am opening with code like this -
>
>
>
> code:
> --------------------------------------------------------------------------------
>
> Option Explicit
> Sub Merge()
> Dim WordApp As Object
> Dim WordDoc As Object
> Dim wb As Excel.Workbook
> Dim Path As String
>
> Dim rng As Range
> Dim c As Range
>
> Set wb = ActiveWorkbook
> 'change this line if you dont want the template to be called
> lotsofpaper. - Only change the filename.
> Path = wb.Path & "\lotsofpaper.dot"
>
> 'set word
> Set WordApp = CreateObject("Word.Application")
>
> 'open work template
> Set WordDoc = WordApp.Documents.Add(Path)
>
> ' code to copy will go here
>
> 'display the word doc
> With WordApp
> .Visible = True
> .ActiveWindow.WindowState = 0
> .Activate
> End With
>
> Set WordApp = Nothing
>
> End Sub
>
> --------------------------------------------------------------------------------
>
>
>
>
> My word document is structured in such a way to recieve all the
> information and all insertion points have the same line and column
> numbers in common just the page numbers differ.
>
> So the word doc is waiting to recieve values from the workbook active
> sheet
>
> Example -
>
> for each row with a value in column a in active worksheet in excel take
> row number and output to matching page number in word.
>
> So row 304 would have the values in the ranges found in page 304 of the
> doc template.
>
> Each row of data in excel contains the ranges in columns to populate
> each page. They are as follows -
>
> Column B = Ln(1) Col(18)
> Column C = Ln(2) Col(13)
> Column E = Ln(3) Col(19)
> Column I = Ln(4) Col(23)
> Column K = Ln(5) Col(24)
>
> I hope I have explained it well enough if someone can explain to me how
> to write the loop and tell excel to copy values from row to pages I will
> be really greatful I cant work out another way to set it up.
>
> Thanks
>
> Rob
>
>
> --
> robhargreaves
> ------------------------------------------------------------------------
> robhargreaves's Profile:
> http://www.excelforum.com/member.php...o&userid=25519
> View this thread: http://www.excelforum.com/showthread...hreadid=391627
>