RE: Starting Excel and doing "stuff"...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Jeff" wrote:

Holy WHAT? Oh Urkec :-/. I am a mainframe programmer, in fact a smart one,
but I could make more sense of Portuguese than I could of that. I believe you
when you say that’s what I need to do. BUT, if that IS what I have to do I
think my ship is sunk :-/.

I have a WMI .vbs that monitors a folder for new files. I was thinking it
would be something easy, like putting the commands Joel gave me in the .vbs
along with the WMI code.

This is asking a lot. But this will be so cool and helpful to my team here,
that I really would like to make it work. I think if you give some code
examples (or code I could really plug in), and say some more things about
this I’ll do what I can to make it work.


The first thing to do is to compile scrcons.mof from the command line with
the mofcomp utility:

mofcomp -N:root\cimv2 c:\windows\system32\wbem\scrcons.mof

This will register the ActiveScriptConsumer class with WMI. Depending on
your Windows version you may need to change the path to the scrcons.mof.
Mofcomp.exe is located in c:\Windows\system32\wbem\ , but it is in my %PATH%
so I don't need to specify the full path.

Next, create instance of the WMI classes I mentioned. This is test VBScript
code, so you may need to change it to suit your requirements:


'connect to WMI locally
'can't use WScript object in Consumer.vbs
'MsgBox will not error out but will not display

strComputer = "."
Set objSWbemServices = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

'create an instance of __EventFilter class

Set objEventFilterClass = objSWbemServices.Get("__EventFilter")
Set objEventFilter = objEventFilterClass.SpawnInstance_()

objEventFilter.Name = "TestFilter"
objEventFilter.QueryLanguage = "WQL"
objEventFilter.Query = _
"Select * From __InstanceCreationEvent " &_
"Within 5 Where TargetInstance " &_
"Isa 'CIM_DataFile' " & _
"And TargetInstance.Name = 'C:\\test.csv'"

objEventFilter.Put_

'create an instance of ActiveScripteventConsumer class

Set objConsumerClass = objSWbemServices.Get _
("ActiveScriptEventConsumer")
Set objConsumer = objConsumerClass.SpawnInstance_()

objConsumer.Name = "TestConsumer"
objConsumer.ScriptFileName = "C:\Consumer.vbs"
'it is .ScriptingEngine instead of .ScriptEngine
objConsumer.ScriptingEngine = "VBScript"

objConsumer.Put_

'need to refresh objEventFilter and objConsumer
'before I can use their Path_ property

objEventFilter.Refresh_
objConsumer.Refresh_

'create an instance of __FilterToConsumerBinding class

Set objBindingClass = objSWbemServices.Get("__FilterToConsumerBinding")
Set objBindingInstance = objBindingClass.SpawnInstance_()

'Filter and Consumer properties are strings containing the absolute path
'to __EventFilter and ActiveScriptEventConsumer instances just created.
' If I want to hardcode the paths I need to use double quotes for Name
property
'doesn't work without '\\.\' part

objBindingInstance.Filter = objEventFilter.Path_
objBindingInstance.Consumer = objConsumer.Path_

objBindingInstance.Put_()


This code first creates an event filter that polls the repository every 5
seconds for a file creation event (the file name here is c:\test.csv, but you
can change it). Then it creates an active script event consumer instance and
sets it's ScriptFileName property to c:\consumer.vbs. This is the VBScript
file that will be executed each time a new file (named c:\test.csv) is
created. Then it uses __FilterToConsumerBinding to associate the event filter
and the consumer.

You only need to run this code once, and the subscription is stored in the
repository permanently. While testing or when you want to cancel the
subscription you need to delete all three instances from the repository (you
can use another script or utilities like wbemtest.exe or CIM Studio).

Consumer.vbs can contain the code for the .csv file processing. This is the
test code I used, it just saves a .csv file as an Excel workbook, but you can
change this to your needs:


Set objExcel = CreateObject _
("Excel.Application")
objExcel.DisplayAlerts = False

Set objBook = objExcel.Workbooks.Open ("C:\test.csv")

objBook.SaveAs "C:\Test.xls"
objExcel.Quit

Active script event consumer has limitations, you can't make Excel visible,
no MsgBoxes etc. but I think you can do what you need with it.

The VBS script you have is probably a temporary event consumer. It monitors
a folder for file creation while it is running, but I think permanent event
consumers are more reliable, you have the subscription stored in the WMI
repository so you don't need to have a script running all the time. WMI
receives an event from the file system and only then runs a script.

Hope this helps some.


--
urkec
.

Relevant Pages

  • Re: Detect cluster group move
    ... and report on changes by using WMI. ... You can script this and there are ... Essentially you create a mof file that describes the filter that you ... also be able to get some information about why the group or resource ...
    (microsoft.public.scripting.vbscript)
  • wmi filter script
    ... I'm trying to learn some WMI for a specific task but i've got myself a bit ... I want to filter a policy to run if a certain file on the computer hard ... Is it possible to do this and could someone possibly tell me what the script ... Prev by Date: ...
    (microsoft.public.windows.server.scripting)
  • RE: Does WMI have to be installed on Remote Computer?
    ... IMHO this looks like a well constructed and very vell commented script. ... WMI comes as standard on w2k w2k3 and xp, ... does WMI need to be installed on the remote computer in order for a ... ''' First try to ping the server. ...
    (microsoft.public.scripting.vbscript)
  • Re: Look over this script.....could anything be done better?
    ... will be unable to connect with WMI. ... With this version of the script I have kept that included because the script that enumerates all of the servers has some servers that no longer exist. ... Dim objWMIService, colFixedDrives, objFD ...
    (microsoft.public.scripting.vbscript)
  • Re: Create simple file share on the fly
    ... WMI share routine faithfully works. ... You can place the command line below as the final line in your WMI ... You might experiment with actual workstations to see if the script is ... the shares that I make from the Windows GUI, ...
    (microsoft.public.windowsxp.security_admin)