Run add-in code when Excel starts

[RB Smissaert]

Using Excel 2000 to 2003.
I would like to run a Sub in an Excel add-in when Excel starts, but only
when
this is specified with a commandline parameter.
Excel will be started by the Windows Task Scheduler.
What I could do is add a workbook to the Scheduler commandline and put some
code in this workbook that will run with the Open event. This is a bit
clumsy though
as I don't need the extra workbook.
Thanks for any advice.

RBS

 

[Bob Phillips]

RB,

Alternative suggestion. Could you set an environment variable and test that
in the add-in?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RB Smissaert]

Bob,

Yes, I could and that might be the easiest.

I have one other problem.
I set the task scheduler via a dll written by Eduardo Morcillo:
http://www.mvps.org/emorcillo/vb6/grl/index.shtml

This Sub makes the task and it looks fine, but the task can't run:

Sub test()

Dim oSchedule As TaskScheduler2.Schedule
Dim oTask As TaskScheduler2.Task
Dim oTrigger As TaskScheduler2.Trigger

Set oSchedule = New Schedule
Set oTask = oSchedule.CreateTask("ReportSchedule")
Set oTrigger = oTask.Triggers.Add

With oTrigger
.TriggerType = Once
.BeginDay = "28/12/2004"
'.EndDay = "30/12/2004" 'this doesn't seem to do anything
.StartTime = "12:48:00"
.Update
End With

With oTask
.ApplicationName = "Excel.exe"
.CommandLine = "C:\Schedule.xls"
.WorkingDirectory = Chr(34) & Application.Path & Chr(34)
.Flags = RunOnlyIfLoggedOn
'.Flags = DeleteWhenDone 'doing this will clear the previous flag
.Save
End With

End Sub

The reason is the string in the Task Scheduler Run textbox.
This string will be:
C:\PROGRA~1\MICROS~2\Office10\EXCEL.EXE C:\Schedule.xls
with the above code.
If I alter this manually to:
"C:\PROGRA~1\MICROS~2\Office10\EXCEL.EXE" "C:\Schedule.xls"
It will run.
How though do I get this string with my code?
Tried all kind of constructions, but nil works.

Any ideas?


RBS

 

[Guest]

its much easier if your scedul;er riuns a vb script program
very simple to do.

open notepad, add this example,

option explicit
dim passparam
dim xl as object
dim wb as object

Set xl = CreateObject("Excel.Application")

xl.workbooks.open "s:\Myworkbook.xls"


if wscript.arguments.count = 0 then
' no sub to call
else
passparam = Wscript.Arguments.Item(0)
select case passparam
case "A"
wb.run "MySub","A"
case "B"
wb.run "MySub","X"
Case else
wb.run "someothersub"
end select

end if

now save the text file with a VBS extension, eg MyExcelTest.VBS

VB Scripts are built as part of XP . example
put MSGBOX "hello world!" in notepade & save to your desktop sa HW.VBS
now double click the HW.VBS icon on the desktop...hey presto!

With the code sample, you could call this like:
MyExcelTest.VBS "A"

in which case the pass parameter is the letter A. the rest is self evident I
think

HTH
Patrick Molloy
Microsoft Excel MVP

 

[Bob Phillips]

RB,

Not tested it myself, but was one of your attempts?

.CommandLine = """C:\Schedule.xls"""


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RB Smissaert]

Yes, tried that one, but didn't work.
It seems it needs the quotes around the
full application path.

RBS

 

[RB Smissaert]

Thanks; will try that.

RBS

its much easier if your scedul;er riuns a vb script program
very simple to do.

open notepad, add this example,

option explicit
dim passparam
dim xl as object
dim wb as object

Set xl = CreateObject("Excel.Application")

xl.workbooks.open "s:\Myworkbook.xls"


if wscript.arguments.count = 0 then
' no sub to call
else
passparam = Wscript.Arguments.Item(0)
select case passparam
case "A"
wb.run "MySub","A"
case "B"
wb.run "MySub","X"
Case else
wb.run "someothersub"
end select

end if

now save the text file with a VBS extension, eg MyExcelTest.VBS

VB Scripts are built as part of XP . example
put MSGBOX "hello world!" in notepade & save to your desktop sa HW.VBS
now double click the HW.VBS icon on the desktop...hey presto!

With the code sample, you could call this like:
MyExcelTest.VBS "A"

in which case the pass parameter is the letter A. the rest is self evident
I
think

HTH
Patrick Molloy
Microsoft Excel MVP

 

[RB Smissaert]

Tried it, but no success sofar.
For starters it seems you can't do this:
dim xl as object
has to be:
dim xl

Secondly, what would the values be to go here:

With oTask
.ApplicationName = "Excel.exe"
.CommandLine = "C:\Schedule.xls"
.WorkingDirectory = Chr(34) & Application.Path & Chr(34)
.Flags = RunOnlyIfLoggedOn
'.Flags = DeleteWhenDone 'doing this will clear the previous flag
.Save
End With

RBS

 

[RB Smissaert]

Got Excel to start with this:

AddScheduleTask "5:00", _
"""C:\Program Files\Microsoft
Office\Office10\Excel.exe""", , , True, False

But started the Windows installer and popped up a dialog.
If I set RunInteractive = False it will run invisible and I don't want that.

It doesn't seem this is a simple thing to do.


RBS

 

[Patrick Molloy]

once instantiated...
xl.visible = True

Got Excel to start with this:

AddScheduleTask "5:00", _
"""C:\Program Files\Microsoft
Office\Office10\Excel.exe""", , , True, False

But started the Windows installer and popped up a dialog.
If I set RunInteractive = False it will run invisible and I don't want
that.

It doesn't seem this is a simple thing to do.


RBS

 

[Bob Phillips]

An other attempt

.ApplicationName = Application.Path & "\Excel.exe"
.CommandLine = """C:\Schedule.xls"""

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RB Smissaert]

Bob,

I think I tried that one, but tried it again and indeed didn't run again.
I am not sure why this is as the string in the Run box looks the
same as a string that is manually set and does run.
Maybe I need to alter the underlying file or registry setting that
stores the information about the tasks.
This turns out the be a real nuisance and it is a shame as the .dll
file works otherwise really well.
Still not given up.

RBS

 

[Bob Phillips]

RB,

I found that it looks the same, and so I thought it would work. Thinking
about it, there was no reason why the former shouldn't run, as the only
reason you need the quotes is to accommodate spaces in the application path,
and by using dos paths, it had gotten around that.

As you say, it is a shame, as the dll is very neat and tidy. Tomorrow, I
will try it properly, with a scheduled task and all, and see if I can get
around it.

If you crack it, please post back, to the NG or offline.

Regards

Bob

 

[RB Smissaert]

Bob,

Will do. I have sent an e-mail to Eduardo M. Hopefully he will know how to
do this.

RBS