Jump to content

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

Embedded Windows Scripting (VBScript & JScript) and COM


  • Please log in to reply
68 replies to this topic
harryman
  • Members
  • 27 posts
  • Last active: Mar 09 2011 10:20 PM
  • Joined: 20 Jul 2007
In trying to do some automation with Visual Studio, I'd like to have be able to call functions and use the results of some calls (objDTE.ActiveDocument.FullName() for example) in ahk. So it seems I'd need some GetObject'ing WS_Exec()'ing.

I'd appreciate it if anyone could point out the correct way do this sort of thing.

A little test VB script:

Dim objDTE
' Creates an instance of the VS.NET 2003 IDE
Set objDTE = GetObject(,"VisualStudio.DTE.8.0")
 
' While the instance is still invisible, show its name and version
MsgBox objDTE.ActiveDocument & " " & objDTE.Name & " " & objDTE.Version
 
' Make it visible and keep it open after we finish this script
objDTE.MainWindow.Visible = True
objDTE.UserControl = True


erictheturtle
  • Members
  • 101 posts
  • Last active: Sep 04 2011 02:07 PM
  • Joined: 27 Jun 2007
Wow Chris, I didn't expect this. I was sorta hoping to start a blank new thread so people aren't bothered with all the development ramblings. But it's here, so I guess I'll run with it. But I do very much like the title of this thread, as I still hadn't decided on a good name for the one I was going to make. And also thank you for all your feedback.

1) Can/should COM Helper be merged into this Windows Scripting API in a way that gives the best of both worlds? (Maybe that would cause too many compromises.)

That's a good question.

I believe I included most, if not all of Sean's CoHelper.ahk functionality in WS4AHK.ahk, so I suppose it is possible to use WS4AHK to do raw COM work in the same way CoHelper does (of course it would have to be written to work with the different function names, expected parameters, and return types). While it's possible, and very useable, I never expected anyone to use it for that purpose. Once someone accepts the extra bulk of WS4AHK into their scripts, then there's really no reason to use the raw COM functions since it's so much easier to use VBScript/JScript.

I figured that if someone wants to make a very small and quick script to access COM, then Sean's raw functions are the way to go. I would think that Sean's scripts should be preferred for compact, high quality, well designed, widely released code that only needs access to a static set of COM functions.

WS4AHK, on the other hand, I figure would be preferred for simple, quickly made scripts for individual use. It may be worthwhile to use in widely released code if there is a need for heavy and dynamic access to COM (such as a web page DOM).

But those are just my thoughts. There may be a better way to handle the two different libraries.
-m35

erictheturtle
  • Members
  • 101 posts
  • Last active: Sep 04 2011 02:07 PM
  • Joined: 27 Jun 2007
@Boo
To use Embedded Windows Scripting in AHK, it's important to first know VBScript or JScript.

To access Microsoft Excel or Word, then it is also important to understand each of these programs' COM object model. The best way to lean this is to use the Visual Basic for Applications (VBA) Integrated Development Environment (IDE) that is included in Microsoft Excel and Word.

If you are familiar with VBScript/JScript and the Excel/Word COM object model, then it's like your writing the VBScript/JScript code on the fly.


I haven't ever played much with Word's COM interface, so maybe someone more familiar with it can share. I am, however, very familiar with Excel's COM interface.

Here is how you might gain access to the top left cell in the active worksheet of a currently running Excel program--in VBScript:
Set oExcel = GetObject(,"Excel.Application")
Set oActiveSheet = oExcel.ActiveSheet
oActiveSheet.Cells(1, 1).Value = "AHK"
got_the_value = oActiveSheet.Cells(1, 1).Value
Msgbox got_the_value

