CODEWITHSUNDEEP
excelvba
Alex Gordon
Alex Gordon

Reputation: 60691

running excel macro from another workbook

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?

Upvotes: 8

Views: 34433

Answers (4)

Sharunas Bielskis
Sharunas Bielskis

Reputation: 1203

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

Upvotes: 0

Alan Elston
Alan Elston

Reputation: 99

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

Upvotes: 0

PaloDravecky
PaloDravecky

Reputation: 281

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!

Upvotes: 1

Fink
Fink

Reputation: 3436

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")

Upvotes: 19

Related Questions