Apache POI: Writing to Excel

//Download  Apache Poi Jar from https://www.apache.org/dyn/closer.lua/poi/release/bin/poi-bin-4.0.1-20181203.zip

2. Add in eclipse project buildpath ( all the jars from all the folders after extraction)


3.Try below code


package dummyarti;  //created a new package
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class PoiData {

public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub

//read the excel in fr object
FileInputStream fr=new FileInputStream("D:\\TstArea\\data.xlsx");

               //passed the excel to workbook
XSSFWorkbook wb=new XSSFWorkbook(fr);
XSSFSheet ws=wb.getSheet("Sheet1") ;



//Created reference to first row .Counting starts from 0
XSSFRow row=ws.createRow(0);

               //0 below indicated first column
XSSFCell cell = row.createCell(0);

//writes to 0,0 cell
cell.setCellValue("SoftwareTestingMaterial.com");

//Saves and closes

FileOutputStream out = new FileOutputStream(new File("D:\\TstArea\\data.xlsx"));
        wb.write(out);
        wb.close();






}

}

Finding Nth Highest Salary

Employee

Employee ID	Salary
3	200
4	800
7	450

Query:

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

How does the query above work?

The query above can be quite confusing if you have not seen anything like it before – pay special attention to the fact that “Emp1” appears in both the subquery (also known as an inner query) and the “outer” query. The outer query is just the part of the query that is not the subquery/inner query – both parts of the query are clearly labeled in the comments.

The subquery is a correlated subquery

The subquery in the SQL above is actually a specific type of subquery known as a correlated subquery. The reason it is called a correlated subquery is because the the subquery uses a value from the outer query in it’s WHERE clause. In this case that value is the Emp1 table alias as we pointed out earlier. A normal subquery can be run independently of the outer query, but a correlated subquery can NOT be run independently of the outer query. If you want to read more about the differences between correlated and uncorrelated subqueries you can go here: Correlated vs Uncorrelated Subqueries.

The most important thing to understand in the query above is that the subquery is evaluated each and every time a row is processed by the outer query. In other words, the inner query can not be processed independently of the outer query since the inner query uses the Emp1 value as well.

Finding nth highest salary example and explanation

Let’s step through an actual example to see how the query above will actually execute step by step. Suppose we are looking for the 2nd highest Salary value in our table above, so our N is 2. This means that the query will look like this:
SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)


You can probably see that Emp1 and Emp2 are just aliases for the same Employee table – it’s like we just created 2 separate clones of the Employee table and gave them different names.

Understanding and visualizing how the query above works

Let’s assume that we are using this data:
Employee

Employee ID
Salary
3
200
4
800
7
450


For the sake of our explanation, let’s assume that N is 2 – so the query is trying to find the 2nd highest salary in the Employee table. The first thing that the query above does is process the very first row of the Employee table, which has an alias of Emp1.



The salary in the first row of the Employee table is 200. Because the subquery is correlated to the outer query through the alias Emp1, it means that when the first row is processed, the query will essentially look like this – note that all we did is replace Emp1.Salary with the value of 200:
SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > 200)


So, what exactly is happening when that first row is processed? Well, if you pay special attention to the subquery you will notice that it’s basically searching for the count of salary entries in the Employee table that are greater than 200. Basically, the subquery is trying to find how many salary entries are greater than 200. Then, that count of salary entries is checked to see if it equals 1 in the outer query, and if so then everything from that particular row in Emp1 will be returned.

Note that Emp1 and Emp2 are both aliases for the same table – Employee. Emp2 is only being used in the subquery to compare all the salary values to the current salary value chosen in Emp1. This allows us to find the number of salary entries (the count) that are greater than 200. And if this number is equal to N-1 (which is 1 in our case) then we know that we have a winner – and that we have found our answer.



But, it’s clear that the subquery will return a 2 when Emp1.Salary is 200, because there are clearly 2 salaries greater than 200 in the Employee table. And since 2 is not equal to 1, the salary of 200 will clearly not be returned.

So, what happens next? Well, the SQL processor will move on to the next row which is 800, and the resulting query looks like this:
SELECT *
FROM Employee Emp1
WHERE (1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > 800)


Since there are no salaries greater than 800, the query will move on to the last row and will of course find the answer as 450. This is because 800 is greater than 450, and the count will be 1. More precisely, the entire row with the desired salary would be returned, and this is what it would look like:

