-
May 4th, 2021, 02:54 AM
#1
Thread Starter
New Member
Help Having Script Export To EXCEL\TSV File Instead of TXT File
Hi,
I have hacked together the below code to grab information from WMIC and currently have it export to a TXT document.
The aim of the script is for users to run it and it will automatically capture their details and their pc\monitor's details.
I am hoping someone can assist in making my code:
1) Write the data to an Excel\TSV file instead of TXT file into specified columns with headers.
e.g. Column A = NAME column - the code from below for Name would paste the data into A2 and so on.
2) A check so that if someone runs the script more than once on the second running they will get a message advising that their information has already been collected (I have seen this once before by checking the name \ pc serial against information in the Excel\TSV but cannot locate this code to use\edit for my purpose).
Thanks in advance for any and all help!
t0ny84
Code:
Function BytesToString(ByVal Bytes)
Dim Result, N
Result = ""
For N = 0 To UBound(Bytes)
If CInt(Bytes(N)) <> 0 Then
Result = Result & Chr(Bytes(N))
Else
Exit For
End If
Next
BytesToString = Result
End Function
Dim fso
Set fso = WScript.CreateObject("Scripting.Filesystemobject")
CurrentDirectory = fso.GetAbsolutePathName(".")
Set f = fso.createTextFile(currentdirectory & "\" & "output.txt", 2)
strComputer = "."
' WMI NameSpaces
Set objWMIServiceWMI = GetObject("winmgmts:\\" & strComputer & "\root\WMI")
Set objWMIServiceCIMV2 = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set MonItems = objWMIServiceWMI.ExecQuery("SELECT * FROM WmiMonitorID")
Set PCItems = objWMIServiceCIMV2.ExecQuery("SELECT * FROM Win32_BIOS")
Set colItems = objWMIServiceCIMV2.ExecQuery("SELECT * FROM Win32_ComputerSystem")
Set objSysInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" & objSysInfo.UserName)
If Not monItems Is Nothing Then
For Each objMon in monItems
If monSerial <> "" Then
monSerial = monSerial & ", "
End If
monSerial = monSerial & BytesToString(objMon.SerialNumberID)
Next
For Each objMon in monItems
If monUFN <> "" Then
monUFN = monUFN & ", "
End If
monUFN = monUFN & BytesToString(objMon.UserFriendlyName)
Next
End If
For each objItem in ColItems
f.WriteLine "-----------------------------------"
f.WriteLine "User Information:"
f.WriteLine "-----------------------------------"
f.WriteLine "ID: " & objUser.CN
f.WriteLine "Full Name: " & objUser.FullName
f.WriteLine " "
f.WriteLine "-----------------------------------"
f.WriteLine "PC Information:"
f.WriteLine "-----------------------------------"
f.WriteLine "Domain\User ID: " & objItem.UserName
f.WriteLine "PC Manufacturer: " & objItem.Manufacturer
f.WriteLine "PC Model: " & objItem.Model
f.WriteLine "Serial: " & objItem.Name
Next
For Each objItem in PCItems
f.WriteLine "PC Serial Number: " & objItem.SerialNumber
Next
For Each objItem in MonItems
f.WriteLine "-----------------------------------"
f.WriteLine "Monitor Data"
f.WriteLine "-----------------------------------"
If isNull(objItem.UserFriendlyName) Then
f.WriteLine "UserFriendlyName: "
Else
f.WriteLine "UserFriendlyName: " & BytesToString(objItem.UserFriendlyName) & vbNewLine & "Serial:" & BytesToString(objitem.SerialNumberID) & vbNewLine & "ManufacturerName: " & bytestostring(objItem.ManufacturerName) & vbNewLine & "YearOfManufacture: " & objItem.YearOfManufacture
End If
Next
Wscript.echo "End"
f.Close
-
May 7th, 2021, 08:41 AM
#2
Re: Help Having Script Export To EXCEL\TSV File Instead of TXT File
Hello t0ny84, welcome to forum!
Replace
Code:
Set f = fso.createTextFile(currentdirectory & "\" & "output.txt", 2)
with:
Code:
Dim OutputPath
OutputPath = currentdirectory & "\" & "output.txt"
If fso.FileExists(OutputPath) Then
WScript.Echo("Output has already been generated.")
WScript.Quit
End If
Set f = fso.createTextFile(OutputPath, 2)
That should warn the user if the script has been executed before.
As to generating a *.csv file instead of a text file, that shouldn't be too hard. You could do it the same way you create your text file. The only things to keep in mind are that columns are always delimited by a semicolon character (";") and rows by a line break.
This might help: https://en.wikipedia.org/wiki/Comma-...file%20format.
Also, always use "Option Explicit" at the top of your code. This will force you to declare any variables. This should make it easier to avoid typo's and keeping track of variables.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|