vbs output to csv format

Microsoft Windows vista ultimate w/ sp1...
March 8, 2011 at 23:55:49
Specs: windows vista, 256
thanks for allowing me to join this forum

the given below a vbs script which i googled and got which will take input from a text file compare with wmi for patches and gives output whether patches are installed or not...

now i want to make some modifications in the script:-

(a) the PCs hostname should me taken automatically from host commad and user no need to enter hostname
(b) Instead of showing output in open exel file, i want the output to be redirected to some csv file.

thanks in advance

the script follows :-


strComputer = InputBox ("Enter Machine Name")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "HotFix"

objExcel.Cells(1, 2).Value = strHotFixId & " Install Date"

Set Fso = CreateObject("Scripting.FileSystemObject")

Set InputFile = fso.OpenTextFile("patches.Txt")

Do While Not (InputFile.atEndOfStream)

strHotFixId = InputFile.ReadLine

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colItems = objWMIService.ExecQuery( _
"Select * from Win32_QuickFixEngineering Where HotFixID ='" & strHotFixId & "'")

If colItems.Count > 0 Then

For Each objItem In colItems

objExcel.Cells(intRow, 1).Value = strHotFixId

objExcel.Cells(intRow, 2).Value = objItem.InstalledOn

Next

Else

objExcel.Cells(intRow, 1).Value = strHotFixId

objExcel.Cells(intRow, 2).Value = "Not Installed"

If objExcel.Cells(intRow, 2).Value = "Not Installed" Then

objExcel.Cells(intRow, 1).Font.ColorIndex = 3

objExcel.Cells(intRow, 2).Font.ColorIndex = 3

Else

End If

End If

intRow = intRow + 1

Loop

objExcel.Range("A1:B1").Select

objExcel.Cells.HorizontalAlignment = 2

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

MsgBox "Done"


See More: vbs output to csv format

Report •

#1
March 9, 2011 at 01:14:48
the PCs hostname should me taken automatically from host commad and user no need to enter hostname
Not sure what you're asking for here. Do you mean you want it to work only on the local PC?

How To Ask Questions The Smart Way


Report •

#2
March 9, 2011 at 01:53:05
thanks for your reply

actually i was also behind the same script.///////////

yes you r correct that this script is meant for local PC and not remote PC...

and even i was able to remove that conditon "enter PC name."
Now this script will take host name automatically and will run wmi command and produce exel output.

But i want instead of poping up exel ouput , i want to redirect the output to some csv file.
please provide some suggestion.

my new modified script as follows

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "HotFix"

objExcel.Cells(1, 2).Value = strHotFixId & " Install Date"

Set Fso = CreateObject("Scripting.FileSystemObject")

Set InputFile = fso.OpenTextFile("patches.Txt")

Do While Not (InputFile.atEndOfStream)

strHotFixId = InputFile.ReadLine

Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
Set colItems = objWMIService.ExecQuery( _
"Select * from Win32_QuickFixEngineering Where HotFixID ='" & strHotFixId & "'")

If colItems.Count > 0 Then

For Each objItem In colItems

objExcel.Cells(intRow, 1).Value = strHotFixId

objExcel.Cells(intRow, 2).Value = objItem.InstalledOn

Next

Else

objExcel.Cells(intRow, 1).Value = strHotFixId

objExcel.Cells(intRow, 2).Value = "Not Installed"

If objExcel.Cells(intRow, 2).Value = "Not Installed" Then

objExcel.Cells(intRow, 1).Font.ColorIndex = 3

objExcel.Cells(intRow, 2).Font.ColorIndex = 3

Else

End If

End If

intRow = intRow + 1

Loop

objExcel.Range("A1:B1").Select

objExcel.Cells.HorizontalAlignment = 2

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit



Report •

#3
March 9, 2011 at 02:28:47
Untested:
Set objWMIService = GetObject("winmgmts:")
Set fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("patches.Txt")
Set outFile = fso.OpenTextFile("output.csv", 2, True)

Do Until (InputFile.atEndOfStream)
  strHotFixId = InputFile.ReadLine
  outFile.Write strHotFixId & ","
  Set colItems = objWMIService.ExecQuery( _
  "Select InstalledOn from Win32_QuickFixEngineering Where HotFixID ='" & strHotFixId & "'")
  
  If colItems.Count > 0 Then
    For Each objItem In colItems
      outFile.WriteLine objItem.InstalledOn
    Next
  Else
    outFile.WriteLine "Not Installed"
  End If
Loop

How To Ask Questions The Smart Way


Report •

Related Solutions

#4
March 9, 2011 at 03:25:18
thanks for the reply.........

it exactly matches my requirement


Report •

#5
March 9, 2011 at 22:59:03
suppose if i have a 3 patches files like
windowsxP_patches.txt,
windowsvista_patches.txt,
windows_7_patches.txt

