Hi,

I was trying to find out if the only way to call a macro in another workbook
is to explicitly use the filename and macro name:

ie: Application.Run ("Oil & Gas.xls!Refresh_Data")

or is there any way to use something like the following:

Application.Run ("""'"& ActiveWorkbook.Name & "'" & "!Refresh_Data" & """")
after I open the file with the code
--when I use this it doesn't recognize the passed variable

I am using a file search to return a list of files within a few directories
and all of these files have a macro named "Refresh_Data". My code will be
significantly shorter and easier to write if I can somehow pass the file
name/macro name from the file search and loop through them and not have to
write the same process explicitly using the filename over a hundred times.

Is it possible? Or do I have to write the longer procedure and create the
file open and run macro for each and every file?

The RefreshData codes are similair across all of these files, but each one
has some unique properties that prevent me from having one code that does the
update in the wbk that uses the file search.

Hoping someone can help me.

Thanks in advance,
Kohai

I guess you can do it. I created a string variable to be the name of the
file and the macro name and passed that to Application.Run

MacroName = "'" & LArray(Y) & "'" & "!Refresh_Data"
Application.Run (MacroName)

Thanks to any who looked.

"kohai" wrote:

Hi,

I was trying to find out if the only way to call a macro in another workbook
is to explicitly use the filename and macro name:

ie: Application.Run ("Oil & Gas.xls!Refresh_Data")

or is there any way to use something like the following:

Application.Run ("""'"& ActiveWorkbook.Name & "'" & "!Refresh_Data" & """")
after I open the file with the code
--when I use this it doesn't recognize the passed variable

I am using a file search to return a list of files within a few directories
and all of these files have a macro named "Refresh_Data". My code will be
significantly shorter and easier to write if I can somehow pass the file
name/macro name from the file search and loop through them and not have to
write the same process explicitly using the filename over a hundred times.

Is it possible? Or do I have to write the longer procedure and create the
file open and run macro for each and every file?

The RefreshData codes are similair across all of these files, but each one
has some unique properties that prevent me from having one code that does the
update in the wbk that uses the file search.

Hoping someone can help me.

Thanks in advance,
Kohai