use a general macro for different files

Posted by camille on May 08, 2001 3:14 PM

Hi,

I've got a general macro in an XLStart file that I run for different files. There is also a small macro (macro 1) different in each file. I run the general macro and call the small macro as follows:

Application.Run "File.xls!macro1"

The problem is that I have to specify the name of the File whereas I would like to use something like ActiveWorkbook or ActiveFile,that could work for the file I'm working on, no matter which one it is.

How can I write that?

Thanks

Camille.

Posted by Sean on May 08, 2001 4:25 PM

Can't remember whether I've got round this before in VB, but here's a work around...

in cell A1 on each of your files add the following formula

=MID(CELL("filename",A1),(SEARCH("[",CELL("filename",A1)))+1,(SEARCH("]",CELL("filename",A1)))-(SEARCH("[",CELL("filename",A1)))-1)

This will populate the filename excluding and directory etc...

next in vb you can reference the cell value using

f_name = ActiveSheet.Range("A1").Value
macrname = f_name & "!macro1"
Application.Run macrname

I know there must be a snazzy way of doing this but at least this one works for the time being.

Sean
s-o-s@lineone.net