Hi everyone.
I've got an Excel Spreadsheet which contains different values that I'm loading into two variables in VBA (an integer counter and an array).
I'm trying to pass those variable values from the VBA within Excel to a script in MS which will call a third party application and transfer the variable values into it.
For the sake of an example let's say I have variable in VBA called j with a value of 17. I'm looking to move that value (17) to a MS variable called i.
From VBA I'm running the following code to start the MS script and transfer the value.
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")
WshShell.Run """C:\Program Files (x86)\Macro Scheduler 14\msched.exe""TestTransfer /i=j"""
Now I am not a strong coder. I got the idea to use the code above from Google and I don't even know why the VBA is requiring all the quotes, I just know that errors are generated without them.
According to the MS help files I should be passing values as follows... /variable_name_in_MS=value_to_be_passed
The code above works in that it runs the MS script "TestTransfer"; however, the value that is passed to MS from VBA is "j" and not the value of "j" which is 17. Does anyone know how to pass the value of the variable and not the literal variable name?
Any thoughts or ideas are greatly appreciated.
Thank you!
Tom
Passing Variables from VBA in Excel to MS
Moderators: Dorian (MJT support), JRL
- Dorian (MJT support)
- Automation Wizard
- Posts: 1352
- Joined: Sun Nov 03, 2002 3:19 am
- Contact:
Re: Passing Variables from VBA in Excel to MS
Is there a reason you can't use Macro Scheduler's native Excel commands such as XLGetCell
Yes, we have a Custom Scripting Service. Message me or go here
Re: Passing Variables from VBA in Excel to MS
Thanks so much for taking the time to reply. Much appreciated
Basically the VBA macro has already been written for some time and functions perfectly within Excel and the variables already exist within that code. I was just trying to enhance things with MS but the MS portion represents a "nice to have".
Thus, to keep things streamlined I was hoping to leverage the variables and code as they already exist as opposed to doing a rewrite; however, if that is the only option then I guess that is what I'll have to do if I want to continue with the MS side of things.
I guess I was sort of hoping that it would be a simple syntax thing that I'm missing with the code I was using to transfer the variables
Tom
Basically the VBA macro has already been written for some time and functions perfectly within Excel and the variables already exist within that code. I was just trying to enhance things with MS but the MS portion represents a "nice to have".
Thus, to keep things streamlined I was hoping to leverage the variables and code as they already exist as opposed to doing a rewrite; however, if that is the only option then I guess that is what I'll have to do if I want to continue with the MS side of things.
I guess I was sort of hoping that it would be a simple syntax thing that I'm missing with the code I was using to transfer the variables
Tom
- Marcus Tettmar
- Site Admin
- Posts: 7380
- Joined: Thu Sep 19, 2002 3:00 pm
- Location: Dorset, UK
- Contact:
Re: Passing Variables from VBA in Excel to MS
The question appears to be "can I pass command line arguments when I use WScript.Shell Run command?" That's really beyond the scope of our support as that is not a Macro Scheduler function. But I can't see why you can't run any runnable command with this command, so I don't see why you can't pass parameters. A quick google suggests you can. You might need to pay attention to quoting:
https://stackoverflow.com/questions/272 ... parameters
https://stackoverflow.com/questions/272 ... parameters
Marcus Tettmar
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?
http://mjtnet.com/blog/ | http://twitter.com/marcustettmar
Did you know we are now offering affordable monthly subscriptions for Macro Scheduler Standard?
Re: Passing Variables from VBA in Excel to MS
Thank you Marcus. I am sorry if I wasted anyone's time here. I appreciate the advice.
Have a great day
Tom
Have a great day
Tom