handle file between Word & Excel in VBA

Hi all,

well i have many files to handle between Word and Excel with a specific structure

i want to copy all Green segments then generate an Excel file in same path and copy it to column (A)
then copy all Yellow segments to same file but at column (B)



after that when i finish some modifications at excel i want to copy them back to word file with same structure:
Column (A) --> goes to Green
Column (B) --> goes to Yellow


Note: There are hidden segments i used wildcard "(\@)(*)(\^13)" to make them hidden


sample file is attached too


i tried my luck starting with this code, to export highlight text to a new excel file but i faced two problems


first one it exports the text in column (B) but under other text in column (A) not opposite to it

second issue that it exports text as sentences and divide it to two lines not as paragraphs

and i'm still don't know how to get it back from excel to word with keeping same structure !!




Sub ExportHighlightText()


Dim docCur As Document
Dim snt As Range
Dim Paragraphs As Range
Dim i As Integer

Dim appXL As excel.Application, xlWB As excel.Workbook, xlWS As excel.Worksheet
Dim path As XPath



Set appXL = CreateObject("Excel.Application")
appXL.Visible = True
Set xlWB = appXL.Workbooks.Add
Set xlWS = xlWB.Worksheets(1)




Application.ScreenUpdating = False

Set docCur = ActiveDocument


For Each snt In docCur.Sentences
If snt.HighlightColorIndex = wdBrightGreen Then
i = i + 1
xlWS.Cells(i, 1).Value = snt.Text
End If
Next snt


For Each snt In docCur.Sentences
If snt.HighlightColorIndex = wdYellow Then
i = i + 1
xlWS.Cells(i, 2).Value = snt.Text
End If
Next snt


ExitHandler:
Application.ScreenUpdating = True
Set snt = Nothing
Exit Sub



Resume ExitHandler
End Sub



is it possible to do that in VBA, Any hint or help would be appreciated.


Thanks in Advance

Cheers

Ethen




Link posting: http://www.vbaexpress.com/forum/show...p-Excel-in-VBA