Getting command-line args in a macro

[jclu]

Hi, I'm trying to create a batch file to automatically open an excel workbook and run the macro so the user doesn't have to baby-sit the process. In my module, I'm using the Auto_open sub to call the rest of the macro. However, there are times where I or the user want to open up the workbook manually, and those times we don't want to the macro to start running automatically. To distinguish between the two purposes, I want to read the command-line arguments and if a particular argument exists, then continue running the macro automatically; and if not, then exit the Auto_open sub.

I can't seem to figure out how to read the command line arguments. I'm using Excel 2000, and I tried using both the GetCommandLineA function in the library Kernel32 - the example code is found here: http://j-walk.com/ss/excel/eee/eee002.txt under the "Power Programming Technique" section. I also tried using the Command() function, but even the help mentions how this function doesn't work in MS Office.

Is there a way I can check for the existence of command line args, and if they exist, get them so I can parse them and check for a specific type of arg? Thanks for the help.

 

[Andrew Poulsom]

I can't help with getting the command line args, but if you hold down Shift while opening the workbook, the Autpen procedure won't run.

 

[Joe Was]

To prevent macros from automatically running at the start of Excel or a Workbook, hold down SHIFT while you start Excel. This works best if you build short cuts for applications, where you start Excel and a Workbook from the ShortCut Icon.
Note: If you start Excel from the Office Shortcut Bar, click the Microsoft Excel button on the Office Shortcut Bar first, and then immediately hold down SHIFT while Excel starts.

In the past you can also load Excel with a start switch which stops auto-macros from loading, but the new versions have greatly shortened the list of switches, to get away from batch processing, the only ones left are and used:
On the Start menu, point to Programs, point to Accessories, and then click Windows Explorer.
In Windows Explorer, go to \Program Files\Microsoft Office\Office11, right-click EXCEL.exe, and click Create Shortcut.
Right-click the shortcut you created, click “Properties”, and then modify the path in the Target box with switches from the following table.
Note: Switches are not case sensitive: /O is the same as /o. In some scenarios, you can use more than one switch at a time. If you use more than one switch at a time, you must separate the switches with spaces. For example: /o /s
To start Excel and Type
Prevent display of the Excel startup screen and a new blank workbook /e or /embedded
Create a new workbook that contains a single macro sheet /m
Force Excel to re-register itself /o
Specify the working folder as the active working folder instead of the default working folder /p workbook path
Open a specific workbook as read-only (read-only: A setting that allows a file to be read or copied, but not changed or saved. If you change a read-only file, you can save your changes only if you give the document a new name.)
/r workbook path\file name
Specify Office Safe Mode /s, /safe, or /safemode
Open and close Excel to rewrite all registry keys and reassociate Excel with all Excel files, such as workbooks and charts /regserver
Open and close Excel to unregister Excel /unregserver
Click OK.
Move the shortcut to \Windows\Documents and Settings\Start Menu.


You can change the security level?
But, then no macros will run.

You can insert a startup macro that renames or give you control of the other macros?

Like: Application.VBE.ActiveVBProject.Name = "myCode"
Then put all the startup code you want to control under the new application.

 

[jclu]

But is there no way to check for the existence of command-line arguments (for when the workbook is opened via a batch script, as opposed to a user double-clicking on the file icon in Windows Explorer), and if args exist, then getting them and dealing with them as appropriate?

Btw, the SHIFT thing doesn't work for me if my Security Level is set to Low. If it's set to Medium, then when I double-click the file icon, and Excel starts up and opens the workbook, it asks me if I want to Enable or Disable macros. At that point, if I press and hold SHIFT and then select Enable macros, the Auto_open sub doesn't run. If my Security Level is set to Low, and I press and hold SHIFT and then double-click the file icon, even while holding SHIFT throughout the startup of Excel and the workbook, the Auto_open sub still runs!

 

[joseph.nowak]

I have nearly the exact requirements. Here's how I pulled the arguments out of the command line. The only trick I haven't unraveled yet is how to check to see if arguments exist.

Private Sub Workbook_Open()
    Dim CmdRaw As Long
    Dim CmdLine As String
    Dim args As String
    
    CmdRaw = GetCommandLine
    CmdLine = CmdToSTr(CmdRaw)
    
    '/e/basecase/2/10
    args = Mid(CmdLine, InStr(1, CmdLine, " /e/"))
    ArgArray = Split(args, "/")
    
    'COA = ArgArray(2)
    'Starting Cond = ArgArray(3)
    'Msn demand = ArgArray(4)
    Application.ScreenUpdating = False
    
    Copy_BComp
    Copy_BData
    Copy_BType
    Copy_Mdata
    Copy_Risk
    
    'write csv files
    cmdSaveCSV
    Close_It

End Sub

I'm thinking checking If Error, Quit but haven't worked out the sytax...any ideas?

 

[Air_Cooled_Nut]

http://www.vbforums.com/showpost.php?p=4027694&postcount=13