Copy table content between documents

[mcb09]

I'm new to VBA, and I'd like to know if anyone has code for copying the content of a table from one document to a table in another document. Cells in the two documents are similar but formatted exactly the same. Right now, I am cut and pasting each cell's content, and its taking way too long. Thanks!

Basically I'm trying to move the cell content of Document A to Document B.

Alternatively, if you have a way to reformat a entire table Doc A to match Doc B, that would work too. Thanks!

[macropod]

There is a problem with the content in your links - all it displays is some html code. Even once that's re-processed, the content appears to be the same for both files.

Rather than providing links to other sites, you could attach your documents to a post here. You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.

[mcb09]

Thank you. Here's the files.

[macropod]

Unless there is always a fixed relationship between the tables in the two documents, this would be a difficult task.

Although you say "Cells in the two documents are similar but formatted exactly the same", that's not really the case. For example, whereas your B document has a separate cell for headings such as 'LEARNING ACTIVITIES', your A document has those headings in the same cells as the content to which they relate. Consequently, even if there is a fixed relationship between the documents, one can't simply transfer entire cell contents - some editing would also be required.

If there isn't a fixed relationship, the difficulties associated with automatically transferring the data increase substantially.

[mcb09]

It really wouldn't be a problem to repeat those cells headings if it meant a more expedient method of turning Doc A into Doc B. The manual cutting and pasting that I'm doing is going to take longer than I have to complete this project.

[macropod]

Originally Posted by mcb09

It really wouldn't be a problem to repeat those cells headings if it meant a more expedient method of turning Doc A into Doc B.

That is much less of an issue than being able to determine which content in Doc A goes into which cells in Doc B.

You also haven't said whether there is always a fixed relationship between the tables in the two documents (eg row 5, column 3 of table 2 in Doc A always does into Row 7 Column 1 of table 1 in Doc B). If there isn't a fixed relationship of this kind, there will be significant difficulties in transferring the data.

From what I've seen, reformatting Doc A wouldn't be any easier.

[mcb09]

The fixed relationship between the docs only relates to certain cells. Here's the mapping.


ROW 4, COLUMN 1 of TABLE 2 in Doc A → ROW 10, COLUMN 1 of TABLE 1 in Doc B
ROW 4, COLUMN 2 of TABLE 2 in Doc A → ROW 10, COLUMN 2 of TABLE 1 in Doc B
ROW 5, COLUMN 1 of TABLE 2 in Doc A → ROW 12, COLUMN 1 of TABLE 1 in Doc B
ROW 5, COLUMN 2 of TABLE 2 in Doc A → ROW 12, COLUMN 2 of TABLE 1 in Doc B
ROW 6, COLUMN 1 of TABLE 2 in Doc A → ROW 14, COLUMN 1 of TABLE 1 in Doc B
ROW 6, COLUMN 2 of TABLE 2 in Doc A → ROW 14, COLUMN 2 of TABLE 1 in Doc B
ROW 8, COLUMN 1 of TABLE 2 in Doc A → ROW 17, COLUMN 1 of TABLE 1 in Doc B
ROW 8, COLUMN 2 of TABLE 2 in Doc A → ROW 17, COLUMN 2 of TABLE 1 in Doc B
ROW 10, COLUMN 1 of TABLE 2 in Doc A → ROW 20, COLUMN 1 of TABLE 1 in Doc B
ROW 10, COLUMN 2 of TABLE 2 in Doc A → ROW 20, COLUMN 2 of TABLE 1 in Doc B
ROW 11, COLUMN 1 of TABLE 2 in Doc A → ROW 22, COLUMN 1 of TABLE 1 in Doc B
ROW 11, COLUMN 2 of TABLE 2 in Doc A → ROW 22, COLUMN 2 of TABLE 1 in Doc B

[macropod]

Try the following macro. Add it to your destination document (or to Word's 'Normal' template and activate your destination document), then run it. Select the source document and the destination document will be updated.

Note: I had to change your source row references, plus some destination column references, and you seemed to have too many cells overall. I've also managed to exclude the source table headings.

Sub XferTblData()
Application.ScreenUpdating = False
Dim DocSrc As Document, TblSrc As Table, RngSrc As Range, RowSrc As String, ColSrc As String
Dim DocTgt As Document, TblTgt As Table, RngTgt As Range, RowTgt As String, ColTgt As String
Dim i As Long
RowSrc = "5,5,6,6,7,7,9,9,11,11"
ColSrc = "1,2,1,2,1,2,1,2,1,2"
RowTgt = "10,10,12,12,14,14,17,17,20,20"
ColTgt = "1,2,1,2,1,2,1,2,2,3"
Set DocTgt = ActiveDocument
With Application.Dialogs(wdDialogFileOpen)
  If .Show = -1 Then
    .AddToMru = False
    .ReadOnly = True
    .Visible = False
    .Update
    Set DocSrc = ActiveDocument
  End If
End With
If DocSrc Is Nothing Then Exit Sub
Set TblSrc = DocSrc.Tables(2)
Set TblTgt = DocTgt.Tables(1)
For i = 0 To UBound(Split(RowSrc, ","))
  Set RngSrc = TblSrc.Cell(Split(RowSrc, ",")(i), Split(ColSrc, ",")(i)).Range
  RngSrc.End = RngSrc.End - 1
  RngSrc.Start = RngSrc.Paragraphs(1).Range.End
  Set RngTgt = TblTgt.Cell(Split(RowTgt, ",")(i), Split(ColTgt, ",")(i)).Range
  RngTgt.End = RngTgt.End - 1
  RngTgt.FormattedText = RngSrc.FormattedText
Next
DocSrc.Close SaveChanges:=False
Application.ScreenUpdating = True
End Sub

[mcb09]

This is AMAZING, Paul! Thank you! I do get the below message. I click and END and it completes the macro. Row 10, Col 2 of the target doc doesn't get populated with content (Row 20, Col 2 of the source).

[macropod]

Using the documents attached to post #3, the macro runs to completion with no errors for me. The error message suggests a referenced cell in one of the documents you're working with doesn't exist.

[mcb09]

I see my error. I tried to add the Tier 2 and Tier 3 cells at the bottom of Doc B. They exist in Doc A, but I never created them in Doc B. I tried to edit your code to accommodate for the addition, but that didn't work. Word kept getting hung and crashing.

Below is the Doc A content that I trying to move as well. How would you alter your code?

[macropod]

Use:

RowSrc = "5,5,6,6,7,7,9,9,11,11,12,12"
ColSrc = "1,2,1,2,1,2,1,2,1,2,1,2"
RowTgt = "10,10,12,12,14,14,17,17,20,20,22,22"
ColTgt = "1,2,1,2,1,2,1,2,2,3,1,2"

[mcb09]

That worked!! Thank you so much. This is going to save me so much time and sweat.

In case you have another miracle to perform, some of the source content for Doc A comes from an Excel sheet. The content there is in HTML markup. If you're feeling up another challenge, is their way to create a macro that moves the data from those cells in sheet to corresponding cells in Doc B while converting the HTML to readable text?

[macropod]

Originally Posted by mcb09

some of the source content for Doc A comes from an Excel sheet. The content there is in HTML markup. If you're feeling up another challenge, is their way to create a macro that moves the data from those cells in sheet to corresponding cells in Doc B while converting the HTML to readable text?

Possibly - even probably. However, that would require knowing a lot about the Excel data source and how a given Doc A relates to those data, so that the correct set from the Excel data source could be imported into Doc B. Not something I have time to tackle right now.

[mcb09]

I understand. Thanks again for your help. It's making all the difference.