Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help writing loop
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help writing loop
Rob,
In the places that you want to put data into your Word Doc, put formfields with unique bookmarks. WordApp.ActiveDocument.FormFields("Bookmark1").Sel ect WordApp.ActiveDocument.FormFields("Bookmark1").Res ult = 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" wrote in message news:robhargreaves.1t1gpn_1122821127.2837@excelfor um-nospam.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop Function unable to loop | Excel Programming | |||
Problem adding charts using Do-Loop Until loop | Excel Programming | |||
How do I create a For loop within a For loop? | Excel Programming | |||
For/Loop skipping one value in loop only | Excel Programming | |||
HELP!!!! Can't stop a loop (NOT an infinite loop) | Excel Programming |