Solved: Moving table content to Excel and back again

[NPC]

Dear gurus,

I'm trying to create a Macro in Word 2010 that will do the following:
-Copy text from a table which contains four columns. I want the last two columns.
-Open Excel and paste that text into excel.
-Run a Macro in Excel that will combine the text from the two columns (A and B in Excel), separated by symbols. This is generated in column C.
-Copy all text in column C and paste it back into the Word document in the fourth column.

Basically I have many, many files to process, each containing a table of differing length. The tables contain text like this:

ID | % | Source | Target
1 | 0% | cereza | cherry
2 | 0% | manzana | apple

...and so on. I want to run it through Excel to change the table's content to:

ID | % | Source | Target
1 | 0% | cereza | ΦcerezaΩcherry
2 | 0% | manzana | ΦmanzanaΩapple

This is what I have from recording macros and cobbling things together from the internet, but I am getting a compile error ('Method of data member not found').

Current code for Word:

Sub ExcelTest()
'
' Test2 Macro
' Testing with Excel copy/paste
'
ActiveDocument.Tables(1).Select
Selection.SetRange _
Start:=Selection.Columns(3).Range.Start, _
End:=Selection.End
Selection.Copy
Sub Macro1()
Dim oXL As Object
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
oXL.Documents.Add
oXL.Application.Run MacroName:="IDML_Conversion"
MsgBox "Now back to Word!"
End Sub
ActiveDocument.Tables(1).Columns(4).Select
Selection.PasteExcelTable False, False, False
ActiveDocument.Save
End Sub


Current code for Excel (in Personal.xlsb):

Sub IDML_Conversion()
'
' IDML_Conversion Macro
'

