CODEWITHSUNDEEP
excelvbavbscripttranslate
Singularity20XX
Singularity20XX

Reputation: 321

Translating VBS script to VBA and executing via Excel

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}"

Upvotes: 2

Views: 470

Answers (1)

Bond
Bond

Reputation: 16321

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.

Upvotes: 3

Related Questions