Passing Variables from VBA in Excel to MS

[tomdraina]

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

[Dorian (MJT support)]

Is there a reason you can't use Macro Scheduler's native Excel commands such as XLGetCell

[tomdraina]

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

[Marcus Tettmar]

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

[tomdraina]

Thank you Marcus. I am sorry if I wasted anyone's time here. I appreciate the advice.

Have a great day

Tom