Excel Programming Question - VBA + WMI
 

Hello all,

I am a fairly proficient vbscript programmer using WMI and ADSI, but
recently I have had to get involved with doing some programming in
Excel and I am a bit lost on a few things. Basically, I am looking for
a little help with the following things:

1. Is there any way to programmatically build a new row in excel when
someone adds a new value to a spreadsheet. For example, when someone
puts a computer name in A:2 (this being the next empty row), can I
somehow enter value in B2, C2, etc. I am specifically looking to add a
drop down list in H2 with the values Yes & No, with Yes being the
default. I also want to add sone specific values in cells in the same
row based on some instr() or instrRev() code that I want to run against
the value entered in B1.

2. Can I call a script that runs a WMI or ADSI query written in
vbscript from within excel? Again, this would probably be fired off of
values being entered in the first cell of the next empty row.

3. Finally, I have a series of worksheets (tabs) based on domain names.
How could I read the names of the tabs and output the names into a
range of cells? Example, tab 1 is mydomain and that would be in G1, tab
2 is mydomain2 and that would be in G2, etc.

I appreciate any assistance on this matter. Thank you all in advance.

Jim Gregg

Excel Programming Question - VBA + WMI
 

Use the Change event

Right click on the sheet tab and select View Code (this brings up the Class
Module associated with that worksheet)

In the left dropdown, select Worksheet. In the Right Dropdown Select Change
(not selection change)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

End Sub

Now you can built your code there

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.count 1 then exit sub
If Target.Column = 1 then
' apply validation, modified from
' macro recorder
cells(Target.Row,"B").Value = 21
cells(Target.Row,"C").Formula = "=rand()"
cells(Target.Row,"D").Formula = "=if(" & _
"A" & target.row & "5,""High"",""Low"")"
With Cells(Target.row,"H").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="Yes,No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

' code to do other entries
End if
End Sub

------
For your vbscript, why not move the code to Excel and run it there -
particularly if you are returning result.. Alternately, look at the shell
command

--------

Dim sh as worksheet, sh1 as Worksheet
Dim i as Long
set sh1 = Worksheets("Data")
i = 1
for each sh in Worksheets
if sh.Name < "Data" then
sh1.Cells(i,"G").Value = sh.Name
i = i + 1
end if
Next

--
Regards,
Tom Ogilvy


"Jim Gregg" wrote in message
ups.com...
Hello all,

I am a fairly proficient vbscript programmer using WMI and ADSI, but
recently I have had to get involved with doing some programming in
Excel and I am a bit lost on a few things. Basically, I am looking for
a little help with the following things:

1. Is there any way to programmatically build a new row in excel when
someone adds a new value to a spreadsheet. For example, when someone
puts a computer name in A:2 (this being the next empty row), can I
somehow enter value in B2, C2, etc. I am specifically looking to add a
drop down list in H2 with the values Yes & No, with Yes being the
default. I also want to add sone specific values in cells in the same
row based on some instr() or instrRev() code that I want to run against
the value entered in B1.

2. Can I call a script that runs a WMI or ADSI query written in
vbscript from within excel? Again, this would probably be fired off of
values being entered in the first cell of the next empty row.

3. Finally, I have a series of worksheets (tabs) based on domain names.
How could I read the names of the tabs and output the names into a
range of cells? Example, tab 1 is mydomain and that would be in G1, tab
2 is mydomain2 and that would be in G2, etc.

I appreciate any assistance on this matter. Thank you all in advance.

Jim Gregg

Excel Programming Question - VBA + WMI
 

Very nice. THank you for the quick results. Just one question regarding
the section on vbscript/wmi. You stated that I could just move my
vbscript to excel. I guess what I was unsure of was whether or not WMI
or ADSI commands would be recognized from within Excel. I have used
external vbscript before to interact with Excel, but I have never
initiated the code from within an excel module.

Excel Programming Question - VBA + WMI
 

Very nice. THank you for the quick results. Just one question regarding
the section on vbscript/wmi. You stated that I could just move my
vbscript to excel. I guess what I was unsure of was whether or not WMI
or ADSI commands would be recognized from within Excel. I have used
external vbscript before to interact with Excel, but I have never
initiated the code from within an excel module.

Excel Programming Question - VBA + WMI
 

I haven't use WMI, but it isn't native to vbscript I assume. You use
something like

Set objSWbemServices = GetObject("winmgmts:")

I would imagine.


this should work directly in Excel VBA as well.

Or create a reference to it in Tools=References in VBA.

I don't see a problem.

--
Regards,
Tom Ogilvy


"Jim Gregg" wrote in message
oups.com...
Very nice. THank you for the quick results. Just one question regarding
the section on vbscript/wmi. You stated that I could just move my
vbscript to excel. I guess what I was unsure of was whether or not WMI
or ADSI commands would be recognized from within Excel. I have used
external vbscript before to interact with Excel, but I have never
initiated the code from within an excel module.

Excel Programming Question - VBA + WMI
 

Hello Jim , Hello Tom

some examples of WMI procedures adapted in Excel


