We help IT Professionals succeed at work.

VBScript write output to Excel row after row

SnAkEhIpS
SnAkEhIpS asked
on
3,452 Views
Last Modified: 2012-05-08
I've got an array of remote hosts from which I'm collecting WMI info. I want to export the output to a new Excel spreadsheet. I want the 4 pieces of WMI info that I retrieve from each computer to be written on a separate row (i.e., row 1 for host 1, row 2 for host 2, row 3 for host 3, etc.).

To clarify:
This is row 1, host 1 ------->  Host Name | Make | Model | Serial Number
This is row 2, host 2 ------->  Host Name | Make | Model | Serial Number

I'm a newbie. Forgive my sloppy form:
 
On Error Resume Next
 
arrComputers = Array("10.109.45.100","10.109.45.101","10.109.45.102","10.109.45.103","10.109.45.104","10.109.45.105")
 
For Each strComputer In arrComputers
 
 
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" _ 
    & strComputer & "\root\cimv2") 
 
Set colComputer = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")
 
Set colSerial = objWMIService.ExecQuery _
          ("Select * from Win32_ComputerSystemProduct")
 
	For Each objComputer in colComputer
  		Wscript.Echo objComputer.Name
		Wscript.Echo objComputer.Manufacturer
		Wscript.Echo objComputer.Model
	Next
 
	For Each objItem in colSerial
		Wscript.Echo objItem.IdentifyingNumber
	Next
Next

Open in new window

Comment
Watch Question

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
The easiest way is just to write the data to a normal text file as a comma delimited file (CSV).  These can be opened in Excel and will automatically be parsed into rows and columns.  Let me know if you need help with that.

~bp

Author

Commented:
That would be helpful. How do I do that?
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
I'll post something up tomorrow, almost quiting time for me here, is that okay?

~bp

Author

