VBScript Event Sink Not Firing

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex Turner - 16 Jan 2008 10:51 GMT

Anyone knows why this does not work - i.e. the even sink
excel_SheetChange never fires?

Dim myExcel,myWorkbook,mySheet

Set myExcel=WScript.CreateObject("Excel.Application","excel_")

Set myWorkbook=myExcel.Workbooks.Add()
Set mySheet=myWorkbook.Sheets(1)
myExcel.Visible=TRUE

myExcel.EnableEvents=TRUE

While myExcel.Visible
WScript.Sleep(100)
Wend

Sub excel_SheetChange(ByVal Sh, ByVal Target)
WScript.Echo "Sheet Change: Sheet=" & Sh.Name & ", Range=" &
Target.Row & "," & Target.Column
End Sub

Any help welcome. If I get this to work I'll be posting about it on my
blog. I've been wanting to sink Excel events to vbscript for ages and
just don't seem to be able to manage :(

Thanks!

AJ

Signature

http://nerds-central.blogpot.com

Reply to this Message

Joel - 16 Jan 2008 11:26 GMT

1) Make sure the worksheet_change function is on the VBA sheet where you want
it to work. Work_sheet change fucntion only works on one sheet
2) Worksheet_change get only one parameter, not two
3) To get the sheetname use activesheet

Sub worksheet_Change(ByVal Target As Range)
MsgBox "Sheet Change: Sheet=" & ActiveSheet.Name & _
", Range=" & Target.Row & "," & Target.Column
End Sub

> Anyone knows why this does not work - i.e. the even sink
> excel_SheetChange never fires?
[quoted text clipped - 25 lines]
>
> AJ

Reply to this Message

Alex Turner - 16 Jan 2008 15:02 GMT

> 1) Make sure the worksheet_change function is on the VBA sheet where you
> want it to work. Work_sheet change fucntion only works on one sheet 2)
[quoted text clipped - 5 lines]
> ", Range=" & Target.Row & "," & Target.Column
> End Sub

Thanks - but I am vbscript not VBA. You're right though - that is how to
do it in VBA.

Cheers - AJ

Signature

http://nerds-central.blogspot.com/search/label/Baby%20Steps

Reply to this Message

Robert Bruce - 16 Jan 2008 14:53 GMT

Yn newyddion: 6rudnb_kwfc-fBDaRVnyhwA@pipex.net,
Roedd Alex Turner <ajt@nerds-central.com> wedi ysgrifennu:

> Anyone knows why this does not work - i.e. the even sink
> excel_SheetChange never fires?

VBScript supports only late binding. In order to sink COM events you need to
early bind and use the WithEvents declaration in a class module.

Rob

Reply to this Message

Alex Turner - 16 Jan 2008 15:04 GMT

> Yn newyddion: 6rudnb_kwfc-fBDaRVnyhwA@pipex.net, Roedd Alex Turner
> <ajt@nerds-central.com> wedi ysgrifennu:
[quoted text clipped - 6 lines]
>
> Rob

Thanks Rob for taking the time to reply.

I think you'll find that what you've said is not actually try. See
WScript.CreateObject and WScript.ConnectObject. There seems something
different about Excel it is definitely possible to sink events to
VBScript with other objects, like IE etc.

Cheers - AJ

Signature

http://nerds-central.blogspot.com/search/label/Baby%20Steps

Reply to this Message

Robert Bruce - 16 Jan 2008 16:49 GMT

Yn newyddion: 6rudnbnkwfdigRPaRVnyhwA@pipex.net,
Roedd Alex Turner <ajt@nerds-central.com> wedi ysgrifennu:

> I think you'll find that what you've said is not actually try. See
> WScript.CreateObject and WScript.ConnectObject. There seems something
> different about Excel it is definitely possible to sink events to
> VBScript with other objects, like IE etc.

Sorry Alex. You're right and I;m wrong.

Rob

Reply to this Message

papou - 22 Jan 2008 10:55 GMT

Hello Alex

Not a VBScript expert and probably not to be considered as an excel "guru".
Nevertheless I would suggest that you can hardly fire your sub to run a
workbook event procedure (Workbook_SheetChange in your case) from an Excel
application object, since no event procedure is present in the workbook
(remember you are creating a new workbook).
The only possible alternative I can see is to create, via your script, the
event procedure in the new workbook.

HTH
Cordially
Pascal

> Anyone knows why this does not work - i.e. the even sink
> excel_SheetChange never fires?
[quoted text clipped - 25 lines]
>
> AJ

Reply to this Message