Now here's how you could do it using WS4AHK.ahk:
WS_Initialize()
WS_Exec("Set oExcel = GetObject(,""Excel.Application"")")
WS_Exec("Set oActiveSheet = oExcel.ActiveSheet")
WS_Exec("oActiveSheet.Cells(1, 1).Value = ""AHK""")
WS_Eval(got_the_value, "oActiveSheet.Cells(1, 1).Value")
Msgbox % got_the_value
WS_Uninitialize()
(Note it is recommended you also include some error checking for each line to ensure there wasn't an error)

You can see the VBScript code is nearly identical. In most cases, you can write and test the code you want in VBScript, then put it directly into Autohotkey like I did above.
-m35

erictheturtle
  • Members
  • 101 posts
  • Last active: Sep 04 2011 02:07 PM
  • Joined: 27 Jun 2007
@BoBo¨
You can do something very similar with WS4AHK
WS_Initialize()

VBCode =
(
	Dim xlApp
	Dim xlBook
	
	'Opens the Excel file in Excel
	Sub OpenExcelFile(filename)
	Set xlApp = CreateObject("Excel.Application")
	xlApp.visible = true
	Set xlBook = xlApp.Workbooks.open(filename)
	end sub
	
	'Use this to close Excel later
	Sub CloseExcel
	xlApp.quit
	Set xlApp = Nothing
	End Sub
	
	'Retrieves a cell value from the specified worksheet
	Function GetCell(Sheet,Row,Column)
	Dim xlSheet
	Set xlSheet = xlBook.Worksheets(Sheet)
	GetCell = xlSheet.Cells(Row, Column).Value
	End Function
	
	'Sets specified cell of specified worksheet
	Function SetCell(Sheet,Row,Column,NewValue)
	Dim xlSheet
	Set xlSheet = xlBook.Worksheets(Sheet)
	xlSheet.Cells(Row,Column).Value = NewValue
	End Function 
)
WS_Exec(VBCode)

WS_Exec("OpenExcelFile """ SCRIPT_DIR "example.xls""")
Msgbox % ErrorLevel
WS_Eval(theValue, "GetCell(""Sheet1"",5,4)")
Msgbox, Cell value: %theValue%
WS_Exec("SetCell ""Sheet1"",28,2,998")
WS_Exec("CloseExcel")

WS_Uninitialize()

-m35

BoBo¨
  • Guests
  • Last active:
  • Joined: --
@ erictheturtle
Thx a mill for your effort to provide your code samples/advise in a noob friendly format. Much appreciated. 8)

I guess one of the main reasons why (noobish) people will be interested using wsh2ahk, is the option to use code snippest from outer space (Fry - my alter ego from Futurama would call it "The Internet" :wink:). Let's name it VBS plug&play :D

Boo
  • Guests
  • Last active:
  • Joined: --
erictheturtle :
Thanks erictheturtle for your superb work and your explanations. The things begin to be much clear for me. Some questions :

- How is it possible to act on a specific instance/window of Word or Excel in which the file I want to act on is already loaded ?

For example, If I have an instance of (say) Excel with the file BoBo.xls in it (each instance is visible or not, never mind) and an other instance with the file erictheturtle.xls in it, how can I "take control" over erictheturtle.xls ?
The same question with only one instance of Excel with the two files in it

- What is(are) the differences between CreateObject and GetObject ? I think the first one is to lauch an instance of Excel (or other app) and the second to act on an instance already launched, is it OK ?

- How to identify and use a particular instance of Excel (so I think in VBScript, as the fact to activate it under AHK is probably not usefull to work with it with a vbs-ahk script) ?

- I tried to use the VBScript (WSH) command "Wscript.Sleep 2500".
If (!WS_Exec("Wscript.Sleep 2500"))
  Msgbox % A_LineFile " : " ErrorLevel
The answer is that there isn't any Wscript object. If I try to create a Wscript Object
If (!WS_Exec("Set oScript = CreateObject(%s)", "Wscript.Application"))
  Msgbox % A_LineFile ":" ErrorLevel
