Application.OnTime Not Working
in [VbScript]
|
Prev: Getting a VBS to run every second without it completely fryinga server
Next: batch file to display ip address when rollover clock
From: Csaba Gabor on 26 Nov 2009 09:16 On Nov 25, 5:23 pm, AutoMagic <handyman1...(a)gmail.com> wrote: > On Nov 24, 5:22 pm, Csaba Gabor <dans...(a)gmail.com> wrote: > > On Nov 25, 12:56 am, AutoMagic <handyman1...(a)gmail.com> wrote: > > > I have created the following VBS code: .... OP wants to bring up Excel and have it call back into the VBScript from an Application.OnTime. He provides example code that doesn't work ... > > > You have two sources of error. The following revision > > illustrates them. The first is due to the fact that you .... Explanation of why the code won't work, the main issue being that no mechanism has been demonstrated for calling out to VBScript from Excel ... > Thanks. I may just use a separate (see note above) Excel file to do > what I want. Here are several options for how to go on this: 1. Write everything in Excel. You have already indicated that you would prefer not to do this. 2. Have your VBScript bring up a hidden copy of Excel and then... 2a. have Excel open up a previously created workbook which has all the VBA code that you need. 2b. create a VBA module in Excel and stuff that with the necessary code. I show an example of that here: http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/14967ab235eb9cd2/ Variant 2a has the problem that the code is now divided into two files, and therefore it is harder to maintain. 2b has the issue that you have to have the VBA code as a string (ugh). In both variants, the VBScript acts only as a startup mechanism without any further calls back into VBScript. 3. Have VBScript bring up a hidden copy of Excel AND also have VBScript bring up a hidden copy of IE. Now use IE.document.parentWindow.setTimeout to call back into VBScript on a time delayed basis. I provide an example script below. I could not get the same approach (using GetRef) to work with Application.OnTime (although there was a glimmer of hope as the call to Application.OnTime occassioned a call to the GetRef'd procedure (seemingly indicating that Excel could make the call back to VBScript) before failing in the actual Application.OnTime assignment). Csaba Gabor from Vienna Sub MyCode MsgBox "Hi mom", , "IE -> VBS call" End sub set ie=CreateObject("InternetExplorer.Application") ie.Visible = true 'development code ie.Navigate2 "about:blank" ie.document.title = "Timer demo" 'devel ie.document.parentWindow.setTimeout _ GetRef("MyCode"), 5000 Dim Till: Till = Now + TimeValue("00:00:12") While Now < Till Wscript.Sleep 1000 Wend ie.quit 'Be nice: clean up
From: Csaba Gabor on 26 Nov 2009 06:59
On Nov 25, 5:23 pm, AutoMagic <handyman1...(a)gmail.com> wrote: > On Nov 24, 5:22 pm, Csaba Gabor <dans...(a)gmail.com> wrote: > > On Nov 25, 12:56 am, AutoMagic <handyman1...(a)gmail.com> wrote: > > > I have created the following VBS code: .... OP wants Excel to run VBScript code (from an Application.OnTime) and gives an example of code that won't work ... > > You have two sources of error. The following revision > > illustrates them. The first is due to the fact that you .... Responder shows why it won't work. The main issue is that Excel won't call into VBScript ... > Thanks. I may just use a separate (see note above) Excel > file to do what I want. As I see it, in the absence of being able to call back into VBScript from Excel, here are some of the options that you have: 1. Do everything from Excel. You've already stated that you prefer not to do it this way. 2. Have the VBScript start off Excel hidden, and then ... 2a. already have an excel workbook which basically just contains a VBA module in which have the code that does what you want. 2b. have excel create a VBA module which you stuff with the code that you want. An example of this is at: http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/14967ab235eb9cd2/ Variant 2a has the problem that you now have your code split into two files. 2b has the problem that the code is composed of strings. 3. Have VBScript start off Excel hidden AND have VBScript start off IE hidden. Load an empty page into IE (ie.Navigate2 "about:blank") and then you can use ie.document.parentWindow.setTimeout to call back into VBScript. I give an example of this code below. I have not gotten this same type of approach (GetRef) to work with Excel (though there is a faint glimmer of hope since the call of Application.OnTime does prematurely fire the call to GetRef'd code before the .OnTime ultimately fails to make the timeout assignment). Csaba Gabor from Vienna Sub MyCode MsgBox "Hi mom", , "IE -> VBS call" End sub set ie=CreateObject("InternetExplorer.Application") ie.Visible = true 'development code ie.Navigate2 "about:blank" ie.document.title = "Timer demo" 'devel ie.document.parentWindow.setTimeout _ GetRef("MyCode"), 5000 Dim Till: Till = Now + TimeValue("00:00:12") While Now < Till Wscript.Sleep 1000 Wend ie.quit 'Be nice: clean up |