Sub Win32_Account_TestExcel()
Dim Fso As Object, Rapport As Object
Dim WmObj As Object, Test As Object
Dim Valeur As Object, Ws As Object

On Error Resume Next
Set Fso = CreateObject("Scripting.FileSystemObject")
Set Rapport = Fso.OpenTextFile("C:\rapport.txt", 2, True)

Set WmObj = GetObject("WinMgmts:{impersonationLevel=impersonat e}")

Set Test = WmObj.ExecQuery("Select * from Win32_Account")
For Each Valeur In Test
Rapport.WriteLine ("Nom : " & Valeur.name)
Rapport.WriteLine ("Description : " & Valeur.Description)
Rapport.WriteLine ("Domaines : " & Valeur.Domain)
Rapport.WriteLine ("SID : " & Valeur.SID)
Rapport.WriteLine ("------------------------------")
Next

Set Ws = CreateObject("WScript.Shell")
ActiveWorkbook.FollowHyperlink Address:="C:\rapport.txt"
End Sub



Sub listeHotFixs()
Dim strComputer As String
Dim objWMIService As Object, objQuickFix As Object, colQuickFixes As
Object

strComputer = "."

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set colQuickFixes = objWMIService.ExecQuery("Select * from
Win32_QuickFixEngineering")

For Each objQuickFix In colQuickFixes
Debug.Print "Computer: " & objQuickFix.CSName
Debug.Print "Description: " & objQuickFix.Description
Debug.Print "Hot Fix ID: " & objQuickFix.HotFixID
Debug.Print "Installation Date: " & objQuickFix.InstallDate
Debug.Print "Installed By: " & objQuickFix.InstalledBy
Next
End Sub



Sub printersProperties()
Dim objWMIService As Object, colItems As Object
Dim objItem As Object
Dim strComputer As String
Dim i As Byte

On Error Resume Next
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer &
"\root\cimv2")
Set colItems = objWMIService.ExecQuery("Select * from
Win32_PrinterConfiguration", , 48)

For Each objItem In colItems
i = i + 1
Cells(1, i) = "BitsPerPel: " & objItem.BitsPerPel
Cells(2, i) = "Caption: " & objItem.Caption
Cells(3, i) = "Collate: " & objItem.Collate
Cells(4, i) = "Color: " & objItem.Color
Cells(5, i) = "Copies: " & objItem.Copies
Cells(6, i) = "Description: " & objItem.Description
Cells(7, i) = "DeviceName: " & objItem.DeviceName
Cells(8, i) = "DisplayFlags: " & objItem.DisplayFlags
Cells(9, i) = "DisplayFrequency: " & objItem.DisplayFrequency
Cells(10, i) = "DitherType: " & objItem.DitherType
Cells(11, i) = "DriverVersion: " & objItem.DriverVersion
Cells(12, i) = "Duplex: " & objItem.Duplex
Cells(13, i) = "FormName: " & objItem.FormName
Cells(14, i) = "HorizontalResolution: " & objItem.HorizontalResolution
Cells(15, i) = "ICMIntent: " & objItem.ICMIntent
Cells(16, i) = "ICMMethod: " & objItem.ICMMethod
Cells(17, i) = "LogPixels: " & objItem.LogPixels
Cells(18, i) = "MediaType: " & objItem.MediaType
Cells(19, i) = "Name: " & objItem.Name
Cells(20, i) = "Orientation: " & objItem.Orientation
Cells(21, i) = "PaperLength: " & objItem.PaperLength
Cells(22, i) = "PaperSize: " & objItem.PaperSize
Cells(23, i) = "PaperWidth: " & objItem.PaperWidth
Cells(24, i) = "PelsHeight: " & objItem.PelsHeight
Cells(25, i) = "PelsWidth: " & objItem.PelsWidth
Cells(26, i) = "PrintQuality: " & objItem.PrintQuality
Cells(27, i) = "Scale: " & objItem.Scale
Cells(28, i) = "SettingID: " & objItem.SettingID
Cells(29, i) = "SpecificationVersion: " & objItem.SpecificationVersion
Cells(30, i) = "TTOption: " & objItem.TTOption
Cells(31, i) = "VerticalResolution: " & objItem.VerticalResolution
Cells(32, i) = "XResolution: " & objItem.XResolution
Cells(33, i) = "YResolution: " & objItem.YResolution

Columns(i).AutoFit
Next
End Sub


...etc...



you can also activate the "Microsoft WMI Scripting Library" and use
Variables like this

Dim objWMIService As WbemScripting.SWbemServices
Dim colItems As WbemScripting.SWbemObjectSet
Dim objItem As WbemScripting.SWbemObject



Regards
michel


--
michelxld
------------------------------------------------------------------------
michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367
View this thread: http://www.excelforum.com/showthread...hreadid=508604

Excel Programming Question - VBA + WMI
 

Ooupss...

in the first procedure (Win32_Account_TestExcel)

replace

impersonationLevel=impersonat e

by

impersonationLevel=impersonate


Regards
michel


--
michelxld
------------------------------------------------------------------------
michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367
View this thread: http://www.excelforum.com/showthread...hreadid=508604