Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible to pass a variable to Excel from command line/short cut?
We've got an application front we've built which uses an Oracle back
end. It has poor graphics/analytics, and we'd like to use Excel for that portion. The only problem is that when we open Excel and connect to Oracle, we still have to enter the ID# in Excel by hand. We'd love be able to do something like run "analysis.xls /set/ ID=12345", and have it pass in ID=12345 as a variable to be used. We're pretty good at developing Excel (use ADO pulls of Oracle data, heavy into pivot tables and use buttons and forms coded through VBA.) But, we've never tried this. I read the MS files about passing command line launch parameters via "excel.exe ....", but didn't see anything about dumping named variables in. (I might use them to force a "read only" open.) We've thought about saving the ID to an Oracle database table, and then having the Excel "on open" code check to see if there's an ID in the last 30 seconds. However, this will be a multi-location, multi- user application, and we'd rather not have to deal with figuring out which row to open. (i.e. 2 people opening the Excel file at once.) So, to restate the question: Is there any way to pass a value to Excel (to VBA is okay) when opening the file? Thanks, Jon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible to pass a variable to Excel from command line/short cut?
As far as I can tell, only another program using VBA to automate Excel
can pass a variable to a new instance of it. HTH, JP On Mar 21, 4:48*pm, Jon wrote: We've got an application front we've built which uses an Oracle back end. *It has poor graphics/analytics, and we'd like to use Excel for that portion. The only problem is that when we open Excel and connect to Oracle, we still have to enter the ID# in Excel by hand. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible to pass a variable to Excel from command line/short cut?
Jon,
You can use a VBScript file as a helper file. The WScript object has an 'arguments' property that you can use to retrieve the arguments when you launch wscript.exe or cscript.exe with the vbs file as the first argument and arguments to the vbs file following. The vbs file would contain the code to create an instance of the Excel application, to open your designated workbook, and if appropriate, run a specific subroutine, handing the sub your argument. Steve Yandl "Jon" wrote in message ... We've got an application front we've built which uses an Oracle back end. It has poor graphics/analytics, and we'd like to use Excel for that portion. The only problem is that when we open Excel and connect to Oracle, we still have to enter the ID# in Excel by hand. We'd love be able to do something like run "analysis.xls /set/ ID=12345", and have it pass in ID=12345 as a variable to be used. We're pretty good at developing Excel (use ADO pulls of Oracle data, heavy into pivot tables and use buttons and forms coded through VBA.) But, we've never tried this. I read the MS files about passing command line launch parameters via "excel.exe ....", but didn't see anything about dumping named variables in. (I might use them to force a "read only" open.) We've thought about saving the ID to an Oracle database table, and then having the Excel "on open" code check to see if there's an ID in the last 30 seconds. However, this will be a multi-location, multi- user application, and we'd rather not have to deal with figuring out which row to open. (i.e. 2 people opening the Excel file at once.) So, to restate the question: Is there any way to pass a value to Excel (to VBA is okay) when opening the file? Thanks, Jon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Possible to pass a variable to Excel from command line/short cut?
On Mar 21, 3:48*pm, Jon wrote:
We've got an application front we've built which uses an Oracle back end. *It has poor graphics/analytics, and we'd like to use Excel for that portion. The only problem is that when we open Excel and connect to Oracle, we still have to enter the ID# in Excel by hand. We'd love be able to do something like run "analysis.xls /set/ ID=12345", and have it pass in ID=12345 as a variable to be used. We're pretty good at developing Excel (use ADO pulls of Oracle data, heavy into pivot tables and use buttons and forms coded through VBA.) But, we've never tried this. I read the MS files about passing command line launch parameters via "excel.exe ....", but didn't see anything about dumping named variables in. *(I might use them to force a "read only" open.) We've thought about saving the ID to an Oracle database table, and then having the Excel "on open" code check to see if there's an ID in the last 30 seconds. *However, this will be a multi-location, multi- user application, and we'd rather not have to deal with figuring out which row to open. *(i.e. 2 people opening the Excel file at once.) So, to restate the question: Is there any way to pass a value to Excel (to VBA is okay) when opening the file? Thanks, Jon I finally found a few links just recently that talk about how to deal with this. You can read the command line argument in VBA, and if you pass it properly, you can parse out your info from the command line. http://www.mrexcel.com/archive/VBA/15657.html is a 2001 write-up of a "tip" letter from 1999. http://www.dailydoseofexcel.com/arch...uments-in-vba/ is a 2006 article describing the same technique with a few comments. In a nutshell, the format they use is excel c:\temp\test /e/param1/param2/.../paramN Then in VBA, use API function GetCommandLine (alias GetCommandLineA in Win95), to retrieve in the command line string. Once you have the string, it's up to your imagination how you'd like to parse it out. (The sample code searches for the '/e', then grabs the string until the next '/'.) Jon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can Excel pass variable from a spreadsheet to IE | Excel Programming | |||
How to: Pass Command Line Parameter ??? | Excel Worksheet Functions | |||
How do I avoid multiple icons of Excel when using short command Al | Excel Discussion (Misc queries) | |||
Pass Version of Excel to a variable | Excel Programming | |||
Command and pass value to Acces from Excel | Excel Programming |