Results 1 to 2 of 2

Thread: Help Having Script Export To EXCEL\TSV File Instead of TXT File

  1. #1

    Thread Starter
    New Member
    Join Date
    May 2021

    Help Having Script Export To EXCEL\TSV File Instead of TXT File


    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!


     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))
          Exit For
        End If
      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)
    For Each objMon in monItems
    	If monUFN <> "" Then
    	monUFN = monUFN & ", "
    	End If
    	monUFN = monUFN & BytesToString(objMon.UserFriendlyName)
    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
    For Each objItem in PCItems
        f.WriteLine "PC Serial Number: " & objItem.SerialNumber
    For Each objItem in MonItems 
        f.WriteLine "-----------------------------------"
        f.WriteLine "Monitor Data"
        f.WriteLine "-----------------------------------"
        If isNull(objItem.UserFriendlyName) Then
            f.WriteLine "UserFriendlyName: "
            f.WriteLine "UserFriendlyName: " & BytesToString(objItem.UserFriendlyName) & vbNewLine & "Serial:" &  BytesToString(objitem.SerialNumberID) & vbNewLine & "ManufacturerName: " & bytestostring(objItem.ManufacturerName) & vbNewLine & "YearOfManufacture: " & objItem.YearOfManufacture
    End If
    Wscript.echo "End"

  2. #2
    Frenzied Member
    Join Date
    Feb 2003

    Re: Help Having Script Export To EXCEL\TSV File Instead of TXT File

    Hello t0ny84, welcome to forum!

    Set f = fso.createTextFile(currentdirectory & "\" & "output.txt", 2)

    Dim OutputPath
    OutputPath = currentdirectory & "\" & "output.txt"
    If fso.FileExists(OutputPath) Then
       WScript.Echo("Output has already been generated.")
    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:

    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

Click Here to Expand Forum to Full Width