EmployeeID 
Salary
7 
450



It’s also worth pointing out that the reason DISTINCT is used in the query above is because there may be duplicate salary values in the table. In that scenario, we only want to count repeated salaries just once, which is exactly why we use the DISTINCT operator.

A high level summary of how the query works


Let’s go through a high level summary of how someone would have come up with the SQL in the first place – since we showed you the answer first without really going through the thought process one would use to arrive at that answer.

Think of it this way – we are looking for a pattern that will lead us to the answer. One way to look at it is that the 2nd highest salary would have just one salary that is greater than it. The 4th highest salary would have 3 salaries that are greater than it. In more general terms, in order to find the Nth highest salary, we just find the salary that has exactly N-1 salaries greater than itself. And that is exactly what the query above accomplishes – it simply finds the salary that has N-1 salaries greater than itself and returns that value as the answer.

Attach Result to QC test run

Public Function addAttachToTDRun (strFileName)
Dim CurrentRun, attachment

On error resume next
If QCUtil.IsConnected Then
Set CurrentRun =QCUtil.CurrentRun
Set attachment = CurrentRun.Attachments
If Err.Number = 424 Then
Reporter.ReportEvent micWarning, "Not running through QC so can't save results", ""
Else
Reporter.ReportEvent micDone, "Attachment Saved to QC run data", strFileName
Set attlist = attachment.NewList("")
Set att = attachment.AddItem(Null) 
att.FileName = strFileName 
att.Type = 1
att.Post 
att.Save False 
End If
Else 

Reporter.ReportEvent micWarning, "Not connected", "Not connected to Quality Center" 

End If


On error goto 0

End Function 

Zipping a file with VBscript and WSH

ArchiveFolder "sub\foo.zip", "..\baz"

Sub ArchiveFolder (zipFile, sFolder)

    With CreateObject("Scripting.FileSystemObject")
        zipFile = .GetAbsolutePathName(zipFile)
        sFolder = .GetAbsolutePathName(sFolder)

        With .CreateTextFile(zipFile, True)
            .Write Chr(80) & Chr(75) & Chr(5) & Chr(6) & String(18, chr(0))
        End With
    End With

    With CreateObject("Shell.Application")
        .NameSpace(zipFile).CopyHere .NameSpace(sFolder).Items

        Do Until .NameSpace(zipFile).Items.Count = _
                 .NameSpace(sFolder).Items.Count
            WScript.Sleep 1000

     Loop
    End With

End Sub

TEST COMPLETE: ERR COUNT

Sub ErrCount()

  ' ...
  
  ' Obtains the total number of errors
  ' posted to the current test log
  ErrCount = Log.ErrCount
  
  ' Checks whether there are some errors
  If ErrCount > 0 Then
    Log.Message("There is(are) " & ErrCount & " error(s) in the test log.")
  Else
    Log.Message("There are no errors in the test log.") 
  End If
  
End Sub

TEST COMPLETE: LOG DISABLING

Sub EnableTestLog()

  ' Posts a message to the test log
  Log.Message("First message.")
  
  ' Disables posting messages to the test log
  Log.Enabled = false
  ' Posts a warning to the test log
  Log.Warning("This warning won't be posted.")
  ' ...
  ' All the messages are ignored
  ' ...
  
  ' Enables posting messages to the test log
  Log.Enabled = true
  
  ' Posts the event to the test log
  Log.Event("Test event.")
     
End Sub

' The procedure produces the following output:

' First message.
' Test event.

Closing Open Browsers

Public Function CloseProcess(strProgramName)
   Err.Clear
   On Error Resume Next
   Dim objShell
   a=split(strProgramName,":")
   Set objShell = CreateObject("WScript.Shell")
  
   For Iterator = 0 To ubound(a) Step 1
         objShell.Run "TASKKILL /F /IM " & a(Iterator)
   Next
   
 
    Set objShell = Nothing
   
    If err.number<>0 Then
      Reporter.ReportEvent micFail,"Closing Open Browsers","Step Failed"
    Else
      Reporter.ReportEvent micPass,"Closing Open Browsers","Step Passed"
    End If
End Function

CloseProcess "chrome.exe:iexplore.exe"

Setting.WebPackage : Simulating Keyboard and mouse operations

Simulating Keyboard and mouse operations in UFT using settings.replay type

