Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Using VBscript in ahk without a temporary file


  • Please log in to reply
9 replies to this topic
savage
  • Members
  • 207 posts
  • Last active: Jul 03 2008 03:12 AM
  • Joined: 02 Jul 2004
I thought I'd unbury this from the thread in the wish list section so that more people could see it.


Put this bit in peval.vbs
Dim StdIn, script
script = ""
Set StdIn = WScript.StdIn
Do While Not StdIn.AtEndOfStream
    script = script + vbNewLine + StdIn.ReadLine
Loop
execute script

And here's how to use it:
var = test
script =
(
Dim s
s = "%var%"
WScript.echo "Hi there, " & s
)

F1::
    text := RunVBS(script)
    Msgbox, %text%
return

;CMDRet functions
RunWaitEx(CMD, CMDdir, CMDin, ByRef CMDout, ByRef CMDerr)
{
  VarSetCapacity(CMDOut, 100000)
  VarSetCapacity(CMDerr, 100000)
  RetVal := DllCall("cmdret.dll\RunWEx", "str", CMD, "str", CMDdir, "str", CMDin, "str", CMDout, "str", CMDerr)
  Return, %RetVal%
}

Capture(CMD, input)
{
    CMDOUT =
    CMDERR =
    RunWaitEx(CMD, a_scriptdir, input, CMDOUT, CMDERR)
    return %CMDOUT%
}

;let's run some vbscript stuff
RunVBS(script)
{
    return Capture("cscript.exe peval.vbs",script)
}

You obviously need CMDRet to make this work. The peval.vbs (peval is short for pipe-eval) script gives you the ability to pipe scripts into cscript or wscript, like you can with perl, python, or just about any other scripting language. So just stick the RunVBS function in some file to be included, along with the CMDRet functions and you can just use RunVBS whenever.

Enjoy. Let me know if you find any weird behavior or possible improvements.

Nemroth
  • Members
  • 278 posts
  • Last active: Dec 31 2011 10:53 PM
  • Joined: 07 Sep 2004
It's absolutely superb !!! Bravo ! If an AHK script is compiled, the VBScript source code isn't accessible. Very good work. Thanks.

savage
  • Members
  • 207 posts
  • Last active: Jul 03 2008 03:12 AM
  • Joined: 02 Jul 2004

It's absolutely superb !!! Bravo !


Thanks 8)

Chris
  • Administrators
  • 10727 posts
  • Last active:
  • Joined: 02 Mar 2004
I'm glad you posted your work in a neat package as a new, easy-to-follow topic. It's sure to be useful to many.

corrupt
  • Members
  • 2558 posts
  • Last active: Nov 01 2014 03:23 PM
  • Joined: 29 Dec 2004
Good stuff savage. Thanks for sharing this solution :)

Nemroth
  • Members
  • 278 posts
  • Last active: Dec 31 2011 10:53 PM
  • Joined: 07 Sep 2004
A problem :
I have an Excel file called Test.xls witch structure is :
A	B
1	Name	Number
2	A	1
3	B	1
4	C	2
5	D	2
6	E	1
7	F	1

I have a Test.vbs file :
On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=I:\Sniplets\Autohotkey\Thèmes\Autres Lang de script\VBS\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 

objRecordset.Open "Select * FROM [Feuil1$] Where Number = 2", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop

When I run the vbs script, I have two msgbox witch are displayed in witch there are the refs of the cells in witch there are the values equal to 2.

A little precision : [Feuil1$] is the same as [Sheet1$] in english.

This source comes from Microsoft and allow to read xls file values without opening the file in Excel, with ADO (one of the main reasons for witch i hoped to be able to use VBScript in AHK).

There are other VBScript source codes on the web to read or write in a xls file with ADO using VBScript or to control MS Office apps.

When I use the ahk script ...
var = test
script =
(
On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=I:\Sniplets\Autohotkey\Thèmes\Autres Lang de script\VBS\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 

objRecordset.Open "Select * FROM [Feuil1$] Where Number = 2", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop
)

^F1::
    text := RunVBS(script)
;    text := RunVBS(VBscript)
;    Msgbox, %text%
return

;CMDRet functions
RunWaitEx(CMD, CMDdir, CMDin, ByRef CMDout, ByRef CMDerr)
{
  VarSetCapacity(CMDOut, 100000)
  VarSetCapacity(CMDerr, 100000)
  RetVal := DllCall("cmdret.dll\RunWEx", "str", CMD, "str", CMDdir, "str", CMDin, "str", CMDout, "str", CMDerr)
  Return, %RetVal%
}

Capture(CMD, input)
{
    CMDOUT =
    CMDERR =
    RunWaitEx(CMD, a_scriptdir, input, CMDOUT, CMDERR)
    return %CMDOUT%
}

;let's run some vbscript stuff
RunVBS(script)
{
    return Capture("cscript.exe peval.vbs",script)
}

... nothing appends. No msgbox.

