+ Reply to Thread
Results 1 to 18 of 18

handle file between Word & Excel in VBA

  1. #1
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    65

    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
    Attached Files Attached Files

  2. #2
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: handle file between Word & Excel in VBA

    Try:
    Please Login or Register  to view this content.
    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.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    65

    Re: handle file between Word & Excel in VBA

    Dear Paul ,





    sorry for bothering you again, finally i succeeded to make a reply



    anyway i used your code and it works perfect,



    but you didn't answer my other final issue and that is how to get that text
    back from excel to word after some modifications



    i want to copy all text in column (A) again to doc file in the green
    sections, then text from column (B) to yellow sections



    cheers



    Ethen

  4. #4
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: handle file between Word & Excel in VBA

    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.

  5. #5
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    65
    Hi Paul,



    well i need to copy those segments from word to excel to make some
    modifications in translation CAT Tool with a specific structure can't be
    done in word, i have to do it in excel in both columns (A) and (B) then make
    it back again to original word file also with same structure



    so i asked for that step, and it is very important to me even i can pay
    for it



    can it be??



    cheers



    Ethen

  6. #6
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: handle file between Word & Excel in VBA

    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?

  7. #7
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    65
    sorry i don't get what you mean but i can explain more



    maybe i need two macros, one for word (export text to excel) and other for
    excel (import text again to word) or just one for word because it is easier.



    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 attached sample word file in main post above



    i hope i make it clear



    thanks a lot for your attention



    cheers



    Ethen

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: handle file between Word & Excel in VBA

    Ethen

    What tool are you using in Excel are you using and how are you using it?
    If posting code please use code tags, see here.

  9. #9
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    65
    Hi Norie

    Do you mean what version of excel or that cat tool i use??

    I use excel 2010
    And Trados cat tool, i insert excel file as a bilingual in trados using colum A and B

    So i need to do some modifications at transtion in cat tool then imort it back to word again

    Cheers

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: handle file between Word & Excel in VBA

    Which CAT tool are you using?

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

  11. #11
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    65
    Dear Norie,



    as i mentioned i use "Trados" CAT Tool



    when i have data inserted from word to excel file in ( Column A) and (Column
    B) i insert that file to Trados to be treated as: Source = Column (A) /
    Translation = Column (B)



    so it became bilingual file in Trados



    after i do some editing on column (B) as it belongs to translation in Trados
    then i have to import that again to word in same structure at original file
    in word

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: handle file between Word & Excel in VBA

    Oops, missed that.

    Can't you use Trados in Word?

  13. #13
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    65

    Re: handle file between Word & Excel in VBA

    unfortunately i can't, trados dosen't support to insert bilingual word on it as does in excel

  14. #14
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: handle file between Word & Excel in VBA

    Quote Originally Posted by Ethen5155 View Post
    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).

  15. #15
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    65
    Hi Paul,



    first of all your care and explanation are highly appreciated.



    well the answer is no unfortunately , because those modifications in CAT
    Tool may be done on another PC with another person



    that's why i do two highlighted colors in word file to make the text special
    and specific while tell either excel or word to replace text back to it





    now i hope i put all points between your hands, and it doesn't matter to me
    to make two codes between word and excel or just one application, it only
    matters the good result.



    Thanks in advance even you couldn't reach it



    cheers

  16. #16
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: handle file between Word & Excel in VBA

    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.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  17. #17
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: handle file between Word & Excel in VBA


  18. #18
    Registered User
    Join Date
    01-11-2017
    Location
    London, England
    MS-Off Ver
    2003,2007,2010,2013
    Posts
    65

    Re: handle file between Word & Excel in VBA

    Dear Paul,



    sorry for being late to post this but i was on a vacation



    really i don't have enough words to thank you for that great code, i admire
    your efforts a lot.



    works like magic



    thanks again bro



    Cheers



    Ethen

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Word 2016 is default, but hyperlinks in Excel file open Word 2010
    By 6StringJazzer in forum Excel General
    Replies: 2
    Last Post: 11-29-2016, 11:29 PM
  2. [SOLVED] How do I best handle these file messages?
    By terrypin in forum Excel General
    Replies: 2
    Last Post: 09-25-2016, 05:50 PM
  3. set word file format in excel and auto adjust requirement in file
    By adeel1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-04-2016, 09:14 AM
  4. [SOLVED] How to read Excel or Word file (Multi-Word phrases) into Array
    By pradeepdeepu_001 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-15-2013, 03:02 AM
  5. Hiding table's handle in Word 2007
    By florence in forum Word Formatting & General
    Replies: 6
    Last Post: 09-11-2010, 04:43 PM
  6. Can a single excel file handle over 100,000 lines?
    By seigi009 in forum Excel General
    Replies: 2
    Last Post: 07-26-2007, 02:02 PM
  7. overflow-how big a file can Excel handle?
    By John Smith in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-04-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1