Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Line Arguments
I have an Excel Workbook that, when opened, runs a
particular macro. The macro requires a few arguments from the user, in order to run. Rather than have the user specify them as the macro is running, I'd like to have the user specify them when they open the Workbook (from the command line). Is there a way to specify command line arguments to Excel, and if so, how can I access them from a macro? Thanks. Michael |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Line Arguments
"Michael" wrote in message ... I have an Excel Workbook that, when opened, runs a particular macro. The macro requires a few arguments from the user, in order to run. Rather than have the user specify them as the macro is running, I'd like to have the user specify them when they open the Workbook (from the command line). Is there a way to specify command line arguments to Excel, and if so, how can I access them from a macro? Thanks. You cant use the command line arguments since Excel treats all command line parameters (other than option switches) as names of files it should open. You could use VB6 or VBscript to automate Excel and then run the macros with the required data using the Application.Run syntax Sub Main Dim objExcel As Object Set objExcel = CreateObject( "Excel.Application") objExcel.application.workbooks.open "c:\YourDirectory\YourWorkBook.XLS" ' run macro from workbook objExcel.application.Run ("YourWorkbook.XLS!.Yourmodule.YourMacro" ,YourArg1, YourArg2) Set objExcel = Nothing 'clear object from memory End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Line Arguments
You can use API function GetCommandLine. Here is an example form archive:
http://www.google.pl/groups?hl=pl&lr...adoo.fr&rnum=6 -- Pozdrowienia |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Line Arguments
Don't know who your target audience is, but I believe Laurent later reported
that this method does not work in NT and its offspring. -- Regards, Tom Ogilvy "pxd74" wrote in message ... You can use API function GetCommandLine. Here is an example form archive: http://www.google.pl/groups?hl=pl&lr...adoo.fr&rnum=6 -- Pozdrowienia |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Command Line Arguments
Don't know who your target audience is, but I believe Laurent later
reported that this method does not work in NT and its offspring. Yes. It's true, but example below works perfect in NT system. Option Explicit Private Declare Function GetCommandLine Lib "kernel32" Alias _ "GetCommandLineA" () As Long Private Declare Function lstrcpy Lib "kernel32" Alias "lstrcpyA" _ (ByVal lpString1 As String, ByVal lpString2 As Long) As Long Sub Auto_open() Dim CmdLine As String 'command-line string Dim Args() As String 'array for storing the parameters Dim ArgCount As Integer 'number of parameters Dim Pos1 As Integer, Pos2 As Integer Dim PosSpace As Integer, PosSlash As Integer Dim bEnd As Boolean Dim i As Long CmdLine = CommandLine Pos1 = InStr(1, CmdLine, "/e") + 2 'search "/e" If Pos1 = 2 Then Exit Sub Do While bEnd = False PosSlash = InStr(Pos1, CmdLine, "/") + 1 Pos1 = PosSlash PosSpace = InStr(Pos1, CmdLine, " ") PosSlash = InStr(Pos1, CmdLine, "/") If PosSlash = 0 Then Pos2 = PosSpace Else Pos2 = WorksheetFunction.Min(PosSpace, PosSlash) End If ArgCount = ArgCount + 1 ReDim Preserve Args(ArgCount) Args(ArgCount) = Mid(CmdLine, Pos1, Pos2 - Pos1) If PosSlash PosSpace Or PosSlash = 0 Then bEnd = True End If Loop For i = 1 To ArgCount MsgBox "Argument " & ArgCount & " : " & Args(i) Next End Sub Private Function CommandLine() As String Dim lpStr As Long, i As Long Dim buffer As String lpStr = GetCommandLine() buffer = Space$(512) lstrcpy buffer, lpStr buffer = Left$(buffer, InStr(buffer & vbNullChar, vbNullChar) - 1) CommandLine = buffer End Function You should pass arguments in this way: - path to excel.exe with quotation mark - space - /e - other arguments followed by slash (can't contain space) - space - file to open with quotation marks For example: "C:\Program Files\Microsoft Office\Office10\EXCEL.EXE" /e/print/something/argument3 "C:\Zeszyt1.xls" -- Regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command-line switches | Excel Discussion (Misc queries) | |||
Run Macro from Command line | Excel Discussion (Misc queries) | |||
What are the possible command-line arguments in Excel? | Excel Discussion (Misc queries) | |||
Q: command line in OE | Excel Discussion (Misc queries) | |||
DOS Command Line | Excel Programming |