Excel command line

[Jef Gorbach]

Normally start Excel via the Window's-Run command box.

Approx 1/day, use a simple form-creation macro from Personal.xls via a
custom button which I would like to call from the command line like Word
does. (IE: windows-R excel /mMACROenter)

However Help/Google imply Excel's equivalents to Word's /mMACRONAME switch
are auto_open or workbook _open, neither of which apply in this instance
since the macro is part of Personal.xls and shouldn't run everytime Excel
opens. (BTW, using Office97)

Are the solutions really:
1. leaving well enough alone
2. saving the results to a file and cluttering the desktop with yet
another icon
3. recoding in Word
???

 

[Dave Peterson]

#4. Open a workbook that runs the macro in personal.xls
#5. Use a VBS script to start excel and run your macro.

Something like:

Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = true
xlapp.workbooks.add
xlapp.workbooks.open "c:\yourpathto\personal.xls"
xlapp.run "Personal.xls!macronamehere"

Save this text file as something.vbs.

Then put a shortcut on your desktop to run this (or run it directly from the
windows start button|run box)

(Do you need to have a specific workbook open when the userform starts?

 

[Jef Gorbach]

Dave,

Thanks for the work-around - works great for the specific case.

Just recently starting exploring VBS so tried passing the macroname as a
command line argument based upon some website examples; however the "set
macro =" line generates the "wrong number of arguments or invalid property
assignment" runtime error -- any idea why??

' Filename: excel.vbs
' Overcomes Excel inability to run macros from the command line
' Usage: [CScript | WScript] excel.vbs macros
dim macro
set macro = "Personal.xls!" & WScript.Arguments
Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = true
xlapp.workbooks.add
xlapp.workbooks.open "C:\Program Files\Microsoft
Office\Office\Xlstart\personal.xls"
'xlapp.run "personal.xls!UPS" 'works for specific case
xlapp.run macro

 

[Dave Peterson]

This worked ok for me.

Dim macro
Dim XLApp
Dim XLWkb

Set XLApp = CreateObject("Excel.Application")

xlapp.visible = true
xlapp.workbooks.add
xlapp.workbooks.open "c:\yourpath\XLSTART\personal.xla"

If wscript.arguments.count = 0 Then
'do nothing
Else
macro = "Personal.xla!" & WScript.Arguments.item(0)
xlapp.run macro
End If

========
Yeah, I use personal.xla and I don't think I'd name the VBS as Excel.VBS. It
would confuse me and I'm not sure what would happen if windows found excel.exe
first.

Dave,

Thanks for the work-around - works great for the specific case.

Just recently starting exploring VBS so tried passing the macroname as a
command line argument based upon some website examples; however the "set
macro =" line generates the "wrong number of arguments or invalid property
assignment" runtime error -- any idea why??

' Filename: excel.vbs
' Overcomes Excel inability to run macros from the command line
' Usage: [CScript | WScript] excel.vbs macros
dim macro
set macro = "Personal.xls!" & WScript.Arguments
Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = true
xlapp.workbooks.add
xlapp.workbooks.open "C:\Program Files\Microsoft
Office\Office\Xlstart\personal.xls"
'xlapp.run "personal.xls!UPS" 'works for specific case
xlapp.run macro

#4. Open a workbook that runs the macro in personal.xls
#5. Use a VBS script to start excel and run your macro.

Something like:

Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = true
xlapp.workbooks.add
xlapp.workbooks.open "c:\yourpathto\personal.xls"
xlapp.run "Personal.xls!macronamehere"

Save this text file as something.vbs.

Then put a shortcut on your desktop to run this (or run it directly from the
windows start button|run box)

(Do you need to have a specific workbook open when the userform starts?

 

[Jefgorbach]

kewl - works!
"excel.vbs" was a working title.
Saving shortcuts (such as e.vbs) in c:\windows\command puts them on the
command Path and thus accessable via the Run box with minimal
keystrokes, reducing final useage to "windows-r e.vbs macroname enter"
which is much more convenient than clearing screen to the desktop to
locate/double-click an icon; at least for me.

 

[Dave Peterson]

Maybe just one shortcut...

Dim macro
Dim XLApp
Dim XLWkb
Dim MacroName

If wscript.arguments.count = 0 Then
macroName = InputBox ("type a macro name")
Else
macroname = WScript.Arguments.item(0)
end If

If macroname = "" Then
'do nothing
Else
macro = "Personal.xla!" & macroname

Set XLApp = CreateObject("Excel.Application")

xlapp.visible = true
xlapp.workbooks.add
xlapp.workbooks.open "c:\yourpath\XLSTART\personal.xla"

xlapp.run macro

End If


And just have the .vbs prompt you for the macro name.