Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
Any links or tips to do this most appreciated. The data would currently come
from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
Eric wrote:
Any links or tips to do this most appreciated. The data would currently come from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric The essential idea for retrieving recordsets (rows) from Excel is shown in http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm. In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the worksheet and the first name is in column B): With ActiveDocument.FormFields .Item("LastName").Result = rs.Fields(0).Value .Item("FirstName").Result = rs.Fields(1).Value End With Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has only one row. If you get stuck, post back. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
#3
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
Hi Jay
The code will be in an Excel wbk (or addin) that the user will have open, so I'm not worried about excel not being there. I don't have too much data to transfer either, so I'm not concerned with speed right now. See below for technique I would typically use to transfer data around. The part I'm more concerned about is the mechanics of populating the Word table, and am thinking it should go something like this 1) create a preformatted table in my protected Word do, with only a header 2) for each row of data 2.1) add a new row to the table (not sure how to do this) 2.2) paste the row into the table (not sure how to do this) 2.3) apply any needed formatting, i.e. currency $ and commas (not sure how to do this) Here's how I deal with moving Excel data around 1) create a User Defined Type to act as a data container for each row 2) create the table in code using three Named Ranges ("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn") 3) for each rngRow in myTable.Rows with udtMyData .FirstName = rngRow.Cells(1, 1) .FirstName = rngRow.Cells(1, 2) end with Any critique, pointers much appreciated. I like your DAO idea, but what I'd really like to do eventually is use a 2008 VSTO based solution, either with ADO, XML or LINQ. This would facilitate testing, reuse, and security for both myself and the user. Unfortunately I haven't the slightest idea how to do that now :-) Thanks, Eirc "Jay Freedman" wrote in message ... Eric wrote: Any links or tips to do this most appreciated. The data would currently come from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric The essential idea for retrieving recordsets (rows) from Excel is shown in http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm. In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the worksheet and the first name is in column B): With ActiveDocument.FormFields .Item("LastName").Result = rs.Fields(0).Value .Item("FirstName").Result = rs.Fields(1).Value End With Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has only one row. If you get stuck, post back. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
#4
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
I can't tell you anything about your Excel code, as I've done very
little of that. But if it gets your worksheet built the way you want, that's fine. I haven't dug into VSTO yet -- that's on my list of things to do (somebody send me a 'tuit'!). And if you plan to use XML and/or LINQ, be sure all your users will be on Office 2007; by comparison, the Office 2003 implementation is clunky. Here are some points about dealing with the Word document: 1. You can't add rows to the table while the document is protected. The code must start by unprotecting the document. Then it can add rows, add content such as form fields to the cells, and do any formatting. At the end, it reprotects the document. 2. In the VB editor, click Tools References and put a checkmark on the Word Object Library. That will give you IntelliSense for objects in the Word model and allow type checking. 3. It's sometimes necessary, and always a good idea, to qualify all type names with the application that's intended. For example, both Word and Excel have Range objects, so you must qualify anything you declare as a Range. Even when there's no ambiguity, it helps you when you go back to do maintenance to know which application a particular variable refers to. 4. If you're driving Word from an Excel macro, then I think neither ADO nor DAO is appropriate. That would be for pulling data from Excel (the server app) with a Word-based macro (the client). However, I'm far from an expert on that. I whomped up a little demonstration for you. I created a worksheet with data in three columns (LastName, FirstName, and Salary) and about a dozen rows, and set a named range ("Presidents") to cover all but the title row. I also created a Word template named "Names.dotx" (this is in Word 2007) that contains a table with only a header row of three columns, and protected it for forms although it doesn't contain any form fields. This Excel macro creates a new document in Word from the template and then builds the table from the Excel data. Sub demo() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdTbl As Word.Table Dim wdRow As Word.Row Dim xlRange As Excel.Range Dim xlRow As Excel.Range On Error Resume Next ' try to get Word if it's open already Set wdApp = GetObject(, "Word.Application") If Err.Number 0 Then Err.Clear On Error Resume Next Set wdApp = CreateObject("Word.Application") End If If Not wdApp Is Nothing Then wdApp.Visible = True Set wdDoc = wdApp.Documents.Add( _ Template:="Names.dotx") ' == change Else MsgBox "Could not create a Word document" Exit Sub End If If wdDoc.ProtectionType wdNoProtection Then wdDoc.Unprotect ' optional password End If ' the template has a table (heading row only) ' so you know there is a .Tables(1) Set wdTbl = wdDoc.Tables(1) For Each xlRow In ActiveWorkbook.Names _ ("Presidents").RefersToRange.Rows ' == change Set wdRow = wdTbl.Rows.Add wdDoc.FormFields.Add Range:=wdRow.Cells(1).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(1).Range.FormFields(1).Result = _ xlRow.Cells(1, 1).Value wdDoc.FormFields.Add Range:=wdRow.Cells(2).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(2).Range.FormFields(1).Result = _ xlRow.Cells(1, 2).Value wdDoc.FormFields.Add Range:=wdRow.Cells(3).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(3).Range.FormFields(1).TextInput.EditT ype _ Type:=wdNumberText, Format:="$#,##0.00" wdRow.Cells(3).Range.FormFields(1).Result = _ xlRow.Cells(1, 3).Value Next wdDoc.Protect Type:=wdAllowOnlyFormFields, NoReset:=True wdDoc.Save End Sub This demo is a bit clumsy; it could use some cleanup and more error checking, but it should point you in the right direction for a VBA-only solution. On Sat, 22 Dec 2007 09:00:06 -0800, "Eric" wrote: Hi Jay The code will be in an Excel wbk (or addin) that the user will have open, so I'm not worried about excel not being there. I don't have too much data to transfer either, so I'm not concerned with speed right now. See below for technique I would typically use to transfer data around. The part I'm more concerned about is the mechanics of populating the Word table, and am thinking it should go something like this 1) create a preformatted table in my protected Word do, with only a header 2) for each row of data 2.1) add a new row to the table (not sure how to do this) 2.2) paste the row into the table (not sure how to do this) 2.3) apply any needed formatting, i.e. currency $ and commas (not sure how to do this) Here's how I deal with moving Excel data around 1) create a User Defined Type to act as a data container for each row 2) create the table in code using three Named Ranges ("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn") 3) for each rngRow in myTable.Rows with udtMyData .FirstName = rngRow.Cells(1, 1) .FirstName = rngRow.Cells(1, 2) end with Any critique, pointers much appreciated. I like your DAO idea, but what I'd really like to do eventually is use a 2008 VSTO based solution, either with ADO, XML or LINQ. This would facilitate testing, reuse, and security for both myself and the user. Unfortunately I haven't the slightest idea how to do that now :-) Thanks, Eirc "Jay Freedman" wrote in message ... Eric wrote: Any links or tips to do this most appreciated. The data would currently come from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric The essential idea for retrieving recordsets (rows) from Excel is shown in http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm. In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the worksheet and the first name is in column B): With ActiveDocument.FormFields .Item("LastName").Result = rs.Fields(0).Value .Item("FirstName").Result = rs.Fields(1).Value End With Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has only one row. If you get stuck, post back. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
#5
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
Hi Eric,
Another thing you'll have to deal with on the Word side, is unprotecting the document before populating the table, then reprotecting the document again afterwards. Either that or you'll have to put the table in an unprotected Section of the document. That's because the forms protection won't allow you to edit the protected portion of the document. Unprotecting & reprotecting the document is fairly straightforward. Since you're working with a document protected for forms, and you apparently want to import some data from an Excel workbook, have you thought of using a LINK field and just updating the field's source range to suit the user's input in a given formfield? Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Eric" wrote in message ... Hi Jay The code will be in an Excel wbk (or addin) that the user will have open, so I'm not worried about excel not being there. I don't have too much data to transfer either, so I'm not concerned with speed right now. See below for technique I would typically use to transfer data around. The part I'm more concerned about is the mechanics of populating the Word table, and am thinking it should go something like this 1) create a preformatted table in my protected Word do, with only a header 2) for each row of data 2.1) add a new row to the table (not sure how to do this) 2.2) paste the row into the table (not sure how to do this) 2.3) apply any needed formatting, i.e. currency $ and commas (not sure how to do this) Here's how I deal with moving Excel data around 1) create a User Defined Type to act as a data container for each row 2) create the table in code using three Named Ranges ("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn") 3) for each rngRow in myTable.Rows with udtMyData .FirstName = rngRow.Cells(1, 1) .FirstName = rngRow.Cells(1, 2) end with Any critique, pointers much appreciated. I like your DAO idea, but what I'd really like to do eventually is use a 2008 VSTO based solution, either with ADO, XML or LINQ. This would facilitate testing, reuse, and security for both myself and the user. Unfortunately I haven't the slightest idea how to do that now :-) Thanks, Eirc "Jay Freedman" wrote in message ... Eric wrote: Any links or tips to do this most appreciated. The data would currently come from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric The essential idea for retrieving recordsets (rows) from Excel is shown in http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm. In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the worksheet and the first name is in column B): With ActiveDocument.FormFields .Item("LastName").Result = rs.Fields(0).Value .Item("FirstName").Result = rs.Fields(1).Value End With Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has only one row. If you get stuck, post back. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
#6
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
You saved me a LOT of time and aggravation. I'm down to one question: I have
(1) form field that needs to get a text value that's 308 characters, and that gives formfield.Result a 'string too long error'. Any ideas? Thanks again! Eric "Jay Freedman" wrote in message ... I can't tell you anything about your Excel code, as I've done very little of that. But if it gets your worksheet built the way you want, that's fine. I haven't dug into VSTO yet -- that's on my list of things to do (somebody send me a 'tuit'!). And if you plan to use XML and/or LINQ, be sure all your users will be on Office 2007; by comparison, the Office 2003 implementation is clunky. Here are some points about dealing with the Word document: 1. You can't add rows to the table while the document is protected. The code must start by unprotecting the document. Then it can add rows, add content such as form fields to the cells, and do any formatting. At the end, it reprotects the document. 2. In the VB editor, click Tools References and put a checkmark on the Word Object Library. That will give you IntelliSense for objects in the Word model and allow type checking. 3. It's sometimes necessary, and always a good idea, to qualify all type names with the application that's intended. For example, both Word and Excel have Range objects, so you must qualify anything you declare as a Range. Even when there's no ambiguity, it helps you when you go back to do maintenance to know which application a particular variable refers to. 4. If you're driving Word from an Excel macro, then I think neither ADO nor DAO is appropriate. That would be for pulling data from Excel (the server app) with a Word-based macro (the client). However, I'm far from an expert on that. I whomped up a little demonstration for you. I created a worksheet with data in three columns (LastName, FirstName, and Salary) and about a dozen rows, and set a named range ("Presidents") to cover all but the title row. I also created a Word template named "Names.dotx" (this is in Word 2007) that contains a table with only a header row of three columns, and protected it for forms although it doesn't contain any form fields. This Excel macro creates a new document in Word from the template and then builds the table from the Excel data. Sub demo() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdTbl As Word.Table Dim wdRow As Word.Row Dim xlRange As Excel.Range Dim xlRow As Excel.Range On Error Resume Next ' try to get Word if it's open already Set wdApp = GetObject(, "Word.Application") If Err.Number 0 Then Err.Clear On Error Resume Next Set wdApp = CreateObject("Word.Application") End If If Not wdApp Is Nothing Then wdApp.Visible = True Set wdDoc = wdApp.Documents.Add( _ Template:="Names.dotx") ' == change Else MsgBox "Could not create a Word document" Exit Sub End If If wdDoc.ProtectionType wdNoProtection Then wdDoc.Unprotect ' optional password End If ' the template has a table (heading row only) ' so you know there is a .Tables(1) Set wdTbl = wdDoc.Tables(1) For Each xlRow In ActiveWorkbook.Names _ ("Presidents").RefersToRange.Rows ' == change Set wdRow = wdTbl.Rows.Add wdDoc.FormFields.Add Range:=wdRow.Cells(1).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(1).Range.FormFields(1).Result = _ xlRow.Cells(1, 1).Value wdDoc.FormFields.Add Range:=wdRow.Cells(2).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(2).Range.FormFields(1).Result = _ xlRow.Cells(1, 2).Value wdDoc.FormFields.Add Range:=wdRow.Cells(3).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(3).Range.FormFields(1).TextInput.EditT ype _ Type:=wdNumberText, Format:="$#,##0.00" wdRow.Cells(3).Range.FormFields(1).Result = _ xlRow.Cells(1, 3).Value Next wdDoc.Protect Type:=wdAllowOnlyFormFields, NoReset:=True wdDoc.Save End Sub This demo is a bit clumsy; it could use some cleanup and more error checking, but it should point you in the right direction for a VBA-only solution. On Sat, 22 Dec 2007 09:00:06 -0800, "Eric" wrote: Hi Jay The code will be in an Excel wbk (or addin) that the user will have open, so I'm not worried about excel not being there. I don't have too much data to transfer either, so I'm not concerned with speed right now. See below for technique I would typically use to transfer data around. The part I'm more concerned about is the mechanics of populating the Word table, and am thinking it should go something like this 1) create a preformatted table in my protected Word do, with only a header 2) for each row of data 2.1) add a new row to the table (not sure how to do this) 2.2) paste the row into the table (not sure how to do this) 2.3) apply any needed formatting, i.e. currency $ and commas (not sure how to do this) Here's how I deal with moving Excel data around 1) create a User Defined Type to act as a data container for each row 2) create the table in code using three Named Ranges ("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn") 3) for each rngRow in myTable.Rows with udtMyData .FirstName = rngRow.Cells(1, 1) .FirstName = rngRow.Cells(1, 2) end with Any critique, pointers much appreciated. I like your DAO idea, but what I'd really like to do eventually is use a 2008 VSTO based solution, either with ADO, XML or LINQ. This would facilitate testing, reuse, and security for both myself and the user. Unfortunately I haven't the slightest idea how to do that now :-) Thanks, Eirc "Jay Freedman" wrote in message .. . Eric wrote: Any links or tips to do this most appreciated. The data would currently come from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric The essential idea for retrieving recordsets (rows) from Excel is shown in http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm. In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the worksheet and the first name is in column B): With ActiveDocument.FormFields .Item("LastName").Result = rs.Fields(0).Value .Item("FirstName").Result = rs.Fields(1).Value End With Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has only one row. If you get stuck, post back. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
#7
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
How does a LINK field work? What's the disadvantage of using it?
Thanks, Eric "macropod" wrote in message ... Hi Eric, Another thing you'll have to deal with on the Word side, is unprotecting the document before populating the table, then reprotecting the document again afterwards. Either that or you'll have to put the table in an unprotected Section of the document. That's because the forms protection won't allow you to edit the protected portion of the document. Unprotecting & reprotecting the document is fairly straightforward. Since you're working with a document protected for forms, and you apparently want to import some data from an Excel workbook, have you thought of using a LINK field and just updating the field's source range to suit the user's input in a given formfield? Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Eric" wrote in message ... Hi Jay The code will be in an Excel wbk (or addin) that the user will have open, so I'm not worried about excel not being there. I don't have too much data to transfer either, so I'm not concerned with speed right now. See below for technique I would typically use to transfer data around. The part I'm more concerned about is the mechanics of populating the Word table, and am thinking it should go something like this 1) create a preformatted table in my protected Word do, with only a header 2) for each row of data 2.1) add a new row to the table (not sure how to do this) 2.2) paste the row into the table (not sure how to do this) 2.3) apply any needed formatting, i.e. currency $ and commas (not sure how to do this) Here's how I deal with moving Excel data around 1) create a User Defined Type to act as a data container for each row 2) create the table in code using three Named Ranges ("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn") 3) for each rngRow in myTable.Rows with udtMyData .FirstName = rngRow.Cells(1, 1) .FirstName = rngRow.Cells(1, 2) end with Any critique, pointers much appreciated. I like your DAO idea, but what I'd really like to do eventually is use a 2008 VSTO based solution, either with ADO, XML or LINQ. This would facilitate testing, reuse, and security for both myself and the user. Unfortunately I haven't the slightest idea how to do that now :-) Thanks, Eirc "Jay Freedman" wrote in message ... Eric wrote: Any links or tips to do this most appreciated. The data would currently come from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric The essential idea for retrieving recordsets (rows) from Excel is shown in http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm. In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the worksheet and the first name is in column B): With ActiveDocument.FormFields .Item("LastName").Result = rs.Fields(0).Value .Item("FirstName").Result = rs.Fields(1).Value End With Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has only one row. If you get stuck, post back. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
#8
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
Hi Eric,
For a general discussion on how link fields work, see Word's help file. The main (dis)advantage is that the document will be linked to the source document. So, if the data in the source file changes, so does the Word document. You can overcome that by locking or unlinking the field when the form is saved. Conversely, if you're using named ranges in Excel, then pointing the link to the appropriate range name permits the embedded object in Word to expand/contract accordingly, with no additional code. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Eric" wrote in message ... How does a LINK field work? What's the disadvantage of using it? Thanks, Eric "macropod" wrote in message ... Hi Eric, Another thing you'll have to deal with on the Word side, is unprotecting the document before populating the table, then reprotecting the document again afterwards. Either that or you'll have to put the table in an unprotected Section of the document. That's because the forms protection won't allow you to edit the protected portion of the document. Unprotecting & reprotecting the document is fairly straightforward. Since you're working with a document protected for forms, and you apparently want to import some data from an Excel workbook, have you thought of using a LINK field and just updating the field's source range to suit the user's input in a given formfield? Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Eric" wrote in message ... Hi Jay The code will be in an Excel wbk (or addin) that the user will have open, so I'm not worried about excel not being there. I don't have too much data to transfer either, so I'm not concerned with speed right now. See below for technique I would typically use to transfer data around. The part I'm more concerned about is the mechanics of populating the Word table, and am thinking it should go something like this 1) create a preformatted table in my protected Word do, with only a header 2) for each row of data 2.1) add a new row to the table (not sure how to do this) 2.2) paste the row into the table (not sure how to do this) 2.3) apply any needed formatting, i.e. currency $ and commas (not sure how to do this) Here's how I deal with moving Excel data around 1) create a User Defined Type to act as a data container for each row 2) create the table in code using three Named Ranges ("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn") 3) for each rngRow in myTable.Rows with udtMyData .FirstName = rngRow.Cells(1, 1) .FirstName = rngRow.Cells(1, 2) end with Any critique, pointers much appreciated. I like your DAO idea, but what I'd really like to do eventually is use a 2008 VSTO based solution, either with ADO, XML or LINQ. This would facilitate testing, reuse, and security for both myself and the user. Unfortunately I haven't the slightest idea how to do that now :-) Thanks, Eirc "Jay Freedman" wrote in message ... Eric wrote: Any links or tips to do this most appreciated. The data would currently come from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric The essential idea for retrieving recordsets (rows) from Excel is shown in http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm. In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the worksheet and the first name is in column B): With ActiveDocument.FormFields .Item("LastName").Result = rs.Fields(0).Value .Item("FirstName").Result = rs.Fields(1).Value End With Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has only one row. If you get stuck, post back. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
#9
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
Hi Eric, try something based on:
ActiveDocument.Bookmarks("Name").Range.Fields(1).R esult.Text = "My really long text string" Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Eric" wrote in message ... You saved me a LOT of time and aggravation. I'm down to one question: I have (1) form field that needs to get a text value that's 308 characters, and that gives formfield.Result a 'string too long error'. Any ideas? Thanks again! Eric "Jay Freedman" wrote in message ... I can't tell you anything about your Excel code, as I've done very little of that. But if it gets your worksheet built the way you want, that's fine. I haven't dug into VSTO yet -- that's on my list of things to do (somebody send me a 'tuit'!). And if you plan to use XML and/or LINQ, be sure all your users will be on Office 2007; by comparison, the Office 2003 implementation is clunky. Here are some points about dealing with the Word document: 1. You can't add rows to the table while the document is protected. The code must start by unprotecting the document. Then it can add rows, add content such as form fields to the cells, and do any formatting. At the end, it reprotects the document. 2. In the VB editor, click Tools References and put a checkmark on the Word Object Library. That will give you IntelliSense for objects in the Word model and allow type checking. 3. It's sometimes necessary, and always a good idea, to qualify all type names with the application that's intended. For example, both Word and Excel have Range objects, so you must qualify anything you declare as a Range. Even when there's no ambiguity, it helps you when you go back to do maintenance to know which application a particular variable refers to. 4. If you're driving Word from an Excel macro, then I think neither ADO nor DAO is appropriate. That would be for pulling data from Excel (the server app) with a Word-based macro (the client). However, I'm far from an expert on that. I whomped up a little demonstration for you. I created a worksheet with data in three columns (LastName, FirstName, and Salary) and about a dozen rows, and set a named range ("Presidents") to cover all but the title row. I also created a Word template named "Names.dotx" (this is in Word 2007) that contains a table with only a header row of three columns, and protected it for forms although it doesn't contain any form fields. This Excel macro creates a new document in Word from the template and then builds the table from the Excel data. Sub demo() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdTbl As Word.Table Dim wdRow As Word.Row Dim xlRange As Excel.Range Dim xlRow As Excel.Range On Error Resume Next ' try to get Word if it's open already Set wdApp = GetObject(, "Word.Application") If Err.Number 0 Then Err.Clear On Error Resume Next Set wdApp = CreateObject("Word.Application") End If If Not wdApp Is Nothing Then wdApp.Visible = True Set wdDoc = wdApp.Documents.Add( _ Template:="Names.dotx") ' == change Else MsgBox "Could not create a Word document" Exit Sub End If If wdDoc.ProtectionType wdNoProtection Then wdDoc.Unprotect ' optional password End If ' the template has a table (heading row only) ' so you know there is a .Tables(1) Set wdTbl = wdDoc.Tables(1) For Each xlRow In ActiveWorkbook.Names _ ("Presidents").RefersToRange.Rows ' == change Set wdRow = wdTbl.Rows.Add wdDoc.FormFields.Add Range:=wdRow.Cells(1).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(1).Range.FormFields(1).Result = _ xlRow.Cells(1, 1).Value wdDoc.FormFields.Add Range:=wdRow.Cells(2).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(2).Range.FormFields(1).Result = _ xlRow.Cells(1, 2).Value wdDoc.FormFields.Add Range:=wdRow.Cells(3).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(3).Range.FormFields(1).TextInput.EditT ype _ Type:=wdNumberText, Format:="$#,##0.00" wdRow.Cells(3).Range.FormFields(1).Result = _ xlRow.Cells(1, 3).Value Next wdDoc.Protect Type:=wdAllowOnlyFormFields, NoReset:=True wdDoc.Save End Sub This demo is a bit clumsy; it could use some cleanup and more error checking, but it should point you in the right direction for a VBA-only solution. On Sat, 22 Dec 2007 09:00:06 -0800, "Eric" wrote: Hi Jay The code will be in an Excel wbk (or addin) that the user will have open, so I'm not worried about excel not being there. I don't have too much data to transfer either, so I'm not concerned with speed right now. See below for technique I would typically use to transfer data around. The part I'm more concerned about is the mechanics of populating the Word table, and am thinking it should go something like this 1) create a preformatted table in my protected Word do, with only a header 2) for each row of data 2.1) add a new row to the table (not sure how to do this) 2.2) paste the row into the table (not sure how to do this) 2.3) apply any needed formatting, i.e. currency $ and commas (not sure how to do this) Here's how I deal with moving Excel data around 1) create a User Defined Type to act as a data container for each row 2) create the table in code using three Named Ranges ("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn") 3) for each rngRow in myTable.Rows with udtMyData .FirstName = rngRow.Cells(1, 1) .FirstName = rngRow.Cells(1, 2) end with Any critique, pointers much appreciated. I like your DAO idea, but what I'd really like to do eventually is use a 2008 VSTO based solution, either with ADO, XML or LINQ. This would facilitate testing, reuse, and security for both myself and the user. Unfortunately I haven't the slightest idea how to do that now :-) Thanks, Eirc "Jay Freedman" wrote in message . .. Eric wrote: Any links or tips to do this most appreciated. The data would currently come from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric The essential idea for retrieving recordsets (rows) from Excel is shown in http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm. In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the worksheet and the first name is in column B): With ActiveDocument.FormFields .Item("LastName").Result = rs.Fields(0).Value .Item("FirstName").Result = rs.Fields(1).Value End With Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has only one row. If you get stuck, post back. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
#10
Posted to microsoft.public.word.tables
|
|||
|
|||
populate a word table programatically
Also, the form has to be unprotected when you do that, the same as for
adding table rows. Details in http://www.word.mvps.org/FAQs/Macros...mFldResult.htm. On Mon, 24 Dec 2007 19:13:59 +1100, "macropod" wrote: Hi Eric, try something based on: ActiveDocument.Bookmarks("Name").Range.Fields(1). Result.Text = "My really long text string" Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Eric" wrote in message ... You saved me a LOT of time and aggravation. I'm down to one question: I have (1) form field that needs to get a text value that's 308 characters, and that gives formfield.Result a 'string too long error'. Any ideas? Thanks again! Eric "Jay Freedman" wrote in message ... I can't tell you anything about your Excel code, as I've done very little of that. But if it gets your worksheet built the way you want, that's fine. I haven't dug into VSTO yet -- that's on my list of things to do (somebody send me a 'tuit'!). And if you plan to use XML and/or LINQ, be sure all your users will be on Office 2007; by comparison, the Office 2003 implementation is clunky. Here are some points about dealing with the Word document: 1. You can't add rows to the table while the document is protected. The code must start by unprotecting the document. Then it can add rows, add content such as form fields to the cells, and do any formatting. At the end, it reprotects the document. 2. In the VB editor, click Tools References and put a checkmark on the Word Object Library. That will give you IntelliSense for objects in the Word model and allow type checking. 3. It's sometimes necessary, and always a good idea, to qualify all type names with the application that's intended. For example, both Word and Excel have Range objects, so you must qualify anything you declare as a Range. Even when there's no ambiguity, it helps you when you go back to do maintenance to know which application a particular variable refers to. 4. If you're driving Word from an Excel macro, then I think neither ADO nor DAO is appropriate. That would be for pulling data from Excel (the server app) with a Word-based macro (the client). However, I'm far from an expert on that. I whomped up a little demonstration for you. I created a worksheet with data in three columns (LastName, FirstName, and Salary) and about a dozen rows, and set a named range ("Presidents") to cover all but the title row. I also created a Word template named "Names.dotx" (this is in Word 2007) that contains a table with only a header row of three columns, and protected it for forms although it doesn't contain any form fields. This Excel macro creates a new document in Word from the template and then builds the table from the Excel data. Sub demo() Dim wdApp As Word.Application Dim wdDoc As Word.Document Dim wdTbl As Word.Table Dim wdRow As Word.Row Dim xlRange As Excel.Range Dim xlRow As Excel.Range On Error Resume Next ' try to get Word if it's open already Set wdApp = GetObject(, "Word.Application") If Err.Number 0 Then Err.Clear On Error Resume Next Set wdApp = CreateObject("Word.Application") End If If Not wdApp Is Nothing Then wdApp.Visible = True Set wdDoc = wdApp.Documents.Add( _ Template:="Names.dotx") ' == change Else MsgBox "Could not create a Word document" Exit Sub End If If wdDoc.ProtectionType wdNoProtection Then wdDoc.Unprotect ' optional password End If ' the template has a table (heading row only) ' so you know there is a .Tables(1) Set wdTbl = wdDoc.Tables(1) For Each xlRow In ActiveWorkbook.Names _ ("Presidents").RefersToRange.Rows ' == change Set wdRow = wdTbl.Rows.Add wdDoc.FormFields.Add Range:=wdRow.Cells(1).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(1).Range.FormFields(1).Result = _ xlRow.Cells(1, 1).Value wdDoc.FormFields.Add Range:=wdRow.Cells(2).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(2).Range.FormFields(1).Result = _ xlRow.Cells(1, 2).Value wdDoc.FormFields.Add Range:=wdRow.Cells(3).Range, _ Type:=wdFieldFormTextInput wdRow.Cells(3).Range.FormFields(1).TextInput.EditT ype _ Type:=wdNumberText, Format:="$#,##0.00" wdRow.Cells(3).Range.FormFields(1).Result = _ xlRow.Cells(1, 3).Value Next wdDoc.Protect Type:=wdAllowOnlyFormFields, NoReset:=True wdDoc.Save End Sub This demo is a bit clumsy; it could use some cleanup and more error checking, but it should point you in the right direction for a VBA-only solution. On Sat, 22 Dec 2007 09:00:06 -0800, "Eric" wrote: Hi Jay The code will be in an Excel wbk (or addin) that the user will have open, so I'm not worried about excel not being there. I don't have too much data to transfer either, so I'm not concerned with speed right now. See below for technique I would typically use to transfer data around. The part I'm more concerned about is the mechanics of populating the Word table, and am thinking it should go something like this 1) create a preformatted table in my protected Word do, with only a header 2) for each row of data 2.1) add a new row to the table (not sure how to do this) 2.2) paste the row into the table (not sure how to do this) 2.3) apply any needed formatting, i.e. currency $ and commas (not sure how to do this) Here's how I deal with moving Excel data around 1) create a User Defined Type to act as a data container for each row 2) create the table in code using three Named Ranges ("ptrTableStartCell", "ptrTableEndCell", "ptrLastTableColumn") 3) for each rngRow in myTable.Rows with udtMyData .FirstName = rngRow.Cells(1, 1) .FirstName = rngRow.Cells(1, 2) end with Any critique, pointers much appreciated. I like your DAO idea, but what I'd really like to do eventually is use a 2008 VSTO based solution, either with ADO, XML or LINQ. This would facilitate testing, reuse, and security for both myself and the user. Unfortunately I haven't the slightest idea how to do that now :-) Thanks, Eirc "Jay Freedman" wrote in message .. . Eric wrote: Any links or tips to do this most appreciated. The data would currently come from Excel, and get there by VBA. The word doc is protected with form filling only allowed. v2003 on both. I posted this on office development as well, as I hadn't seen this forum first. If there is a more appropriate forum please let me know. TIA, Eric The essential idea for retrieving recordsets (rows) from Excel is shown in http://www.word.mvps.org/FAQs/InterD...ordWithDAO.htm. In place of the MsgBox statement in that code, you would need something like this (assuming the last name is in column A of the worksheet and the first name is in column B): With ActiveDocument.FormFields .Item("LastName").Result = rs.Fields(0).Value .Item("FirstName").Result = rs.Fields(1).Value End With Of course, iterating the rows with While Not rs.EOF doesn't make much sense for a single Word form unless the worksheet has only one row. If you get stuck, post back. -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Programatically assigning values to table cells | Microsoft Word Help | |||
insert new cell in specific table, then populate with text? | Tables | |||
How do i insert a watermark into a Word Document programatically ( | Microsoft Word Help | |||
Populate Table from Access | Mailmerge | |||
Programatically concatenating Word files | New Users |