handle file between Word & Excel in VBA

Try:

Sub ExportHighlightText()
Dim appXL As New Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet, i As Long
Set xlWB = appXL.Workbooks.Add: Set xlWS = xlWB.Worksheets(1)
appXL.Visible = True

With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = ""
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .Highlight = True
    .Execute
  End With
  Do While .Find.Found
    Select Case .HighlightColorIndex
      Case wdYellow
        i = i + 1
        xlWS.Cells(i, 1).Value = .Text
      Case wdBrightGreen
        i = i + 1
        xlWS.Cells(i, 2).Value = .Text
    End Select
    If .End = ActiveDocument.Range.End Then Exit Sub
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With

Set xlWS = Nothing: Set xlWB = Nothing: Set appXL = Nothing
End Sub

Note: each match is output to a new row; it's not clear whether that's what you want or just for the data in each column to be treated independently (i.e. no empty rows in either column). There is also no need with this code for anything in the document to be hidden.

Without knowing what those modifications might be, it's impossible to say what you should do. Besides which, it makes no sense to me to be copying the text from Word to Excel so you can modify it there before copying it back to the document instead of modifying it in the document itself.

So is this new macro to be run from Word or Excel? Furthermore, if it's from
• Word, how is the workbook to be identified;
• Excel, how is the document to be identified?

Originally Posted by Ethen5155

from Word to Excel --> just copy highlight green to column (A) and highlight
yellow to column (B)

from Excel to Word --> use the same word file we exported text from to
replace column (A) again instead of highlight green and replace column (B)
instead of highlight yellow

I understand all that but, once the macro I posted has finished running, Word 'remembers' nothing about what just happened and doesn't 'know' there is an unsaved Excel workbook with some of its data. Likewise, Excel 'knows' nothing about the Word document or what should be done with the data after you've processed it with the Trados cat tool. So:
• does the newly-created Excel workbook remain open after all the Trados cat tool processing has finished?
• does the Word document remain open while that processing is done?
If the answer to either of these questions is 'no', then we need to be able to tell whichever application you want the second process from how to find the Word document or Excel workbook, as appropriate, so the updated data can be sent back. If the answer to both questions is yes, the code to transfer the updated data back to Word is quite simple (some minor changes to the previous code would also be needed).

Ethen

What tool are you using in Excel are you using and how are you using it?

Which CAT tool are you using?

How are you using/running it when you have the data in Excel?

Oops, missed that.

Can't you use Trados in Word?

In that case, you might try the following two macros.

The first is a variation of the original one but now saves the Excel workbook to the same folder and with the same name as the document. The second one opens that (updated) workbook to import the updates. So, provided the edited workbook's name is kept the same as the document's and is returned to the same folder, the updates should work seamlessly. As you will see, much of the code for the export and import is the same.

Sub ExportHighlightText()
Dim appXL As New Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet, i As Long
Set xlWB = appXL.Workbooks.Add: Set xlWS = xlWB.Worksheets(1)
appXL.Visible = True

With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = ""
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .Highlight = True
    .Execute
  End With
  Do While .Find.Found
    Select Case .HighlightColorIndex
      Case wdYellow
        i = i + 1
        xlWS.Cells(i, 1).Value = .Text
      Case wdBrightGreen
        i = i + 1
        xlWS.Cells(i, 2).Value = .Text
    End Select
    If .End = ActiveDocument.Range.End Then Exit Do
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With
xlWB.SaveAs FileName:=Split(ActiveDocument.FullName, ".doc")(0) & ".xls", FileFormat:=xlExcel8, AddToMRU:=False
Set xlWS = Nothing: Set xlWB = Nothing: Set appXL = Nothing
End Sub

Sub ImportHighlightText()
Dim appXL As New Excel.Application, xlWB As Excel.Workbook, xlWS As Excel.Worksheet, i As Long
Set xlWB = appXL.Workbooks.Open(FileName:=Split(ActiveDocument.FullName, ".doc")(0) & ".xls", ReadOnly:=True, AddToMRU:=False)
Set xlWS = xlWB.Worksheets(1)
appXL.Visible = False

With ActiveDocument.Range
  With .Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = ""
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .Highlight = True
    .Execute
  End With
  Do While .Find.Found
    Select Case .HighlightColorIndex
      Case wdYellow
        i = i + 1
        .Text = xlWS.Cells(i, 1).Value
      Case wdBrightGreen
        i = i + 1
        .Text = xlWS.Cells(i, 2).Value
    End Select
    If .End = ActiveDocument.Range.End Then Exit Do
    .Collapse wdCollapseEnd
    .Find.Execute
  Loop
End With

xlWB.Close: appXL.Quit
Set xlWS = Nothing: Set xlWB = Nothing: Set appXL = Nothing
End Sub

Cross-posted at: http://www.vbaexpress.com/forum/show...p-Excel-in-VBA

Please read the Cross-Posting policy in rule #8: http://www.excelforum.com/forum-rule...rum-rules.html