Solved: Copy data from Word Table cells into Fields in a new document

[arcanedevice]

Hi
I've done some searches but can't find what I'm looking for, so hoping someone can assist or point me to a previous posting that matches...

I have about 1500+ Word documents that each have a table containing data that I need to 'copy' into a new template and create an updated version of the document. The data in each of the old documents was manually entered over time (about 8 years) and is not 'marked' in anyway except for rows and columns, while the new template has bookmarks or Fields for the data.

What I was hoping to do was have Word open both documents, and copy the table data from the 'old' document (ie, Row1,Column1) and paste it to the corresponding Field (ie, TxtUnitCode) in the new document.

I have had no problems with getting the two documents to open, but have been stumbling around with getting the 'copy' and 'paste' part to happen. I have tried running a macro to copy and paste but can't even get this to work to give me some guidance. :banghead:

So, is anyone able to assist with how I might do this?

arcanedevice

[fumei]

More details needed. First off, is the data in the "old" files always in the same place? If it is - say, Row1,Column1 - then it will be much easier. Actually, it would be fairly simple.

Next, do the "old" documents only have one table? Again, if this is the case, then it would be really simple.

In other words, IF the old documents have:

one table
the data is always in Row1,Column1

then coding this is pretty easy. But the more detail I ask for is this:

"copy the table data from the 'old' document (ie, Row1,Column1) and paste it to the corresponding Field (ie, TxtUnitCode) in the new document."

OK, does that mean all the data from the 1500 files goes into ONE field? By the way...what kind of field?

[fumei]

Here is something that may help to get you further along.
Sub Whatever()
Dim file
Dim myPath As String
Dim ThisDoc As Document
Dim TempDoc As Document
Dim oFF As FormField

myPath = "c:\yadda\Misc\"
Set ThisDoc = ActiveDocument
Set oFF = ThisDoc.FormFields("txtUnitCode")

file = Dir(myPath & "*.doc")
Do While file <> ""
Set TempDoc = Documents.Open(FileName:=myPath & file)
oFF.Result = oFF.Result & vbCrLf & _
TempDoc.Tables(1).Cell(1, 1).Range.Text
TempDoc.Close wdDoNotSaveChanges
file = Dir
Loop
End Sub
This take all .doc files in c:\yadda\Misc\, opens each one, extracts the contents of Cell(1, 1) of Table 1 - the assumption the IS a table! - and appends that to the contents of a formfield (txtUnitCode).

Proper error trapping would do a check of each document to make sure that there is indeed a table.

[arcanedevice]

Hi Fumei

Thanks for your response(s). I'll have a look at your code in more detail and see how I go, but to answer your questions:

1. Yes, data in the old files is always in the same place. For example, 'Unit Code' will always be in R1C1, and 'Unit Title' will be R1C2

2. Yes, the 'old' files do have only one table with multiple rows and columns, but again, these are all in the same layout of X Rows but only 2 Columns.

3. The 'new' template has Text Form Fields.

4. I think I've understood your last question, but no, the the data from the 'old' document has to go into the 'new' document, but in different text form fields. For example, the contents of R1C1 in the 'old' document will always be the Unit Code, and the Text Form Field in 'new' will always be TxtUnitCode. Likewise, the data R5C2 in the old will always be text relating to 'Employability Skills' while in the new R7C2 will be the Text Form Field for 'TxtEmployabilitySkills.

This last response probably begs the question of why move at all rather than just re-format, but essentially the contents of the 'old' document are missing some updated content, the data is also used for a variety of different means so we need to get it into a new format that can be used more easily depending on our working group(s).

But again, thanks for the code, I'll have a look through and see what I can do with it and let you know!
:)

[macropod]

Hi arcanedevice,

Surely it would be better to manage this data in an Excel workbook or an Access database? Extracting the data to a csv file that can be imported into either app (or used as Word mailmerge data source, for example) is relatively straightforward.

[arcanedevice]

Hi macropod

In principle I agree wholeheartedly however the 'old' documents have been created in Word previously long before I arrived, and need to go into Word again to meet our client needs / desires.
However, rest assured that as part of my project I have already flagged that the 'content' will be stored in Access and can then be drawn down into the template as necessary...

[arcanedevice]

Here is something that may help to get you further along.
Sub Whatever()
Dim file
Dim myPath As String
Dim ThisDoc As Document
Dim TempDoc As Document
Dim oFF As FormField

myPath = "c:\yadda\Misc\"
Set ThisDoc = ActiveDocument
Set oFF = ThisDoc.FormFields("txtUnitCode")

file = Dir(myPath & "*.doc")
Do While file <> ""
Set TempDoc = Documents.Open(FileName:=myPath & file)
oFF.Result = oFF.Result & vbCrLf & _
TempDoc.Tables(1).Cell(1, 1).Range.Text
TempDoc.Close wdDoNotSaveChanges
file = Dir
Loop
End Sub
This take all .doc files in c:\yadda\Misc\, opens each one, extracts the contents of Cell(1, 1) of Table 1 - the assumption the IS a table! - and appends that to the contents of a formfield (txtUnitCode).