In QTP, We can replicate device events, i.e keyboard and mouse operation using the below line of code.

Setting.WebPackage("ReplayType") = 2

By default, value of the Setting is 1, in which UFT interacts with application using the browser through DOM.

Setting.WebPackage("ReplayType") = 1

In case, you are not able to perform click or operation on an element in the page, please give a try by providing setting:

 Setting.WebPackage("ReplayType") =2

Excute Global Use

GWKB2035 : Using ExecuteGlobal to Include VBScript Code

Product: Window-Eyes
Author: Aaron Smith
Date Added: 08/16/2013
Last Modified: 08/16/2013

---

Many programming languages include the ability to include external code to make common code tasks available without having to re-type the code every time you want to use it. Although VBScript lacks an explicit method of including external code files, or code snippets, the ExecuteGlobal statement can be used to provide this missing functionality.

ExecuteGlobal executes a string of VBScript code in a script's global namespace, meaning that the executed code will be available anywhere in the script. Variables, sub routines and functions can be included in the executed string, and their respective declarations will be available any time you need them throughout the rest of your code.

Let's take a look at some examples.

Example 1 - Including a Simple Code Snippet

A code snippet, in the context of ExecuteGlobal, refers to a string of code (simple or complex) rather than an entire, external VBScript file.

Dim result : result = 0
Dim a : a = 5
Dim b : b = 10
ExecuteGlobal "result = a + b"
Speak result

In this example, the code snippet is the string following the ExecuteGlobal statement. After reading through the previous lines of code, what value do you think the variable result will contain? Let's examine each line, and see what happens.

Dim result : result = 0

This line declares a variable named result, and sets its initial value to 0.

Dim a : a = 5

This line declares a variable named a, and sets its initial value to 5.

Dim b : b = 10

This line declares a variable named b, and sets its initial value to 10.

ExecuteGlobal "result = a + b"

This is the line that does the work by executing the string of code. The string says, "let result equal the value of a plus b." Since a is equal to 5, and b is equal to 10, result would contain the addition of the two values: 15. ExecuteGlobal is nothing more than a VBScript statement saying, "Do the stuff inside this string," which would in fact set the result variable equal to the sum of a plus b (again, 15).

Speak result

The final line of this example causes Window-Eyes to speak the value contained in the result variable, which as we know now to be 15.

While it may make more sense to simply use result = a + b (or even more concise, Speak a + b), this example does demonstrate how ExecuteGlobal executes the code inside of a string.

Example 2 - Including a Complex Code Snippet

The usefulness of ExecuteGlobal is apparent when including more complex structures, such as a sub routine.

Dim subStr : subStr = "Sub HelloWorld() : MsgBox ""Hello World!"" : End Sub"
ExecuteGlobal subStr

If you were to run a script including these two lines, what would you expect to happen? If you said nothing, you would be correct. Well, nothing obvious, that is. Although you receive no verbal indication that the code executed successfully, ExecuteGlobal did what it was told to do; it executed the code inside of the string provided. In this example, ExecuteGlobal executed a sub routine called HelloWorld, and now a HelloWorld sub exists for use throughout the rest of the script. At any time, we could say:

HelloWorld

to display a message box containing the text "Hello World!" If you attempted to call HelloWorld without first using ExecuteGlobal to execute the string containing the sub routine, you would get an error, because the sub routine HelloWorld does not exist until ExecuteGlobal causes the string to get executed.

Example 3 - Including an External File

While including code snippets is certainly useful (the GW Toolkit script provides a complex string for setting up script error reporting for use with ExecuteGlobal), the true power of being able to execute external code comes in the way of including external VBScript files.

Lets say you have a script called myfunctions.vbs which includes several functions you find useful when developing scripts. Rather than re-typing, or copying and pasting those functions every time you want to use them, you can use ExecuteGlobal to execute the functions in the global namespace of your current script.

Dim fsObj : Set fsObj = CreateObject("Scripting.FileSystemObject")
Dim vbsFile : Set vbsFile = fsObj.OpenTextFile("myfunctions.vbs", 1, False)
Dim myFunctionsStr : myFunctionsStr = vbsFile.ReadAll
vbsFile.Close
Set vbsFile = Nothing
Set fsObj = Nothing
ExecuteGlobal myFunctionsStr

Let's examine each of these lines to see how the external VBScript file was included.

