ExcelBanter - View Single Post - Call particular Excel function from command line

semiopen

wrote:
Hi and Thanks for your great help !

Can you please let me know what the code should look like if I want to
pass in some parameters into that Macro function as well ??

Thanks !

sqlfan13 のメッセージ:

You could create a simple script, similar to the following, and execute the
script from the command line.

<Code

'Define your Excel application object
Dim objXL
dim moUseSheet

Const C_APP_PATH = "C:\folder\"
Const csUseSheet = "MySpreadsheet.XLS"

'------------------------------------------
' Create an instance of Excel
'------------------------------------------
On Error Resume Next
Set objXL = GetObject(,"Excel.Application")

If TypeName(objXL) < "Application" Then
Set objXL = CreateObject("Excel.Application")
End If

On Error GoTo 0

'Open the file
Set moUseSheet = objXL.Workbooks.Open(C_APP_PATH & csUseSheet)
objXL.Visible = True

'Run Macro
objXL.Application.Run csUseSheet & "!MacroNameToRun"
'Set window size xlNormal
objXL.WindowState = -4143
moUseSheet.Close

Set moUseSheet = Nothing
Set objXL = Nothing

<End Code

Hope this gives you something to go with.

" wrote:

Anyone can teach me how I can trigger e.g. SpreadSheet A - Module B -
Function C from Windows Command Line ? Is there such an
option/partameter ?

Here is my version (with less error trapping than sqlfan's - so be
careful):

First of all, I created a new workbook "CommandLineTest.xls" I inserted
a code module and typed:
Sub Add(x As Long, y As Long)
MsgBox x + y
End Sub

Then I wrote the following script (in Notepad - I'm low tech):
______________________________________

Dim xlApp, xlWB
Dim myArgs

Set myArgs = WScript.Arguments
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.WorkBooks.Open("C:\Documents and Settings\semiopen\My
Documents \CommandLineTest.xls")
xlApp.Visible = True
xlApp.Run "Add", myArgs(0),myArgs(1)
xlWB.Close
xlApp.Quit
Set xlWB = Nothing
Set xlApp = Nothing
Set myArgs = Nothing
__________________________________________________

I saved it as ExcelAdd.vbs - then typing exceladd 5 7 at the command
line causes the worksheet to appear with a message box displaying 12.
As written - Excel disappears as soon as you click OK. If you want it
to hang around so you can work with it more - replace the lines
xlWB.Close and xlApp.Quit by
xlApp.UserControl = True

A good free resource from microsoft.com/downloads is the Widows Script
5.6 documentation. Also - the newsgroup
microsoft.public.scripting.vbscript is a good place to find answers
regarding automating Excel from VBScript.

Hope that helps

-semiopen