Accessing word macros from excel

  • I have written several macros in VBA / EXCEL but want to access a WORD (template / master) then replace various fields from my VBA EXCEL application database.
    Example replace my_address_line1 (in the word document, with the actual address line from my text database accessed from VBA / Excel


    Just not sure if I can do what I want to achieve!


    When the WORD document is 'edited' then I wish to save and either Print or Email. I don't want to save macros into the final word document id sending by email (I guess that would be poor practice).


    There doesn't seem to be an option to save a file in word which is macro enabled or disabled.


    Just trying to get some pointers before I head off in the complete wrong direction!

  • Re: Accessing word macros from excel


    If you need to edit the title, click the 'Edit post' link beneath the message and then click the 'Go advanced' button - you'll find an option to edit the title there... I edited it this time.


    Your post is confusing. There's no need to access Word macros, everything can be done in Excel, and the resulting document can be saved without any macros in it.


    I can't give specifics at the moment as I'm not at a computer (mobile phone), but I'll have a quick search for relevant threads here...

  • Re: Accessing word macros from excel


    Couldn't really find anything suitable (and simple enough). Following was quickly cobbled together and was tested. Comments in the code.

  • Re: Accessing word macros from excel


    Just trying to implement something for myself and wondering how I could for example replace


    my_replacements(1,1) with my_replacements(1,2) where my_replacements(1,1)= "replacement_1" and my_replacements(1,2) is the new text for this occurrence e.g. name
    my_replacements(2,1) with my_replacements(2,2) where my_replacements(2,1)= "replacement_2" and my_replacements(1,2) is the new text for this occurrence e.g. address_line_1
    my_replacements(3,1) with my_replacements(3,2) where my_replacements(3,1)= "replacement_3" and my_replacements(1,2) is the new text for this occurrence e.g. city
    etc.

  • Re: Accessing word macros from excel



    ...?

  • Re: Accessing word macros from excel


    I seem to be struggling and your help is valued.


    I had the basics of your example but my system seems to 'lock' then comes up with FILE is locked for editing, read only etc.


    Doesn't seem to close. Needs an object.



  • Re: Accessing word macros from excel


    The file is already open... shows the reason why error handlers should be included in code.


    Either:
    Close any copies of the file you have open.
    Or
    Use TaskManager to check for running but hidden copies of Word and kill the processes or restart your machine.

  • Re: Accessing word macros from excel


    Quote from cytop;604652

    The file is already open... shows the reason why error handlers should be included in code.


    Either:
    Close any copies of the file you have open.
    Or
    Use TaskManager to check for running but hidden copies of Word and kill the processes or restart your machine.


    Guess I am a little out of my depth and where to look.


    I killed the process using TASKMANAGER then tried to run and failed trying to SAVE, QUIT etc stating there was no object.
    Presumably there are some handlers in word for checking if the file or object is closed / open == I just don't seem to be thinking along the right path


  • Re: Accessing word macros from excel


    if I add wrdApp.Visible = True then a WORD window opens but no replacements appear to have occurred when the failure happens - i.e. "council" is still present

  • Re: Accessing word macros from excel


    Combination of errors - mostly mine, for which I apologise. Even though I tested the original code, I modified it afterwards - and that's usually fatal. Following has been tested completely, and works.


    Note some changes need to be reversed...
    Load location
    Save location
    Save file name
    and obviouly the text to replace and it's replacement...



    An object lesson - always include the 'Option Explicit' statement at the top of every module. It helps to highlight errors with variable names. You can add it automatically to new modules/userforms by setting the 'Require Variable Declaration' checkbox on the VBA settings window: In 2003 by using the Tools/Options menus in and selecting the Editor tab. Not too sure where it is exactly in later versions.

  • Re: Accessing word macros from excel


    realised I had not correctly got 'wrdApp' in all correct places.
    The code below seems to achieve everything ** except ** make the replacements. WORD opens (visible).
    A file is saved i.e. ProblemsA.docx but it appears to be the same as the original - i.e. no replacements have taken place.




  • Re: Accessing word macros from excel


    Thought I had made a reply but can't locate.


    Now made alterations and the following appears to work correctly except that the replacements are not made.
    WORD opens
    WORD saves to file ProblemsA
    WORD quits
    ...but no replacements have been made (I changed to "and" for multiple replacements)



  • Re: Accessing word macros from excel


    My previous post has corrected that. It has been tested and works....


    The problem was a combination of errors. When I wrote and tested the procedure first, I included a reference to the Work Object model using the Tools/References menu.


    After testing the first code I posted, I removed the reference to Word to make the code 'generic' regardless which version of Office was used, however I left in a Word constant named wdReplaceAll. This is used by the .Execute menthod of the Find method and tells Word to replace all occurrances of the search string.


    However, as your module did not include an 'Option Explicit' statement, the fact that this variable was now undefined wasn't picked up. Without the Option Explicit statement, VBA will create variables on the fly... and assign a default value (0 for numeric, null string for strings).


    The end result was the .Execute method was using a Replace property with a value of 0 which tells Word to replace nothing! Corrected by including a definition for wdReplaceAll and assigning a value of 2...


    Have another read of the last paragraph in my previous post. Including the Option Explicit statement in every module can save a lot of grief...

  • Re: Accessing word macros from excel


    sorry if I had been at cross purposes. I had gone to what I thought was the bottom of the thread but was in fact only the end of page 1. Now found page 2 .


    Many thanks for all your support.


    PS - just edited to ADD I have now run the code and it works - many , many thanks

  • Re: Accessing word macros from excel


    I am getting a

    Quote


    Run-time error '9':


    Subscript out of range


    and here's the code I was trying from above


    I click debug on the error window, and it highlights the 3rd my replacements line, with "da-ac"



    it may also help if I give some context as to what I am doing. I have about 40-50 pages of letters, documents, etc... in word that are now 1 file, and have generics (ie: [da-ac]) and an excel file where in A1, I have [da-ac] and the text I intend to replace it with in B1. Down to 13
    I've been struggling with writing this piece of code for a little over a month and a half now, so anything to point me in the right direction is appreciated more than you can imagine.

  • Re: Accessing word macros from excel


    Quote from sleeper108;606362

    I am getting a



    Dim my_replacements(2, 2)
    [/code]
    .


    the above only defines the following cells. You need to replace with Dim my_replacements(2, 9) - this should eliminate the subscript error


    [TABLE="width: 56"]

    [tr]


    [td]

    my_replacements(0,0)

    [/td]


    [/tr]


    [tr]


    [td]

    my_replacements(0,1)

    [/td]


    [/tr]


    [tr]


    [td]

    my_replacements(0,2)

    [/td]


    [/tr]


    [tr]


    [td]

    my_replacements(1,0)

    [/td]


    [/tr]


    [tr]


    [td]

    my_replacements(1,1)

    [/td]


    [/tr]


    [tr]


    [td]

    my_replacements(1,2)

    [/td]


    [/tr]


    [tr]


    [td]

    my_replacements(2,0)

    [/td]


    [/tr]


    [tr]


    [td]

    my_replacements(2,1)

    [/td]


    [/tr]


    [tr]


    [td]

    my_replacements(2,2)

    [/td]


    [/tr]


    [/TABLE]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!