Dim fsObj : Set fsObj = CreateObject("Scripting.FileSystemObject")

This line declares the fsObj variable, and sets its value to a FileSystemObject object (an object provided by the operating system used to manipulate directories and files) created with the CreateObject method.

Dim vbsFile : Set vbsFile = fsObj.OpenTextFile("myfunctions.vbs", ForReading)

This line declares the vbsFile object, and sets its value to an object (a TextStream object, to be precise) returned by the OpenTextFile method of the FileSystemObject object (which was previously stored in the fsObj variable). The parameter ForReading indicates that we're interested in obtaining the contents of the file (instead of writing contents to the file).

Dim myFunctionsStr : myFunctionsStr = vbsFile.ReadAll

This line declares the myFunctionsStr variable, and sets its value to the contents of the file stored in the vbsFile object using the TextStream object's ReadAll method.

vbsFile.Close

This line closes the file that we opened. It is very important to remember to close files that are opened so that they don't become locked, and inaccessible in the future.

Set vbsFile = Nothing

This line releases the memory that the vbsFile variable was using. We don't need this object anymore as we've already stored the contents of the file in another variable.

Set fsObj = Nothing

This line releases the memory that the fsObj variable was using. We don't need this object anymore as we're done reading from our external file.

ExecuteGlobal myFunctionsStr

Once again, ExecuteGlobal does the work by executing the contents of the myFunctionsStr string, which at this point contains the entire contents of our external myfunctions.vbs file. Now the variables and functions that reside in the myfunctions.vbs file are included in memory, accessible from any point in the script. For example, perhaps you have a function called OSVersionNumber, which contained the major and minor build numbers of the operating system, along with the installed service pack version number. Prior to using ExecuteGlobal, you would have had to re-type, or copy and paste the entire OSVersionNumber function in your current script. Having executed the contents of your external file containing the function, however, you can now access the function as if it existed in your script explicitly, by doing something like:

If OSVersionNumber < 12.34.5 Then
   MsgBox "Operating System Not Supported"
End If

Function to display type ahead list in a dropdown

To display content in Typeahead type of dropdowns, use following code
Steps:
1) Enter full text in Dropdown

2) Create a Shell Object

3) Send keys: END and then backspace

4) Typeahead value will be displayed from Dropdown

Code:

'Function to display type ahead list in a dropdown'
Function Type_Ahead(browser_name,page_name,object_name,ParamValue)
'1) Enter full text in Dropdown'
  Browser(browser_name).Page(page_name).WebEdit(object_name).Set ParamValue
     wait 1
  Browser(browser_name).Page(page_name).WebEdit(object_name).Click
  
'2) Create a Shell Object'  
    Set WshShell = CreateObject("WScript.Shell")
    
'3) Send keys: END and then backspace'    
    WshShell.SendKeys "{END}"    
    WshShell.SendKeys "{BACKSPACE}"    
    
'4) Typeahead value will be displayed from Dropdown'    
    Set WshShell= Nothing

End Function

Read Excel using ADODB connection

Read Excel using ADODB connection

   

To open and read Excel data using ADODB connection , use following code:

Steps-

1) Create an ADODB connection

2) Open connection

3) check if Error then Exit Function

4) Create a Recordset

5) Execute SQL and store results in reocrdset

6) Read all fields data

7) Close and Discard all variables 


Code-
Dim objAdodbCon, objAdodbRecSet
'1) Create an ADODB connection'
Set objAdodbCon = CreateObject("ADODB.Connection")

'2) Open connection
objAdodbCon.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ="&strFileName & ";Readonly=True"

'3) check if Error then Exit Function'
If Err <> 0 Then
      Reporter.ReportEvent micFail,"Create Connection", "[Connection] Error has occurred. Error : " & Err
   Exit Function
End If

'4) Create a Recordset'
Set objAdodbRecSet = CreateObject("ADODB.Recordset")
objAdodbRecSet.CursorLocation=3                        ' set the cursor to use adUseClient – disconnected recordset

'5) Execute SQL and store results in reocrdset'
strSQLStatement= "Select * from [Sheet1]"
objAdodbRecSet.Open strSQLStatement, objAdodbCon, 1, 3

'6) Read all fields data'
While objAdodbRecSet.EOF=false

           For i=0 to objAdodbRecSet.Fields.count
       
            Msgbox objAdodbRecSet.fields(i)
       
        Next
       