can i make my the above script portable to take one of the above file as input based on the OS in which it is running............... and produce the output

some thing like switch statement ?
any idea plase
thanks in advance.


Report •

#6
March 10, 2011 at 10:36:09
Still untested:
Set objWMIService = GetObject("winmgmts:")
For Each os In objWMIService.InstancesOf("Win32_OperatingSystem")
  'Not a complete list, also excludes the server versions
  If os.BuildNumber >= 7600 Then
    outName = "windows_7"
  ElseIf os.BuildNumber >= 6000 Then
    outName = "windowsvista"
  ElseIf os.BuildNumber >= 2600 Then
    outName = "windowsxP"
  Else
    outName = "Win_Unknown"
  End If
Next 'os
outName = outName & "_patches.csv"

Set fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile("patches.Txt")
Set outFile = fso.OpenTextFile(outName, 2, True)

Do Until (InputFile.atEndOfStream)
  strHotFixId = InputFile.ReadLine
  outFile.Write strHotFixId & ","
  Set colItems = objWMIService.ExecQuery( _
  "Select InstalledOn from Win32_QuickFixEngineering Where HotFixID ='" & strHotFixId & "'")
  
  If colItems.Count > 0 Then
    For Each objItem In colItems
      outFile.WriteLine objItem.InstalledOn
    Next
  Else
    outFile.WriteLine "Not Installed"
  End If
Loop

How To Ask Questions The Smart Way


Report •

#7
March 10, 2011 at 12:36:30
I guess Reg value "Productname" under key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\ shows the correct OS. Not sure if that helps

Subhash Chandra.


Report •

#8
March 13, 2011 at 22:13:33
thanks this script is working ,

but if i have 3 files as input for example,
vista_patches.txt
seven_patches.txt
xp_patches.txt

how to match apropriate input file with OPERATING SYSTEM in which it runs

i tried with the script which follows, but it always takes input from xp_patches file even though it runs in VISTA operating system//////

please help

the script follows

Set objWMIService = GetObject("winmgmts:")
For Each os In objWMIService.InstancesOf("Win32_OperatingSystem")
'Not a complete list, also excludes the server versions
If os.BuildNumber >= 7600 Then
outName = "windows_7"
ElseIf os.BuildNumber >= 6000 Then
outName = "windowsvista"
ElseIf os.BuildNumber >= 2600 Then
outName = "windowsxP"
Else
outName = "Win_Unknown"
End If
Next 'os

outName = outName & "_patches.csv"

Set fso = CreateObject("Scripting.FileSystemObject")
If outName = "windows_7" Then
Set InputFile = fso.OpenTextFile("seven_patches.txt")
ElseIf outName = "windowsvista" Then
Set InputFile = fso.OpenTextFile("vista_patches.txt")
Else
Set InputFile = fso.OpenTextFile("xp_patches.txt")
End If
Set outFile = fso.OpenTextFile(outName, 2, True)

Do Until (InputFile.atEndOfStream)
strHotFixId = InputFile.ReadLine
outFile.Write strHotFixId & ","
Set colItems = objWMIService.ExecQuery( _
"Select InstalledOn from Win32_QuickFixEngineering Where HotFixID ='" & strHotFixId & "'")

If colItems.Count > 0 Then
For Each objItem In colItems
outFile.WriteLine objItem.InstalledOn
Next
Else
outFile.WriteLine "Not Installed"
End If
Loop


Report •

#9
March 14, 2011 at 05:33:33
Oh, I thought you wanted the output file renamed, not the input file. That's easy enough.

Once again, untested:

Set objWMIService = GetObject("winmgmts:")
For Each os In objWMIService.InstancesOf("Win32_OperatingSystem")
  'Not a complete list, also excludes the server versions
  If os.BuildNumber >= 7600 Then
    inName = "seven"
  ElseIf os.BuildNumber >= 6000 Then
    inName = "vista"
  ElseIf os.BuildNumber >= 2600 Then
    inName = "xp"
  Else
    WScript.Echo "Unknown or invalid build version. Aborting."
    WScript.Quit 1
  End If
Next 'os
inName = inName & "_patches.txt"

Set fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fso.OpenTextFile(inName)
Set outFile = fso.OpenTextFile("output.csv", 2, True)

Do Until (InputFile.atEndOfStream)
  strHotFixId = InputFile.ReadLine
  outFile.Write strHotFixId & ","
  Set colItems = objWMIService.ExecQuery("Select InstalledOn from " _
    & "Win32_QuickFixEngineering Where HotFixID ='" & strHotFixId & "'")
  
  If colItems.Count > 0 Then
    For Each objItem In colItems
      outFile.WriteLine objItem.InstalledOn
    Next
  Else
    outFile.WriteLine "Not Installed"
  End If
Loop

How To Ask Questions The Smart Way


Report •

Ask Question