Proper error trapping would do a check of each document to make sure that there is indeed a table.

Hi Fumei

Thanks for that - I was able to use the questions you raised to help modify that code slightly and now have it working pretty well with my documents and moving into the 'save and next document' form.

However, I have noticed that in some instances I've been getting a 'string too long' error, particularly if the Cell has a large amount a text. My FormFields are set to unlimited so I would have thought that this error would not occur? Is there something I'm missing?

[fumei]

No, you are not missing anything. Yup, while not a "bug" (at least according to Microsoft - although I certainly consider it a bug) - formfields will NOT accept a string longer that 255 characters via code. Even if the formfield is set as "unlimited".

Did you try searching on this? Type "error 4609" into Google. Microsoft shows a workaround. A rather sloppy one IMO, but it does work. It unfortuately uses Selection.


BTW: I posted very simple code. I would recommend that you make it better by stripping the end-of-cell marker out of the Cell(x,y).Range.Text value.

That is, Cell(x,y).Range.Text always includes the end-of-cell marker, and maybe you do not want that. Most of the time, I do not. Since you a doing a lot of this, use a Function. You can do it using either the range.text as a string (CellText), or the cell as an object (CellText2).
Function CellText(strIn As String) As String
CellText = Left(strIn, Len(strIn) - 2)
End Function

Function CellText2(aCell As Cell) As String
Dim sText As String
sText = aCell.Range.Text
CellText2 = Left(sText, Len(sText) - 2)
End Function

so......
FormfieldObject.Result = CellText(Table.Cell(x,y).Range.Text)

' OR

FormfieldObject.Result = CellText2(Table.Cell(x,y))

[arcanedevice]

No, you are not missing anything. Yup, while not a "bug" (at least according to Microsoft - although I certainly consider it a bug) - formfields will NOT accept a string longer that 255 characters via code. Even if the formfield is set as "unlimited".

Did you try searching on this? Type "error 4609" into Google. Microsoft shows a workaround. A rather sloppy one IMO, but it does work. It unfortuately uses Selection.


BTW: I posted very simple code. I would recommend that you make it better by stripping the end-of-cell marker out of the Cell(x,y).Range.Text value.

That is, Cell(x,y).Range.Text always includes the end-of-cell marker, and maybe you do not want that. Most of the time, I do not. Since you a doing a lot of this, use a Function. You can do it using either the range.text as a string (CellText), or the cell as an object (CellText2).
Function CellText(strIn As String) As String
CellText = Left(strIn, Len(strIn) - 2)
End Function

Function CellText2(aCell As Cell) As String
Dim sText As String
sText = aCell.Range.Text
CellText2 = Left(sText, Len(sText) - 2)
End Function

so......
FormfieldObject.Result = CellText(Table.Cell(x,y).Range.Text)

' OR

FormfieldObject.Result = CellText2(Table.Cell(x,y))



Thanks Fumei

I actually found that by removing the FormField and using a Bookmark it actually copied across the full text anyway, so I've switched them round.

Will have a look at the stripping you've mentioned as well. :)

[fumei]

"I actually found that by removing the FormField and using a Bookmark it actually copied across the full text anyway"

HURRAH!!

BRAVO!!!

Personally, I think that was the way to go anyway. Formfields are, for the most part, for user input. If the user is not inputing anything....ummmm, do not use a formfield.

True, there ARE valid reasons for using formfields for holding text, without any user input. But those are special cases I think.

[arcanedevice]

Hi fumei

Much thanks for all your help with this for a relatively newbie (and to all the other VBAX posters who have helped unknowingly with their questions!).

To give some explanation, the reason I initially used FormFields is because after they've been converted to the new layout, the user will require the ability to change or update the text, so I thought it would be easier as FF tend to 'show' on the document unlike bookmarks.

But I'm happy because now the bookmarks will give me another challenge to test my skills by building some additional forms for the 'update' process.

[lucas]

To give some explanation, the reason I initially used FormFields is because after they've been converted to the new layout, the user will require the ability to change or update the text, so I thought it would be easier as FF tend to 'show' on the document unlike bookmarks.


You can make bookmarks visible in your document, they just won't print. You can make them not visible to send out.

Tools-Options on the view tab check next to bookmarks.

or by code:

Option Explicit
Sub a()
ActiveWindow.View.ShowBookmarks = True
End Sub
Sub b()
ActiveWindow.View.ShowBookmarks = False
End Sub

[fumei]

Be careful here. If you do in fact require user input (changes) to the contents of the bookmarks, then you may need to revisit design. It all depends.

The advantage of formfields is that user input can be restricted to just the formfields. Which MAY be the way you do want to go. It depends on EXACTLY what your requirements are.