objAdodbRecSet.moveNext

Wend


If Err<>0 Then
      Reporter.ReportEvent micFail,"Open Recordset", "Error has occured.Error Code : " & Err
     Exit Function
End If

'7) Close and Discard all variables '
Set objAdodbRecSet.ActiveConnection = Nothing
objAdodbCon.Close

Set objAdodbCon = Nothing

 

Note:- To work with MS Excel 2007 use following Connection string command

    Set cnDBA = CreateObject("ADODB.Connection")
    cnDBA.connectionstring = "PROVIDER=MICROSOFT.ACE.OLEDB.12.0;DATA SOURCE=" & strDBNameA & "; Extended Properties=""Excel 12.0;HDR=Yes;"";"
    cnDBA.open

System /Environment Variables

Environment Variables

There are several ways to read or write environment variables:

1. Use the WSH Shell object
2. Use WMI's Win32_Environment class
3. Read/write the variables directly from/to the registry

As directly accessing the registry is both risky and usually requires a reboot for the changes to take effect, I would not recommend using it, unless all other methods fail.

WSH Shell Object

Read Environment Variables

Reading an environment variable is simple:

Set wshShell = CreateObject( "WScript.Shell" )
WScript.Echo wshShell.ExpandEnvironmentStrings( "%PATHEXT%" )
wshShell = Nothing

The output will look like this:

.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH

The ExpandEnvironmentStrings method can expand environment variables embedded in a string too:

Set wshShell = CreateObject( "WScript.Shell" )
WScript.Echo wshShell.ExpandEnvironmentStrings( "PATH=%PATH%" )
wshShell = Nothing

The output will look like this (but probably longer):

PATH=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem

This behaviour is exactly like that of a batch file: the environment variable is replaced by its value when the string is processed.

Some environment variables are actually the result of two variables being merged. The environment variable PATH, for example, is defined in the system environment as well as in the user environment, as can be seen in this screenshot of the "System" Control Panel applet.

In this case, if we query the PATH environment variable like we did just before, the result will look like this:

PATH=C:\WINDOWS\system32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;D:\Test

As we can see, the PATH value from the user environment was appended to the value from the system environment.

Other user variables, like TEMP, overwrite their system counterpart:

Set wshShell = CreateObject( "WScript.Shell" )
WScript.Echo wshShell.ExpandEnvironmentStrings( "TEMP=%TEMP%" )
wshShell = Nothing

The output will look like this:

TEMP=C:\DOCUME~1\You\LOCALS~1\Temp

Note:

In fact, it gets even more complicated: if you look in the "System" Control Panel applet, you'll notice that the TEMP value in the user environment displays the long path name, not the short 8.3 notation.

Only the system environment values will be available to other users logging on to the same computer, the user environment values are part of the (roaming) profile and hence will be different or even absent for other users.

As you may already have guessed, this technique is not suited for setting environment variables.

To set an environment variable, we first need to find a way to specify in which environment we would like to set that variable.

That is where we use the WSH Shell's Environment method:

Set wshShell = CreateObject( "WScript.Shell" )
Set wshSystemEnv = wshShell.Environment( "SYSTEM" )
WScript.Echo "SYSTEM:  TEMP=" & wshSystemEnv( "TEMP" )
Set wshSystemEnv = Nothing
Set wshShell     = Nothing

Valid parameters for Environment are PROCESS, SYSTEM, USER and VOLATILE.

The resulting output will look like this:

SYSTEM:  TEMP=%SystemRoot%\TEMP

Had we used the PROCESS parameter, the output would have looked like this:

PROCESS:  TEMP=C:\DOCUME~1\Rob\LOCALS~1\Temp

This is the value the WSH Shell's ExpandEnvironmentStrings method would return; ExpandEnvironmentStrings can only read the process environment.

OK, time for a demonstration:

Set wshShell = CreateObject( "WScript.Shell" )
WScript.Echo Left( "Expanded" & Space( 12 ), 12 ) & wshShell.ExpandEnvironmentStrings( "TEMP=%TEMP%" )
arrEnvironments = Array( "PROCESS", "SYSTEM", "USER", "VOLATILE" )
For Each strEnv In arrEnvironments
 Set wshEnv = wshShell.Environment( strEnv )
 WScript.Echo Left( strEnv & Space( 12 ), 12 ) & "TEMP=" & wshEnv( "TEMP" )
