macro names not consistent?

[alex999]

Hi,

I have a subroutine written, to be run on various different machines.
This is to be run automatically as part of an overnight procedure, so
have it being called from a VBscript file. Of the form


Code
-------------------
Set xlObj = CreateObject("Excel.application")
xlObj.Workbooks.Open "c:\trinity\exchange.xls"
xlObj.Run "ExchangePriceImport"
xlObj.ActiveWorkbook.Saved = True
xlObj.ActiveWindow.Close
xlObj.Qui
-------------------


However, on certain machines this code fails, saying the macro canno
be found.
On looking in the workbook under tools>macros, the name seems to be o
the form

exchange.xls!sheet1.ExchangePriceImport

However on other machines, opening the file shows the macro in the lis
as ExchangePriceImport only.

What is casuing this, and how can I stop the name being altered? a
this is stopping processes runing on certain systems.

Excel version is 2000 and they're all running the same service pack.

Thank

 

[keepITcool]

there is a significant difference between placing the macro in a sheet's
code module or placing it in a normal code module.

imho: code that is NOT strictly related to events or controls should never
be placed in an object module.

SO I suggest that you check your files, and for those files where the
macro shows up as sheet1.MyMacro you'll have to move the procedure to
a normal module.

moreover...
if the code in all those files is the same...
adapt your code to work independent of the specific files
(either make it work with a workbook variable or with the activeworkbook)
remove the macro's from your workbooks and include the adapted procedure
in your vbs script/file.

moreover...
if all your users use the same file anyway...
(exchange rates suggest a readonly scenario)
....
why not store it in 1 networklocation, mark it as readonly,
and have the users share it?

(be sure to remove the readonly attribute in your update script





keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >