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
    Posts
    1

    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

  2. #2
    Frenzied Member
    Join Date
    Feb 2003
    Posts
    1,151

    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
  •  



Click Here to Expand Forum to Full Width