Is there a solution to make it works ?

How can I retreive the result (without msgbox of course, in an AHK variable(s)) ?

Thanks by advance for your explanations.

hardo
  • Members
  • 1 posts
  • Last active: Jan 30 2006 09:01 PM
  • Joined: 12 Dec 2005
@Nemroth
Your code is running. Have a look at peval.vbs. Savage has corrected the line in the While-construct:
Dim StdIn, script
script = ""
Set StdIn = WScript.StdIn
Do While Not StdIn.AtEndOfStream
    script = script + vbNewLine + StdIn.ReadLine
Loop
execute script
Without the vbNewLine a multiline vbscript from inside of AHK produces syntactical errors.
Another point is in the function RunVBS. The parameter //Nologo to the cscript.exe suppresses unwanted output:
RunVBS(script)
{  
  return Capture("cscript.exe //Nologo peval.vbs", script)
}

Here is my adaption of your example "Read from Excel by ADO"
; Read from Excel by ADO
; (and in the same manner - with another provider - from Access, MSSQL ...)
;
Gui, Add, ListView, x6 y40 w380 h120, Name|Number
Gui, Add, Text, x6 y12, Many thanks to savage and corrupt.
Gui, Add, Button, x20 y170 w100 h20 gClearContent, Clear Content
Gui, Add, Button, x260 y170 w100 h20 gReadExcel +Default, Read Excel

Gui, Show, h203 w400, Read Excel using ADO

script1 =
(
On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"

objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), vbTab, _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop
)
Return


ReadExcel:
  Excelsays := RunVBS(script1)
  Loop, Parse, Excelsays, `n, `r
  {
    StringSplit, fields, A_LoopField, %A_Tab%
    If A_LoopField
      LV_Add("", fields1, fields2)
  }
Return

ClearContent:
  LV_Delete()
Return

GuiClose:
  ExitApp
  
;CMDRet functions
RunWaitEx(CMD, CMDdir, CMDin, ByRef CMDout, ByRef CMDerr)
{
  VarSetCapacity(CMDOut, 100000)
  VarSetCapacity(CMDerr, 100000)
  RetVal := DllCall("cmdret.dll\RunWEx", "str", CMD, "str", CMDdir, "str", CMDin, "str", CMDout, "str", CMDerr)
  Return, %RetVal%
}

Capture(CMD, input)
{
    CMDOUT =
    CMDERR =
    RunWaitEx(CMD, a_scriptdir, input, CMDOUT, CMDERR)
    return %CMDOUT%
}

;let's run some vbscript stuff
RunVBS(script)
{  
  return Capture("cscript.exe //Nologo peval.vbs", script)
} 
And once more: Many thanks to savage and corrupt. Great work.

Gast
  • Guests
  • Last active:
  • Joined: --
Hello, (My english is bad, sorry)

How I can use a MS Agent VBScript in AutoHotkey?

e.g.

'_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
'_/ Portions generated by MASH - Microsoft Agent Scripting Helper, version 7.3
'_/ by BellCraft Technologies, http://www.bellcraft.com/mash
'_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

' * Agent Object
Dim AgentControl

' * Character Objects
Dim Merlin

' * Variables
Dim UsedChars
Dim MerlinID
Dim MerlinACS
Dim MerlinLoaded
Dim HideReq
Dim Req
Dim ScriptComplete

' * Initialize
UsedChars = "Merlin"

' * Merlin
MerlinID = "Merlin"
MerlinACS = "merlin.acs"
MerlinLoaded = False

ScriptComplete = False

Call Main

Function IsAgentInstalled()
    ' Purpose:  Returns True if Agent 2.0 is installed, else False
    On Error Resume Next

    If ScriptEngineMajorVersion < 2 Then
        IsAgentInstalled = False
    Else
        Set AgentControl = WScript.CreateObject("Agent.Control.2", "AgentControl_")
        IsAgentInstalled = IsObject(AgentControl)
    End If
End Function

Sub Main()
    On Error Resume Next

    ' * INSERT ANY NON-AGENT RELATED SCRIPTING HERE

    If Not IsAgentInstalled() Then
        Exit Sub
    End If

    AgentControl.Connected = True

    MerlinLoaded = LoadLocalChar(MerlinID, MerlinACS)

    If Not MerlinLoaded Then
        MerlinLoaded = LoadLocalChar(MerlinID, "")
    End If

    If MerlinLoaded Then
        Call SetCharObj
        Call AgentIntro
    Else
        Call LoadError
    End If
End Sub

Function LoadLocalChar(ByVal CharID, ByVal CharACS)
    ' Purpose:  Attempts to load the specified character
    ' Returns:  True if successful, False if not
    On Error Resume Next

    If CharACS = "" Then
        AgentControl.Characters.Load CharID, CharACS
    Else
        AgentControl.Characters.Load CharID, CharACS
    End If

    If Err = 0 Then
        LoadLocalChar = True
        Exit Function
    End If
    LoadLocalChar = False