Next
Set wshEnv   = Nothing
Set wshShell = Nothing

This is what the resulting output will look like:

Expanded    TEMP=C:\DOCUME~1\You\LOCALS~1\Temp
PROCESS     TEMP=C:\DOCUME~1\You\LOCALS~1\Temp
SYSTEM      TEMP=%SystemRoot%\TEMP
USER        TEMP=%USERPROFILE%\Local Settings\Temp
VOLATILE    TEMP=

Experiment, play with the code.
So far all we did is read environment variables, which is absolutely harmless.

Set Environment Variables

After having read the chapter on reading environment variables, setting them is only a small step.

We will use the WSH Shell's Environment method again:

Set wshShell = CreateObject( "WScript.Shell" )
Set wshSystemEnv = wshShell.Environment( "SYSTEM" )
' Display the current value
WScript.Echo "TestSystem=" & wshSystemEnv( "TestSystem" )

' Set the environment variable
wshSystemEnv( "TestSystem" ) = "Test System"

' Display the result
WScript.Echo "TestSystem=" & wshSystemEnv( "TestSystem" )
' Delete the environment variable
wshSystemEnv.Remove( "TestSystem" )
' Display the result once more
WScript.Echo "TestSystem=" & wshSystemEnv( "TestSystem" )
Set wshSystemEnv = Nothing
Set wshShell     = Nothing

The output should look like this:

TestSystem=
TestSystem=Test System
TestSystem=

List Environment Variables

To list all variables in the user environment:

Set wshShell = CreateObject( "WScript.Shell" )
Set wshUserEnv = wshShell.Environment( "USER" )
For Each strItem In wshUserEnv
 WScript.Echo strItem
Next
Set wshUserEnv = Nothing
Set wshShell   = Nothing

The result will look like this:

TEMP=%USERPROFILE%\Local Settings\Temp
TMP=%USERPROFILE%\Local Settings\Temp

If you read the previous chapters you will know how to list the variables from the other environments too.

WMI's Win32_Environment Class

Besides being able to access environment variables on remote computers, WMI's Win32_Environment class also allows us to access (read and set) environment variables for other users!

See MSDN for detailed information on this class' properties.

Read or List Environment Variables

The following code, created with the help of Scriptomatic, lists all TEMP variables on the local computer:

Set objWMIService = GetObject( "winmgmts://./root/CIMV2" )
strQuery = "SELECT * FROM Win32_Environment WHERE Name='TEMP'"
Set colItems = objWMIService.ExecQuery( strQuery, "WQL", 48 )

For Each objItem In colItems
  WScript.Echo "Caption        : " & objItem.Caption
  WScript.Echo "Description    : " & objItem.Description
  WScript.Echo "Name           : " & objItem.Name
  WScript.Echo "Status         : " & objItem.Status
  WScript.Echo "SystemVariable : " & objItem.SystemVariable
 WScript.Echo "UserName       : " & objItem.UserName
 WScript.Echo "VariableValue  : " & objItem.VariableValue
 WScript.Echo
Next

Set colItems      = Nothing
Set objWMIService = Nothing

Set Environment Variables

To set a variable, specify new values for its Name, UserName and/or VariableValue properties.

The following code, from the book Windows Server Cookbook by Robbie Allen, creates a new system environment variable called FOOBAR:

strVarName = "FOOBAR"
strVarValue = "Foobar Value"

Set objVarClass = GetObject( "winmgmts://./root/cimv2:Win32_Environment" )
Set objVar      = objVarClass.SpawnInstance_
objVar.Name          = strVarName
objVar.VariableValue = strVarValue
objVar.UserName      = "<SYSTEM>"
objVar.Put_
WScript.Echo "Created environment variable " & strVarName
Set objVar      = Nothing
Set objVarClass = Nothing

And the following code removes the environment variable again by giving it an empty value:

strVarName = "FOOBAR"

Set objVarClass = GetObject( "winmgmts://./root/cimv2:Win32_Environment" )
Set objVar      = objVarClass.SpawnInstance_
objVar.Name          = strVarName
objVar.VariableValue = ""
objVar.UserName      = "<SYSTEM>"
objVar.Put_
WScript.Echo "Removed environment variable " & strVarName
Set objVar      = Nothing
Set objVarClass = Nothing

Replace the dot in the GetObject commands by a remote computer name to manage environment variables on that remote computer.