[SOLVED] Calling Excel macro remotely?

I have a task I would like to automate. It only takes a few minutes to run,
but it has to run on one particular machine on our network (hardware key),
and babysitting it through the process takes longer than the process itself.
The data generated is then used on a different machine, so there's some
walking back and forth.

What I would like to do is simply call the macro from the main spreadsheet
on my machine. Is there any way to do this? I suppose I could use an autoexec
macro in the spreadsheet, but that seems a little less than perfect (it would
be nice to be able to call one of n different versions of the macro, some
only update a few items from the whole set). I don't see a "run this macro"
command-line switch though.

Also, what is the best way to spool it? I have seen code that will run a
file on the command line, but it seems that it returns before the file has
completed running. Since I'm waiting on the data being generated before I can
continue, I'd need to pause until it completed.

I don't know how to do this. To call a macro from a different workbook, both workbooks need to be open in the same instance of Excel.

Consider, for example, what happens when you have two instances of Excel open on the same PC. You can't call macros from an open workbook in instance 1 from instance 2, right? (OK, you could use the "send keys" command perhaps.)

I wish I had better news. Perhaps someone will explain why I'm wrong.

- Pete

if you can open the file on your machine, you should be
able to run the macro from your machine. i fail to
understand why it has to run on a perticular machine.
otherwise you are looking at some heavy duty code or 3
party software like lotus notes. but that would still put
the running of the macro on your machine. excel cannot
send a command to another machine and tell IT to run a
macro.

>-----Original Message-----
>I have a task I would like to automate. It only takes a
few minutes to run,
>but it has to run on one particular machine on our
network (hardware key),
>and babysitting it through the process takes longer than
the process itself.
>The data generated is then used on a different machine,
so there's some
>walking back and forth.
>
>What I would like to do is simply call the macro from the
main spreadsheet
>on my machine. Is there any way to do this? I suppose I
could use an autoexec
>macro in the spreadsheet, but that seems a little less
than perfect (it would
>be nice to be able to call one of n different versions of
the macro, some
>only update a few items from the whole set). I don't see
a "run this macro"
>command-line switch though.
>
>Also, what is the best way to spool it? I have seen code
that will run a
>file on the command line, but it seems that it returns
before the file has
>completed running. Since I'm waiting on the data being
generated before I can
>continue, I'd need to pause until it completed.
>.
>

"[email protected]" wrote:

> if you can open the file on your machine, you should be
> able to run the macro from your machine. i fail to
> understand why it has to run on a perticular machine.

As I stated, the code in question is protected by a hardware key on that
machine. It will not run on any other machine. However that code represents
perhaps 2% of the overall task, and that machine is very slow. Therefore I
would like to run that 2% on that machine, and the other 98% on my own.

> otherwise you are looking at some heavy duty code or 3
> party software like lotus notes. but that would still put
> the running of the macro on your machine. excel cannot
> send a command to another machine and tell IT to run a
> macro.

Which is why I wrote my question.

Maury

I wouldn't give up yet. There are such things as NetDDE and DCOM that
support this I believe. I don't know anything about them, but they may
provide the facilities you need. They both have to do with applications
that support Automation. However, if the application on the other machine
doesn't support COM, can you start it from that machine using excel. Then
perhaps you could communicate with Excel to start the application. As far
as using the file produced, your code can check the status of the file,
start the application, then periodically check the file to see if a new one
has been produced.

Look at CreateObject in VBA help in xl2000 or later. Note that the last
argument is ServerName.

CreateObject(class,[servername])


--
Regards,
Tom Ogilvy

"Maury Markowitz" <[email protected]> wrote in
message news:[email protected]...
> "[email protected]" wrote:
>
> > if you can open the file on your machine, you should be
> > able to run the macro from your machine. i fail to
> > understand why it has to run on a perticular machine.
>
> As I stated, the code in question is protected by a hardware key on that
> machine. It will not run on any other machine. However that code
represents
> perhaps 2% of the overall task, and that machine is very slow. Therefore I
> would like to run that 2% on that machine, and the other 98% on my own.
>
> > otherwise you are looking at some heavy duty code or 3
> > party software like lotus notes. but that would still put
> > the running of the macro on your machine. excel cannot
> > send a command to another machine and tell IT to run a
> > macro.
>
> Which is why I wrote my question.
>
> Maury

"Tom Ogilvy" wrote:

> Look at CreateObject in VBA help in xl2000 or later. Note that the last
> argument is ServerName.

Thanks Tom, it looks like this is what I need. I'm having some problems
trying to figure out what security settings are needed to allow it to work
(right now everything gives me a permissions error) but other than that it
seems simple enough.

Maury

I have never used it or played with it - so I can't be of much help there.
Sorry.

You might search on MSDN to see if there is any information

http://msdn.microsoft.com


--
Regards,
Tom Ogilvy

"Maury Markowitz" <[email protected]> wrote in
message news:[email protected]...
> "Tom Ogilvy" wrote:
>
> > Look at CreateObject in VBA help in xl2000 or later. Note that the last
> > argument is ServerName.
>
> Thanks Tom, it looks like this is what I need. I'm having some problems
> trying to figure out what security settings are needed to allow it to work
> (right now everything gives me a permissions error) but other than that it
> seems simple enough.
>
> Maury

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> I wouldn't give up yet. There are such things as NetDDE and DCOM that
> support this I believe. I don't know anything about them, but they may
> provide the facilities you need. They both have to do with applications
> that support Automation. However, if the application on the other machine
> doesn't support COM, can you start it from that machine using excel. Then
> perhaps you could communicate with Excel to start the application. As far
> as using the file produced, your code can check the status of the file,
> start the application, then periodically check the file to see if a new
one
> has been produced.
>
> Look at CreateObject in VBA help in xl2000 or later. Note that the last
> argument is ServerName.
>
> CreateObject(class,[servername])
>
>

DCOM should definitely be able to do the trick. I have only done this from
C++. I shall try to find relevant articles to help you get started.

/ Fredrik