Translating VBS script to VBA and executing via Excel

Question

I am looking to convert a VBScript to VBA and run it from within an Excel module. I have translated 'VB' to it's VBA equivalent in the past, but I am having a little trouble this time around. I made several attempts (okay maybe a lot of attempts) to try to translate this myself without success.

I then searched Google, the stack and other forums that I had found on Google and had little success with some of the examples I had found.

Is it possible to execute the below from an Excel module? (As a side note, I am trying to interact with an application directly. I have other modules outside this script that does just this using OLE Automation and referencing the appropriate COMs / library references). However, the application lacks extensive documentation and the methods and properties available to me via said DLL's are pretty limited.

And so, this is my workaround (though not preferred), as awful as it may be, it seems to work. I currently have a BAT file that runs the VBScript and it works great. Now I just need to run the code from within a module. I tried inserting the script as an object and running a macro that executes it from within Excel, but I get security prompts (and those are no fun).

Any help is greatly appreciated!

Set WshShell = WScript.CreateObject("WScript.Shell")
WScript.Sleep 1000
WshShell.AppActivate "Average Terminal Emulator"
WScript.Sleep 1000
WshShell.SendKeys "%"
WshShell.SendKeys "I"
WshShell.SendKeys "Y"
WshShell.SendKeys "S"
WScript.Sleep 1000
WshShell.AppActivate "Average Terminal Emulator"
WScript.Sleep 1000
WshShell.SendKeys "0"
WshShell.SendKeys "{TAB}"
WshShell.SendKeys "M"
WshShell.SendKeys "{TAB}"
WshShell.SendKeys "C"
WshShell.SendKeys "{TAB}"
WScript.Sleep 1000
WshShell.SendKeys "{ENTER}"

Bond · Accepted Answer · 2015-08-28 15:23:39Z

3

SendKeys() and AppActivate() are native to VBA, so you can remove all the WshShell stuff, including the CreateObject() call to create it.

Then just replace each instance of:

WScript.Sleep 1000

with:

Application.Wait Now() + TimeValue("00:00:01")

and you should be fine.

answered Aug 28, 2015 at 15:23

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BruceWayne Over a year ago

I've used SenKeys in the past, and just a note - OP might need to add a couple of Application.Wait in between some of those. I've noticed that, since Excel runs through the macro as fast as it can, sometimes the program that the SendKeys is manipulating can't keep up.

Bond Over a year ago

@BruceWayne - It's a good point. SendKeys is almost never the best solution so if you're going to use it, you'll need to play with it and tweak your wait times in between keypresses.

BruceWayne Over a year ago

I agree - when running my SendKeys macro, I don't even look at the computer, when running SendKeys because it's so finicky.

Collectives™ on Stack Overflow

Translating VBS script to VBA and executing via Excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related