Announcement

Collapse
No announcement yet.

Accessing word macros from excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!
    Last edited by cytop; April 12th, 2012, 02:06. Reason: trying to edit frim to from in header - unable to do so

  • #2
    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...

    Comment


    • #3
      Re: Accessing word macros from excel

      Originally posted by cytop View Post
      .............), but I'll have a quick search for relevant threads here...
      Many thanks.

      Comment


      • #4
        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.
        Code:
        Sub FindReplaceSample()
             
           '// To test this code, paste it into an Excel module
           
           Dim wrdApp As Object    '// Word.Application    if using Early binding and reference set in project
           Dim wrdDoc As Object    '// Word.Document       same
           
           '// This procedure assumes a range is selected/highlighted in Excel.
           '// The highlighted range provides the text to search for
           '// Adjacent cell in the next column provides the replacement text
           Dim r As Excel.Range
           
           '// Start Word
           Set wrdApp = CreateObject("Word.Application")
           
           '// Make visible and load template
           '// Set .Visible False if doinga lot of processing. This speeds things up
           '// as the screen does not have to be refreshed after every change
           With wrdApp
              .Visible = True
              '// Load a template - Change to suit
              .Documents.Add Template:="C:\Documents and Settings\xxx\Application Data\Microsoft\Templates\test.dot", NewTemplate:=False
           End With
           
           '// Set a reference to the active document
           Set wrdDoc = wrdApp.ActiveDocument
           
           '// or - don't load a template, just open a doc instead
           '// Set wrdDoc = wrdApp.Documents.Open("C:\Foldername\Filename.doc")
           
           '// for each selected cell in the Excel sheet
           For Each r In Selection
           
              With wrdDoc.Range.Find
                 
                 '// Search value in cell
                 .Text = r.Value
                 
                 '// Replace with value in adjacent cell
                 '// Needs to change to match your requirements
                 '// I didn't have any details so just made up this...
                 .Replacement.Text = r.Offset(0, 1).Value
                 
                 '// Other parameters - check if are as required.
                 .Forward = True
                 .Wrap = wdFindContinue
                 .Format = False
                 .MatchCase = False
                 .MatchWholeWord = False
                 .MatchWildcards = False
                 .MatchSoundsLike = False
                 .MatchAllWordForms = False
                 
                 '// Go replace
                 .Execute Replace:=wdReplaceAll
                 
              End With
              
           Next
           
           '// Need to add more code to save, email or whatever you want to do...
        End Sub

        Comment


        • #5
          Re: Accessing word macros from excel

          Many thanks again.

          Comment


          • #6
            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.

            Comment


            • #7
              Re: Accessing word macros from excel

              Code:
                  Dim iTemp as Integer
                  
                  For iTemp = 1 to 3
                       
                      With wrdDoc.Range.Find 
                           
                           '// Search value in cell
                          .Text = my_replacements(iTemp,1)              
              
                           .Replacement.Text = my_replacements(iTemp,2)  
                           
                           '// Other parameters - check if are as required.
                          .Forward = True 
                          .Wrap = wdFindContinue 
                          .Format = False 
                          .MatchCase = False 
                          .MatchWholeWord = False 
                          .MatchWildcards = False 
                          .MatchSoundsLike = False 
                          .MatchAllWordForms = False 
                           
                           '// Go replace
                          .Execute Replace:=wdReplaceAll 
                           
                      End With 
                       
                  Next
              ...?

              Comment


              • #8
                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.


                Code:
                Sub FindReplaceSample()         '// To test this code, paste it into an Excel module
                Dim my_replacements(2, 2)
                Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
                Dim wrdDoc As Object '// Word.Document       same
                    Set wrdApp = CreateObject("Word.Application")
                    Set wrdDoc = wrdApp.Documents.Open("D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\Problems.docx")
                Dim iTemp As Integer
                    my_replacements(1, 1) = "council"
                    my_replacements(1, 2) = "xxxxxxx"
                    For iTemp = 1 To 1
                        With wrdDoc.Range.Find
                             '// Search value in cell
                            .Text = my_replacements(iTemp, 1)
                            .Replacement.Text = my_replacements(iTemp, 2)
                             '// Other parameters - check if are as required.
                            .Forward = True
                            .Wrap = wdFindContinue
                            .Format = False
                            .MatchCase = False
                            .MatchWholeWord = False
                            .MatchWildcards = False
                            .MatchSoundsLike = False
                            .MatchAllWordForms = False
                             '// Go replace
                            .Execute Replace:=wdReplaceAll
                        End With
                    Next
                    appWd.ActiveDocument.SaveAs Filename:="D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\ProblemsA.docx"
                        ' Close this new word document
                    appWd.ActiveDocument.Close
                    appWd.Quit
                     '// Need to add more code to save, email or whatever you want to do...
                End Sub

                Comment


                • #9
                  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.

                  Comment


                  • #10
                    Re: Accessing word macros from excel

                    Originally posted by cytop View Post
                    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

                    Code:
                    Sub FindReplaceSample()         '// To test this code, paste it into an Excel module
                    Dim my_replacements(2, 2)
                    Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
                    Dim wrdDoc As Object '// Word.Document       same
                    Dim iTemp As Integer
                        Set wrdApp = CreateObject("Word.Application")
                        Set wrdDoc = wrdApp.Documents.Open("D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\Problems.docx")
                        Set wrdDoc1 = wrdApp.ActiveDocument
                        my_replacements(1, 1) = "council"
                        my_replacements(1, 2) = "xxxxxxx"
                        
                        For iTemp = 1 To 1      '       reaches breakpoint 1 OK
                            With wrdDoc.Range.Find
                                 '// Search value in cell
                                .Text = my_replacements(iTemp, 1)
                                .Replacement.Text = my_replacements(iTemp, 2)
                                 '// Other parameters - check if are as required.
                                .Forward = True
                                 '// Go replace
                                .Execute Replace:=wdReplaceAll      '    reaches breakpoint 2 here
                            End With
                        Next
                        '   fails with needs object - wrdDoc & wrdDoc1 appears as "Document1"
                        appWd.ActiveDocument.SaveAs Filename:="D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\ProblemsA.docx"
                            ' Close this new word document
                        appWd.ActiveDocument.Close
                        appWd.Quit                          '// Need to add more code to save, email or whatever you want to do...
                    End Sub

                    Comment


                    • #11
                      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

                      Comment


                      • #12
                        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...

                        Code:
                        Option Explicit
                        
                        Sub FindReplaceSample() '// To test this code, paste it into an Excel module
                            Dim my_replacements(2, 2)
                            Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
                            Dim wrdDoc As Object '// Word.Document       same
                            Dim iTemp As Integer
                            
                            Set wrdApp = CreateObject("Word.Application")
                            Set wrdDoc = wrdApp.Documents.Open("c:\temp\document.doc")
                            
                            Const wdReplaceAll As Integer = 2
                            
                            '// Set wrdDoc1 = wrdApp.ActiveDocument
                            
                            my_replacements(1, 1) = "council"
                            my_replacements(1, 2) = "xxxxxxx"
                             
                            my_replacements(2, 1) = "test"
                            my_replacements(2, 2) = "yyyyyyy"
                             
                            For iTemp = 1 To 2 '       reaches breakpoint 1 OK
                                With wrdDoc.Range.Find
                                     '// Search value in cell
                                    .Text = my_replacements(iTemp, 1)
                                    .Replacement.Text = my_replacements(iTemp, 2)
                                     '// Other parameters - check if are as required.
                                    .Forward = True
                                     '// Go replace
                                    .Execute Replace:=wdReplaceAll '    reaches breakpoint 2 here
                                End With
                            Next
                             '   fails with needs object - wrdDoc & wrdDoc1 appears as "Document1"
                            With wrdApp
                              .ActiveDocument.SaveAs Filename:="c:\temp\document1.doc"
                              ' Close this new word document
                              .ActiveDocument.Close
                              .Quit '// Need to add more code to save, email or whatever you want to do...
                            End With
                            
                            Set wrdApp = Nothing
                            Set wrdDoc = Nothing
                            
                        End Sub
                        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.

                        Comment


                        • #13
                          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.



                          Code:
                          Sub FindReplaceSample()         '// To test this code, paste it into an Excel module
                          Dim my_replacements(2, 2)
                          Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
                          Dim wrdDoc As Object '// Word.Document       same
                          Dim iTemp As Integer
                              Set wrdApp = CreateObject("Word.Application")
                              Set wrdDoc = wrdApp.Documents.Open("D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\Problems.docx")
                              Set wrdDoc1 = wrdApp.ActiveDocument
                              wrdApp.Visible = True
                              my_replacements(1, 1) = "council"
                              my_replacements(1, 2) = "xxxxxxx"
                              
                              For iTemp = 1 To 1      '       reaches breakpoint 1 OK
                                  With wrdDoc.Range.Find
                                       '// Search value in cell
                                      .Text = my_replacements(iTemp, 1)
                                      .Replacement.Text = my_replacements(iTemp, 2)
                                       '// Other parameters - check if are as required.
                                      .Forward = True
                                       '// Go replace
                                      .Execute Replace:=wdReplaceAll      '    reaches breakpoint 2 here
                                  End With
                              Next
                              '   fails with needs object - wrdDoc "Document1"
                              wrdApp.ActiveDocument.SaveAs Filename:="D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\ProblemsA.docx"
                                  ' Close this new word document
                              wrdApp.ActiveDocument.Close
                              wrdApp.Quit                          '// Need to add more code to save, email or whatever you want to do...
                          
                          End Sub

                          Comment


                          • #14
                            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)


                            Code:
                            Sub FindReplaceSample()         '// To test this code, paste it into an Excel module
                            Dim my_replacements(2, 2)
                            Dim wrdApp As Object '// Word.Application    if using Early binding and reference set in project
                            Dim wrdDoc As Object '// Word.Document       same
                            Dim iTemp As Integer
                                Set wrdApp = CreateObject("Word.Application")
                                Set wrdDoc = wrdApp.Documents.Open("D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\Problems.docx")
                                Set wrdDoc1 = wrdApp.ActiveDocument
                                wrdApp.Visible = True
                                my_replacements(1, 1) = "and"
                                my_replacements(1, 2) = "xxxxxxx"
                                
                                For iTemp = 1 To 1      '       reaches breakpoint 1 OK
                                    'With wrdDoc.Range.Find
                                    With wrdDoc.Content.Find                        '// Search value in cell
                                        .Text = my_replacements(iTemp, 1)
                                        .Replacement.Text = my_replacements(iTemp, 2)
                                         '// Other parameters - check if are as required.
                                        .Forward = True
                                         '// Go replace
                                        .Execute replace:=wdReplaceAll      '    reaches breakpoint 2 here
                                    End With
                                Next
                                wrdApp.ActiveDocument.SaveAs Filename:="D:\Myfiles\H Drive\Current Data\Personal\Miscellaneous\ProblemsA.docx"
                                    ' Close this new word document
                                wrdApp.ActiveDocument.Close
                                wrdApp.Quit                          '// Need to add more code to save, email or whatever you want to do...
                            
                            End Sub
                            Last edited by peterhw; April 17th, 2012, 19:38. Reason: removed an original FAIL statement

                            Comment


                            • #15


                              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...

                              Comment

                              Working...
                              X