(I don't know if the syntax is correct), the answer is that such an object can't be created. Probably what I did is stupid. But how I can use the sleep command ?

May be my questions are stupid. So sorry if it is the case.
Thanks by advance for your answer.

2Chris : Do you think it would be possible to integrate these functionnalities in ahk ? May be it would be possible to use continuation sections to put the vbscript in ?
Thanks for your answer

erictheturtle
  • Members
  • 101 posts
  • Last active: Sep 04 2011 02:07 PM
  • Joined: 27 Jun 2007
@harryman
I hope the examples I posted for Boo and Bobo have provided what you needed. If you still have questions, feel free to ask.


@Boo

- What is(are) the differences between CreateObject and GetObject ? I think the first one is to lauch an instance of Excel (or other app) and the second to act on an instance already launched, is it OK ?

Your understanding is correct. I might recommend you look at Microsoft's VBScript library reference for CreateObject and GetObject, but their descriptions are pretty cryptic.

- How is it possible to act on a specific instance/window of Word or Excel in which the file I want to act on is already loaded ?
- How to identify and use a particular instance of Excel (so I think in VBScript, as the fact to activate it under AHK is probably not usefull to work with it with a vbs-ahk script) ?

For example, If I have an instance of (say) Excel with the file BoBo.xls in it (each instance is visible or not, never mind) and an other instance with the file erictheturtle.xls in it, how can I "take control" over erictheturtle.xls ?
The same question with only one instance of Excel with the two files in it


Hmm, that's a good question. It's very possible to have two different Excel programs running at the same time, but I've never tried to gain access to both of them. I'm not sure what you can do. Maybe there is someone who has some experience with this situation?

But in the case where both workbooks are open in the same Excel program, then you can do something like this (in VBScript)
Set oExcelApp = GetObject(, "Excel.Application") ' Get the current running Excel program
Set oEricTheTurtle = oExcelApp.Workbooks("erictheturtle.xls") ' Get the erictheturtle workbook
oEricTheTurtle.ActiveSheet.Cells(1,1) = "Turtle"
Set oBoBo = oExcelApp.Workbooks("BoBo.xls") ' Get the bobo workbook
oBoBo.ActiveSheet.Cells(1,1) = "Bobo"

- I tried to use the VBScript (WSH) command "Wscript.Sleep 2500".
The answer is that there isn't any Wscript object. If I try to create a Wscript Object...the answer is that such an object can't be created.

Unfortunately the WScript object is only available in normal VBScript executed via wscript.exe or cscript.exe. It is not part of the Microsoft Scripting Control that WS4AHK uses. This isn't usually a problem because Autohotkey can do most of the things WScript can do. Instead of calling the VBScript code
WS_Exec("WScript.Sleep 2500")
you should just use the AHK code
Sleep, 2500

-m35

erictheturtle
  • Members
  • 101 posts
  • Last active: Sep 04 2011 02:07 PM
  • Joined: 27 Jun 2007

Once you decide on a name, feel free to rename your homepage from ~easycom to the new name (unless it's too much trouble). I can update the links throughout the forum.

Is it possible to change the autohotkey.net ~name? I looked but didn't find any way how.
-m35

Boo
  • Guests
  • Last active:
  • Joined: --
Thanks erictheturtle for your explanations.

I am seaching in vbscript ressources on internet if I can find an answer concerning the multiple instances of Excel. For the moment, I didn't found really interstings things. If I find the solution, I'll post it.

you should just use the AHK code

Sleep, 2500

Of course. It was just in the case it was possible to put the whole vbscript and exec it.

Unfortunately the WScript object is only available in normal VBScript executed via wscript.exe or cscript.exe. It is not part of the Microsoft Scripting Control that WS4AHK uses.

I didn't knew that. It's a pity, even if the majority of the vbscript commands can be replaced by AHK !!!

Thanks again for your work.

harryman
  • Members
  • 27 posts
  • Last active: Mar 09 2011 10:20 PM
  • Joined: 20 Jul 2007
erictheturtle,

Yes I think that example gives me a good enough understanding. Thank you.

I don't know if this is a good solution, but I might suggest that there be 2 threads. A "help with" thread and a development thread.

The problem I found with the actual help forum is that the people that do the helping out over there might not know how to help with something like this. They'd probably just point back over here. Perhaps if you started the thread in the help section and monitored it that might work...

Thanks again,
-hm

Andi
  • Members
  • 195 posts
  • Last active: Apr 18 2014 05:03 PM
  • Joined: 11 Feb 2005

@BoBo¨
You can do something very similar with WS4AHK

WS_Initialize()

VBCode =
(
	Dim xlApp
	Dim xlBook
	
	'Opens the Excel file in Excel
	Sub OpenExcelFile(filename)
	Set xlApp = CreateObject("Excel.Application")
	xlApp.visible = true
	Set xlBook = xlApp.Workbooks.open(filename)
	end sub
	
	'Use this to close Excel later
	Sub CloseExcel
	xlApp.quit
	Set xlApp = Nothing
	End Sub
	
	'Retrieves a cell value from the specified worksheet
	Function GetCell(Sheet,Row,Column)
	Dim xlSheet
	Set xlSheet = xlBook.Worksheets(Sheet)
	GetCell = xlSheet.Cells(Row, Column).Value
	End Function
	
	'Sets specified cell of specified worksheet
	Function SetCell(Sheet,Row,Column,NewValue)
	Dim xlSheet
	Set xlSheet = xlBook.Worksheets(Sheet)
	xlSheet.Cells(Row,Column).Value = NewValue
	End Function 
)
WS_Exec(VBCode)

WS_Exec("OpenExcelFile """ SCRIPT_DIR "example.xls""")
Msgbox % ErrorLevel
WS_Eval(theValue, "GetCell(""Sheet1"",5,4)")
Msgbox, Cell value: %theValue%
WS_Exec("SetCell ""Sheet1"",28,2,998")
WS_Exec("CloseExcel")

WS_Uninitialize()


At first, thank you erictheturtle for your wonderful improvement! Really great :D
Has someone a hint for me, how to send a string to an excel cell? The following line sends nothing!?
string = ABC
WS_Exec("SetCell ""Sheet1"",28,2," . string)


Andi
  • Members
  • 195 posts
  • Last active: Apr 18 2014 05:03 PM
  • Joined: 11 Feb 2005
I think I found it... or is there a more elegant way?
string = ABC 
WS_Exec("SetCell ""Sheet1"",28,2," . "" . string . "")


erictheturtle
  • Members
  • 101 posts
  • Last active: Sep 04 2011 02:07 PM
  • Joined: 27 Jun 2007
Ah I thought you might find the error yourself :)

Your approach will work fine for constant strings that are guaranteed not to have any forbidden characters (such as tab or linefeed).

If the string might possibly contain forbidden characters, then there are safer ways of inserting a string into the code.

The VBStr() function (part of the WS4AHK) will wrap your string in quotes, and will escape forbidden characters in the string.
; Using the VBStr() function
WS_Exec("SetCell ""Sheet1"",28,2," . VBStr(string))

If you've ever written any C/C++ code before, then you are probably familiar with the function printf(). In that case you may feel comfortable using a similar syntax (also wraps in quotes and escapes forbidden characters).
; Using a style inspired by the C function 'printf'
WS_Exec("SetCell ""Sheet1"",28,2, %s", string)

Hope this helps. And thanks for trying out the code.
-m35

Andi
  • Members
  • 195 posts
  • Last active: Apr 18 2014 05:03 PM
  • Joined: 11 Feb 2005
Thank you! I try it and it works wonderful :D :D :D

erictheturtle
  • Members
  • 101 posts
  • Last active: Sep 04 2011 02:07 PM
  • Joined: 27 Jun 2007
This code works very similar to Sean's NetConnect() function, but uses ws4ahk instead.

#Include ws4ahk.ahk

WS_Initialize()

NetConnect(0)
Sleep, 5000
NetConnect(1)

WS_Uninitialize()	


NetConnect(bEnable = False, sConnection = "Local Area Connection")
{
	Static blnSetup = False
	If (!blnSetup)
	{
		VBCode =
		(
			Const CSIDL_Connections = &H31
			
			Sub NetConnect(iEnable, sConnection)
				' e.g.
				'iEnable = 1
				'sConnection = "Local Area Connection"
			
				Set oShellApp = CreateObject("Shell.Application")
				Set oNetFldr = oShellApp.Namespace(CSIDL_Connections)
				For Each oConnection In oNetFldr.Items
					If oConnection.Name = sConnection Then
						Set oEnableDisableVerb = oConnection.Verbs.Item(0)
						Exit For
					End If
				Next
				
				If Not oEnableDisableVerb Is Nothing Then
				
					' "Disa&ble" and "En&able" strings
					' will vary depending on the Windows
					' language setting.
					If ((iEnable = 0) And oEnableDisableVerb.Name = "Disa&ble") Or _
						((iEnable = 1) And oEnableDisableVerb.Name = "En&able") Then
						oEnableDisableVerb.DoIt
					End If
					
				End If
			
			End Sub
		)
		If (!WS_Exec(VBCode))
		{
			Msgbox % ErrorLevel
			Return
		}
			
		blnSetup = True
	}
		
	If (!WS_Exec("NetConnect %v, %s", bEnable, sConnection))
	{
		Msgbox % ErrorLevel
		Return
	}
	WinWait, %sConnection% ahk_class #32770,, 1
	WinWaitClose
}

-m35