running excel macro from another workbook

Question

I have a macro that is on a server. I need to be able to run it from different workstations that connect to this server.

Currently I am doing:

Application.Run ("L:\database\lcmsmacro\macro1.xlsm!macro_name") 

The error message I am getting is "The macro may not be available in this workbook #1004"

I have already made sure that my security settings are set on the lowest level.

How do I run a macro from another workbook which is hosted on a different server?

would using add-ins help me?

Answer 1

This error also shows up when there are duplicate macro names in the remote workbook, e.g. two macros named "macro_name". Took me a while to find out!

Answer 2

I think your syntax is missing the single quote characters:

Application.Run ("'L:\database\lcmsmacro\macro1.xlsm'!macro_name") 

Then, if you needed to pass parameters to it the syntax would be like this:

Application.Run ("'L:\database\lcmsmacro\macro1.xlsm'!macro_name","param1","param2") 

Answer 3

Generally in Names a single ‘ is required if you have a space or punctuation in a name so that Excel does not get confused thinking that the space is a deliberate separation, such as in separating arguments from a Method. In some cases Excel will insist on them. Usually it does no harm to include them, even if they are not needed, for example when no spaces are present in names. Sometimes Excel will then take them out if they are not needed. http://www.eileenslounge.com/viewtopic.php?f=27&t=25599

Answer 4

If the macro you need to find relative macro path by using workbook path from which you run macro and you need to run several macros from the array list, the code below will help:

Dim relativePath As String, programFileName As String
Dim selectedProgramsFiles() As String, programsArrayLastIndex As Byte, I As Byte

For I = 0 To programsArrayLastIndex 'Loop through all selected programs
    programFileName = selectedProgramsFiles(I)
    relativePath = ThisWorkbook.Path & "\" & programFileName
    Workbooks.Open Filename:=relativePath

    Application.Run ("'" & relativePath & "'!ModuleName.Main")

    Workbooks(programFileName).Activate
    ActiveWorkbook.Close SaveChanges:=False
Next I 'For I = 0 To programsArrayLastIndex 'Loop through all selected program