Calling Macro?

[robfo0]

Hi everyone, I had a problem calling a macro in another book from a macro in my current book before, and got help here with it. I looked up the response, and tried the solution that worked before, but this time i cant get it to work. Help!

This is the code I am trying to run:
Dim FromFile As Boolean

FromFile = False

For Each TestBook In Workbooks
If ModuleExists("ShutitDown") = True Then
FromFile = True
nameworkbook = TestBook.Name
Exit For
End If
Next

Application.DisplayAlerts = False
Application.Run (nameworkbook) & "!ShutitDown.CloseSave"
Application.DisplayAlerts = True


The code I'm trying to run is in another open workbook. The procedure is named "CloseSave", and it is located in a module named "ShutitDown". I tried using Application.Run (nameworkbook) & "!CloseSave" instead, but it still is saying it cannot locate the module.

I assure you, there is a modeule by this name in a currently open workbook.

Thanks

[ This Message was edited by: robfo0 on 2002-05-14 16:55 ]

[Mark O'Brien]

Would this work?

Application.Run nameworkbook & "!ShutitDown.CloseSave" 

or

Application.Run (nameworkbook & "!ShutitDown.CloseSave")

I don't have Excel to test it.

HTH

[robfo0]

Mark,
thanks for the response, sorry for the delay, been out of the office for a few days.

Neither of these work, still having the same problem of macro not found

Any other ideas?

[jkpd2000]

give the whole path for the workbook
... just a thought

[robfo0]

No that didnt work either

[Russell Hauf]

Can you post your ModuleExists procedure? I think your problem is with the "ThisWorkbook" contained in that procedure. You should probably re-write it something like this:

Option Explicit 



Sub TestCall() 

    Dim strMod As String 

    Dim strProc As String 

    Dim strWbkName As String 

    Dim wbk As Workbook 

    

    

    strMod = "basTest" 

    strProc = "Testupperleft" 

    

    For Each wbk In Application.Workbooks 

        If ModuleExists(wbk, strMod) Then 

            Application.Run wbk.Name & "!" & strMod & "." & strProc 

            Exit For 

        End If 

    Next wbk 

    

End Sub 





Function ModuleExists(wbk As Workbook, ModuleName As String) As Boolean 

On Error Resume Next 

    ModuleExists = Len(wbk.VBProject.VBComponents(ModuleName).Name) <> 0 

End Function 

Hope this helps,

Russell

--> So my module is named "basTest", and the procedure I want to run is called "Testupperleft"

Also notice the wbk in the ModuleExists procedure - once in the arguments of the function, and again inside the function, this time in place of ThisWorkbook.


[ This Message was edited by: Russell Hauf on 2002-05-17 16:49 ]

[Nimrod]

Hi Guys :
I don't know if this will help or not but I got this code to work for me ... Please note that My macro has a capital "I" in the name.

Sub SaveAndClose2()
Dim FromFile As Boolean
Dim TestBook As Workbook
FromFile = False

For Each TestBook In Workbooks
        For Each VBComp In TestBook.VBProject.VBComponents
         'MsgBox (VBComp.Name)
         If VBComp.Name = "ShutItDown" Then
            FromFile = True
            nameworkbook = TestBook.Name
            
            Exit For
         End If
        Next VBComp
Next

Application.DisplayAlerts = False
Application.Run (nameworkbook) & "!ShutItDown.CloseSave"
Application.DisplayAlerts = True

Also found an interesting article that you may care to read on the subject:
http://<a href="http://www.cpearson....el/vbe.htm</a>

[robfo0]

This is a follow up post. Thanks everyone for your posts, but i tried them ALL and none worked. After weeks of trying to figure it out! alas!!! Here is the way that worked for me:

Application.Run "'" & nameworkbook & "'!ShutitDown.CloseSave"

I guess it needs the stupid ' !!!

Hope this helps someone else, because it was hell trying to figure it out