Commented:
Sure. Have a good evening.
ok, sorry to barge through like this but it took me about 10min to write this.  I set this up per the above discussion in that you will need an input file ie "whatever.txt"  have one IP or FQDN per line.  The script starts with a browse function for that file ( this must be ran with wscript).  Once you set the input file it will do the query and write it to a csv on the users desktop who is executing the script.  the sierial number part maynot show.  It pulls the field you stated that you wanted, but it might not be what you are looking for.  If you want the BIOS serial or another # let me know it is a 2 sec mod.
Const ForReading = 1
Dim sResultFile: sResultFile = "Results.csv"' you can change the name of the result file here
Dim sSourceFile : sSourceFile = BrowseForFile("c:\") ' root of Browse for file
Dim oFso : Set oFso = CreateObject("Scripting.FileSystemObject")
Dim oSourceFile : Set oSourceFile = oFso.OpenTextFile (sSourceFile,ForReading)
Dim oResults : Set oResults = oFso.CreateTextFile (GetEnvirmomentVariable("%userprofile%") & "\desktop\" & sResultFile, True)
Do Until oSourceFile.AtEndOfStream
	oResults.WriteLine Win32_ComputerSystem(oSourceFile.ReadLine)
loop
WScript.Echo sSourceFile
Function BrowseForFile (path)
Dim objDialog
Set objDialog = CreateObject("UserAccounts.CommonDialog")
With objDialog
	.Filter = "Text Documents|*.txt"
	.Flags = &H80000
	.FilterIndex = 1
	.InitialDir = path
End With
intResult = objDialog.ShowOpen
If intResult = 0 Then
    Wscript.Quit
Else
    BrowseForFile = objDialog.FileName
End If
End Function
Function GetEnvirmomentVariable(tmp)
Dim  objShell
Set objShell = CreateObject("WScript.Shell")
GetEnvirmomentVariable = objShell.ExpandEnvironmentStrings(tmp)
End Function
Function Win32_ComputerSystem (strComputerAccount)
On Error Resume Next
'Variables
'=============================================================================================
Dim objWMI,objPCAttribute,objPC,sTemp
'=============================================================================================
'Initalize WMI
'=============================================================================================
Set objWMI = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputerAccount & "\root\cimv2")
Set objPC = objWMI.ExecQuery _
    ("Select * from Win32_ComputerSystem")
Set colSerial = objWMIService.ExecQuery _
          ("Select * from Win32_ComputerSystemProduct")
 
'=============================================================================================
'Get Attribues from WIN32_Computersystem
'=============================================================================================
For Each objPCAttribute In objPC
	sTemp = objPCAttribute.Name
	sTemp = sTemp & "," & objPCAttribute.Manufacturer
	sTemp =  sTemp & "," &objPCAttribute.Model
Next
For Each objItem in colSerial
		sTemp =  sTemp & "," & objItem.IdentifyingNumber
	Next
Win32_ComputerSystem = sTemp
End Function

Open in new window

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi there, here's another version that will output your current code to Excel, row after row.

Regards,

Rob.
On Error Resume Next
 
strExcelFileName = Replace(WScript.ScriptFullName, WScript.ScriptName, "") & "CompData.xls"
arrComputers = Array("10.109.45.100","10.109.45.101","10.109.45.102","10.109.45.103","10.109.45.104","10.109.45.105")
 
Set objExcel = CreateObject("Excel.Application")
Const xlUp = -4162
Set objWB = objExcel.Workbooks.Add
Set objSheet = objWB.Sheets(1)
objExcel.Visible = True
objSheet.Cells(1, 1) = "Computer"
objSheet.Cells(1, 2) = "Make"
objSheet.Cells(1, 3) = "Model"
objSheet.Cells(1, 4) = "Serial Number"
objSheet.Rows("1:1").Font.Bold = True
 
For Each strComputer In arrComputers
	Set objWMIService = GetObject("winmgmts:" _
	    & "{impersonationLevel=impersonate}!\\" _ 
	    & strComputer & "\root\cimv2") 
 
	Set colComputer = objWMIService.ExecQuery _
	    ("Select * from Win32_ComputerSystem")
 
	Set colSerial = objWMIService.ExecQuery _
		("Select * from Win32_ComputerSystemProduct")
 
	For Each objComputer in colComputer
  		objSheet.Cells(objSheet.Cells(65536, 1).End(xlUp).Row + 1, 1).Value = objComputer.Name
		objSheet.Cells(objSheet.Cells(65536, 1).End(xlUp).Row, 2).Value = objComputer.Manufacturer
		objSheet.Cells(objSheet.Cells(65536, 1).End(xlUp).Row, 3).Value = objComputer.Model
	Next
 
	For Each objItem in colSerial
		objSheet.Cells(objSheet.Cells(65536, 1).End(xlUp).Row, 4).Value = objItem.IdentifyingNumber
	Next
Next
 
objWB.SaveAs strExcelFileName

Open in new window

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
A suggestion (not for points) that Rob's solution would run quicker (which may be unnecessary) by either

  • Using a variant array rather than write cell by cell ( I can assist if needed)
  • Writing to a csv file rather than an open Excel file
Regards
Dave
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks for all of your effort!  I took the weekend off too , billprew. :-)  Rob, your method ran faster and the added error checking was a bonus. Thanks to you both.
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Glad you got a good solution.

~bp
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
No problem. Thanks for the grade.

Regards,

Rob.
CERTIFIED EXPERT

Commented:
Hi Rob,
This is a useful reference. I normally define a range, put in in the array, do the work, then dump the array back to the initial range
http://www.avdf.com/apr98/art_ot003.html
Cheers
Dave
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Hmmm, that seems like double-handling to me.  I don't use any Select statement, just direct references, so it only accesses each cell once, and I have never really had it work painfully slow, but I will certainly check out your method, and run some benchmark tests. I've some large inventory scripts that this may help with.  Thanks.

I did forget to turn off ScreenUpdating in my code though.

Rob.
CERTIFIED EXPERT

Commented:
Rob,
No probs.
As an example try running two codes in http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23721149.html  over an entire column
Cheers
Dave

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.