none
VBA - Excel Run Word Macros Listed in Column RRS feed

  • Question

  • Hi friends,

    Happy holidays!

     

    I am trying to run some word macros from excel.

     

    I know how to run 1 macro, but there are a lot of documents and macros, so I decided to make a list like this, to make it easier for me to track my word macros.

     

     

     

    And then when I click on each cell it will open the document and run the macro in column B.

     

    Now I have done a lot of wrong code so now I need to go back to scratch again.


    I got started on the first part -

        Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    
    
            oMacro As String
            On Error Resume Next
            If Not Intersect(ActiveCell, Range("A2:A10")) Is Nothing Then
    
    
            Dim objWord As Object
            Set objWord = CreateObject("Word.Application")
            objWord.Visible = True
    
    
            objWord.Documents.Open Range("ActiveCell").Text
    
    
            'Application.Run oMacro   ' Run the Macro in Column B
    
            End If
    
            End Sub
    

     

    I am not sure if this is the right way but I would like to click a cell in Column B that will run the listed macro from the corresponding document filepath listed.

     

    There was an error on the application run macro bit - but it was a bit confusing, also word opened up but nothing happened, so I have come for help.

    Please do advise and thank you


    Cheers Dan :)

    Thursday, December 29, 2016 2:41 PM

Answers

  • Dan,

    If I'm right you want to run your macro to do something with the Word-document.
    Then it is possible how you want that.
    I only changed from Worksheet_Change to Worksheet_BeforeDoubleClick, but that is only a choice.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim oMacro As String
        Dim objWord As Object
        On Error Resume Next
        If Not Intersect(ActiveCell, Range("A2:A10")) Is Nothing Then
            Set objWord = CreateObject("Word.Application")
            objWord.Visible = True
            objWord.Documents.Open Target.Text
            oMacro = Target.Offset(0, 1)
            objWord.Run oMacro
        End If
    End Sub
    
    

    Jan

    • Marked as answer by Dan_CS Thursday, December 29, 2016 4:17 PM
    Thursday, December 29, 2016 3:54 PM

All replies

  • I don't think you can run a macro stored in a Word document from Excel unless there is a reference (in Tools > References...) to the VBA project in that document. But that is not realistic when you open and close documents on the fly (theoretically, it's possible to set a reference dynamically, but that always causes problems when I try it). So I fear you're out of luck...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, December 29, 2016 3:13 PM

  • Hello Hans,

    :)

    I was hoping to substitute

    Something like below - that uses the excel sheet as a reference to do the magic, but as you explianed it wont work.

     

     
    
    ' Bad Pseudocode below
    
    Dim WordApp As Object
    
    On Error Resume Next
    
    Set WordApp = GetObject(, "Word.Application")
    
    On Error GoTo 0
    
     
    
    If WordApp Is Nothing Then
    
    Set WordApp = CreateObject("Word.Application")
    
    End If
    
     
    
    If Cell A2 pressed
    
    WordApp.Visible = true
    
    WordApp.documents.Open "A2"    'filepath
    
    WordApp.Run "B2"               ' macro name in B2
    
     
    
    'Cell A3 pressed
    
    WordApp.documents.Open "A3"    
    
    WordApp.Run "B3"  
    
     
    
     

                                              

    The only alternative is to hardcode each one  - and that will be gazillions of macros.

     

    'The Normal way  - 
    
    WordApp.documents.Open "C:\Users\Dan\Desktop\Doc3.docm"    
    
    WordApp.Run "Macro3"  

    I have to keep switching from excel to word, so it became a bit long so i thought i would make it simpler by making a list  -

    Excel can open the word doc nicely  - I wish it would be able to reference the macro name :(

    let me have  a think about how i can change or adapt  this idea


    Cheers Dan :)

    Thursday, December 29, 2016 3:44 PM
  • Dan,

    If I'm right you want to run your macro to do something with the Word-document.
    Then it is possible how you want that.
    I only changed from Worksheet_Change to Worksheet_BeforeDoubleClick, but that is only a choice.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        Dim oMacro As String
        Dim objWord As Object
        On Error Resume Next
        If Not Intersect(ActiveCell, Range("A2:A10")) Is Nothing Then
            Set objWord = CreateObject("Word.Application")
            objWord.Visible = True
            objWord.Documents.Open Target.Text
            oMacro = Target.Offset(0, 1)
            objWord.Run oMacro
        End If
    End Sub
    
    

    Jan

    • Marked as answer by Dan_CS Thursday, December 29, 2016 4:17 PM
    Thursday, December 29, 2016 3:54 PM
  • Hello my friends,

     

    Now I must admit I'm not the best when it comes to  explaining things that’s why I try to add a picture.

     

    These macros are basic message boxes and mini userforms and I need to see them to make sure they are not malfunctioning -  but then I had to keep opening each document manually then finding the macro and it became very tedious losing a mesage box or other such component buried somewhere.

     

    As an ardent  list person  -  I love organising things , but excel is really hard to work with word as Han's explained.

     

    Thank you Hans and Jan for the nice code, it worked yay!

     

    I was trying to do this but I got absorbed in some very advanced stuff and couldn’t make heads or tails in the end, but simplicity is the best

     

    Enjoy the holidays!

    🙂



    Cheers Dan :)


    • Edited by Dan_CS Thursday, December 29, 2016 4:18 PM
    Thursday, December 29, 2016 4:17 PM
  • This is  an interesting question.  The following worked for me.

    Sub RunAllWordMacros()
    
    Dim wdApp As Object, newDoc As Object
    Dim strFile As String
    Dim rng As Range, cell As Range
    Dim wordApp As Object
    
    Set rng = Range("A2:A4")
    For Each cell In rng
    
        Set wordApp = CreateObject("Word.Application")
        wordApp.Documents.Open Filename:=cell.Value
        wordApp.Visible = True
        wordApp.Run "AutoOpen"
        
        wordApp.ActiveDocument.Close SaveChanges:=wdSaveChanges
        wordApp.Quit
        Set wordApp = Nothing
    
    Next cell
    End Sub

    My setup looks like this.  As you should expect, 'AutoOpen' is the name of the Macro.


    MY BOOK


    • Edited by ryguy72 Monday, January 16, 2017 3:59 PM
    Monday, January 16, 2017 3:58 PM