End Function

Sub SetCharObj()
    ' Purpose:  Sets the character reference and TTS Language ID
    On Error Resume Next

    Set Merlin = AgentControl.Characters(MerlinID)
    Merlin.LanguageID = &H409
End Sub

Sub AgentControl_RequestComplete(ByVal RequestObject)
    ' Purpose:  Take action on completion or failure of requests
    On Error Resume Next

    If RequestObject <> EndReq Then
    Else
        If Not Merlin.Visible Then
            ' Trigger the Script to Close
            ScriptComplete = True
        Else
            ' It is up to the user to close the script, by right-clicking
            ' the character and selecting 'Exit'
        End If
    End If

    If RequestObject <> HideReq Then
    Else
        AgentControl.Characters.Unload MerlinID
        ScriptComplete = True
    End If
End Sub

Sub LoadError()
    Dim strMsg
    strMsg = "Error Loading Character: " & MerlinID
    strMsg = strMsg & Chr(13) & Chr(13) & "This Microsoft Agent Script requires the character(s):"
    strMsg = strMsg & Chr(13) & UsedChars
    MsgBox strMsg, 48
End Sub

Sub AgentControl_Click(ByVal CharacterID, ByVal Button, ByVal Shift, ByVal X, ByVal Y)

End Sub

Sub AgentControl_DblClick(ByVal CharacterID, ByVal Button, ByVal Shift, ByVal X, ByVal Y)
    ' Purpose:  Stop and Hide all characters on double-click
    On Error Resume Next

    Merlin.StopAll
    If Not MerlinID.HasOtherClients Then
        If Merlin.Visible Then
            Set HideReq = Merlin.Hide()
        Else
            AgentControl.Characters.Unload MerlinID
            ScriptComplete = True
        End If
    End If
End Sub

Sub InitAgentCommands()
    ' Purpose:  Initialize the Commands menu
    Merlin.Commands.RemoveAll
    Merlin.Commands.Caption = "MASH Menu"
    Merlin.Commands.Add "ACO", "Advanced Character Options", "Advanced Character Options"
    Merlin.Commands.Add "Exit", "Exit", "Exit"
End Sub

Sub AgentControl_Command(ByVal UserInput)
    ' Purpose:  Determine Command that was selected either by menu or voice
    '           and run the applicable Command Script
    On Error Resume Next

    Dim BadConfidence
    BadConfidence = 10

    If (UserInput.Confidence <= -40) Then
        ' Bad Recognition
        Exit Sub
    ElseIf (UserInput.Alt1Name <> "") And Abs(Abs(UserInput.Alt1Confidence) - Abs(UserInput.Confidence)) < BadConfidence Then
        ' Bad Confidence - too close to another command
        Exit Sub
    ElseIf (UserInput.Alt2Name <> "") And Abs(Abs(UserInput.Alt2Confidence) - Abs(UserInput.Confidence)) < BadConfidence Then
        ' Bad Confidence - too close to another command
        Exit Sub
    Else ' High Confidence
        ' *** BEGIN MASH USER COMMANDS ***
        Select Case UserInput.Name
        Case "ACO"
            AgentControl.PropertySheet.Visible = True
        End Select
        ' *** END MASH USER COMMANDS ***

        If UserInput.Name = "Exit" Then
            Set HideReq = Merlin.Hide()
        End If
    End If
End Sub

Sub AgentControl_Bookmark(ByVal BookmarkID)
    On Error Resume Next

End Sub

Sub AgentIntro()
    On Error Resume Next

    Call InitAgentCommands

    ' *** BEGIN MASH USER SCRIPT ***

    Merlin.MoveTo 655, 409
    Merlin.Show
    Merlin.MoveTo 698, 488
    Merlin.MoveTo 236, 316
    Merlin.MoveTo 567, 165
    Merlin.MoveTo 272, 66
    Merlin.Hide


    ' *** END MASH USER SCRIPT ***

    Set EndReq = Merlin.Speak("\mrk=999999999\")

    Do
        WScript.Sleep 1000
    Loop Until ScriptComplete
End Sub
'_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/


THX

  • Guests
  • Last active:
  • Joined: --
Using the CMDRet, how would you pass a variable to the VBS function "script1" using the, Excelsays := RunVBS(script1) format?

For example I would like to query data in an Excel file and send the value to be used in the query string.

"Select * FROM [Sheet1$] WHERE [Number]='" & vNum & "'"


I would like to take user input from a GUI Edit field and use it in the Query.


Thanks
Bill

Chris
  • Administrators
  • 10727 posts
  • Last active:
  • Joined: 02 Mar 2004
For anyone following this topic, please check out erictheturtle's Windows Scripting for Autohotkey. It allows you to embed VBScript and/or JScript directly into an AutoHotkey script. It's completely self-contained: no DLLs or external/temporary scripts are needed.

Please post any comments/feedback at http://www.autohotke...topic21674.html