'
ActiveSheet.Paste
Columns("B:B").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormulaR1C1 = "=""F""&RC[-2]&""O""&RC[-1]"
Range("C1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
End Sub


I'm sure there are probably a lot of ignorant errors in here; I really don't know anything about VBA but am doing my best to try to figure it out. Any help would be greatly appreciated!

[NPC]

Uh-oh, I've just realized a problem. Copying the text from the tables in Word means that if there are hard breaks within the table, the text seems to be split into two cells in Excel instead of having two lines in one cell. So working from Word might be not the ideal option...

I have Word and Excel versions of the tables, but the final text needs to be in the Word file, so I thought it would be easier to work entirely from there. If I want each Word file to call up its respected Excel counterpart, is there a way to conditionally call up an Excel file?

What I mean is this: I have come across things like the following...

Docname = "d:\data98\TEST DOCUMENT.doc"
Set oWrdDoc = oWrd.Documents.Open(Docname)

But putting in a specific name means it will only work for that particular document and I don't want to have to edit the macro for every single instance. Can I somehow put in a variable that sets Docname (or whatever the Excel equivalent is) to the same name as the Word doc, only with an .xls extension? I.e., if I am working with fruits.doc, I want to open fruits.xls and past the data from fruits.xls into fruits.doc. Make sense?

ETA: The line I am now considering pursuing is the follow:
- User opens excel worksheet
- Runs macro
MACRO:
(If necessary, puts column D into Column E (just to define length) then use ctrl+shift+down to select E)
Fills column E with formula
Copy occupied text from column E
Open equivalent word doc
Select column 4
Paste column E text into column 4
Close Excel
Save Word doc

The only reason I have to put it back into the existing Word doc is because that doc is used to convert to another file format, which only works properly from the original Word doc. There must be something in it "behind the scenes" because if I try to simply paste the excel data into a new .doc, that .doc does not successfully convert to the final file type. I've been banging my head against that wall for a while already...

[geekgirlau]

Can I suggest that you rethink your approach? Why do you want to use Excel as an intermediary?

How about you just concatenate the contents of the cells directly in Word?

[macropod]

I agree - nothing in the post suggests Excel is needed.

NPC: You will still have to resolve the issue of cells having more than one paragraph, though. it's not clear what's supposed to get concatenated in such cases. You also mention:

convert to another file format, which only works properly from the original Word doc
This is ambiguous - is the original file a Word file, or something else? If it's a Word file, to what are you trying to convert it?

[NPC]

Sorry for the confusion. I would be happy to rethink my approach! I can imagine there must be simpler ways to do this--I'm just not aware of them. Until last week, I had basically never used Word for anything more complicated than word processing.

A quick search indicates there is some way to concatenate cell contents in a Window table, something I wasn't aware of before this morning(!). Is it also easy to insert new information between the cell content, like the symbols we are talking about?

As for the hard breaks, ideally the end result would be something like this:

Before
SOURCE
Este es un
mal ejemplo.

TARGET
This is a bad
example.

After
SOURCE
Este es un
mal ejemplo

TARGET
ΦEste es un
mal ejemplo.ΩThis is a bad
example.

Does that make any sense? I'd like to put the content of the source column in before any content in the target column, preserving any line breaks. Would concatenation automatically do so?

I'm sorry for the unnecessarily complicated situation. To address your question, Macropod, the files originate from InDesign, then the text is translated using CAT tools which results in an SDLXLIFF file to be converted back to InDesign (IDML). But this results in two separate IDML files for each language and the client wants both languages in one IDML file, separated by these symbols so that one language or the other can be hidden. Basically to create a bilingual IDML file. SDLXLIFF can most easily be converted to .doc or .xls, thus why we have those two file extensions available to play with and to create "bilingual" entries in the target column. Does that help clarify things at all? This could all be done manually, of course, with lots of copying and pasting, but with dozens of long files to adjust, one efficient code would really help on time.

ETA:
Reading around on concatenation sure sounds like it could work. Can you use concatenation within the same cell that the information is being drawn from or would that create some kind of loop? I.e., can columns C and D be concatenated in D, or do we need to create a new column E to store the newly concatenated information?

This is how I am imagining it, but please tell me if I am making things unnecessarily complicated again!

1. Create a new column on the right hand of the table. (A fifth column, or column E in Excel parlance.)
2. Populate that column with the content: Φ[column C cell content]Ω[column D cell content]
3. Copy column E
4. Paste into column D
5. Delete the now empty column E

The last two steps are because we want to preserve the original formatting of a four-column table for converting the word document back to SDLXLIFF.

Thank you all again for taking the time to bash through this with me!

[NPC]

Okay, I've been working off of what I can find online again and this is what I have so far:



Sub ConcatTest()
'
' ConcatTest Macro
'
'
ActiveDocument.Tables(1).Columns(4).Select
Selection.InsertColumnsRight
Dim rng As Range
With ActiveDocument.Tables(1)
Set rng = .Cell(2, 3).Range
rng.MoveEnd Count:=-1
If rng.End > rng.Start Then
rng.Copy
.Cell(2, 5).Range.Paste
.Cell(2, 5).Range.InsertBefore ("`")
.Cell(2, 5).Range.InsertAfter ("|")
End If
Set rng = .Cell(2, 4).Range
rng.MoveEnd Count:=-1
If rng.End > rng.Start Then
rng.Copy
Set rng = .Cell(2, 5).Range
rng.MoveEnd Count:=-1
rng.Collapse Direction:=wdCollapseEnd
rng.Paste
End If
End With
Selection.Tables(1).AutoFitBehavior (wdAutoFitWindow)

End Sub


It works for one row and does seem to preserve the line breaks, so that's all well and good. It can't seem to handle the characters we had chosen as delimiters, so I chose more "standard" characters which are unlikely to be used in the source or target text.

I have a couple of questions, though. Most importantly, how do I make it keep looping this action for each row in the document? And second, the current code is inserting a space between the two terms. Is there something in this code that I found which is inserting that space? If so, I wasn't able to easily recognize it. Thank you all again for your patience!

[macropod]

Try the following. It will process every table in every document in the selected folder:
Sub UpdateDocuments()
Application.ScreenUpdating = False
Dim strFolder As String, strFile As String, wdDoc As Document
Dim Tbl As Table, TblRw As Row, Rng1 As Range, Rng2 As Range
strFolder = GetFolder
If strFolder = "" Then Exit Sub
strFile = Dir(strFolder & "\*.doc", vbNormal)
While strFile <> ""
Set wdDoc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
On Error Resume Next
For Each Tbl In wdDoc.Tables
For Each TblRw In Tbl.Range.Rows
With TblRw
Set Rng1 = .Cells(2).Range
Rng1.End = Rng1.End - 1
Set Rng2 = .Cells(3).Range
Rng2.End = Rng2.End - 1
.Cells(4).Range.Text = ChrW(934) & Rng1.Text & ChrW(937) & Rng2.Text
End With
Next
Next Tbl
wdDoc.Close SaveChanges:=True
strFile = Dir()
Wend
Set Rng1 = Nothing: Set Rng2 = Nothing: Set wdDoc = Nothing
Application.ScreenUpdating = True
End Sub

Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

[NPC]

That's brilliant! I never even thought of running it at the folder level, but it will be even more efficient to run files as batches. Thank you for your help and the great code. It is working great in my tests. :bow: