''Input Information - Workbook file Name with path ''WorkSheet Name '' Provide the sort criteria based on which data needs to be filtered seperated by | WorkBookFile = "C:\\testing.xls" SheetName = "testing" MultipleSortCriteria = "TestName|TestClass|TestPath" '' Define the constants to be used in the script Const xlYes = 1 Const xlAscending = 1 Const xlDescending = 2 Set oXL = CreateObject("Excel.Application") Set oWB = oXL.WorkBooks.Open(WorkBookFile) Set oWS = oWB.Worksheets(SheetName) Set oRnge = oWS.UsedRange ColCnt = oRnge.columns.count '' Create an array based on the sort criteria provided aSrtCriteria = split(MultipleSortCriteria,"|") For i = ubound(aSrtCriteria) to 0 step -1 ''Loop the times data needs to be sorted For j = 1 to ColCount step 1 If (oWS.cells(1,j).value =aSrtCriteria(i)) Then '' Sort the data based on the column name Set oRngeSrt = oXL.Range(Chr(asc("A")- 1+j) & "1") oRnge.Sort oRngeSrt, xlDescending, , , , , , xlYes oWB.save Exit For End If Next Next oWB.Close oXL.Quit Set oWB = Nothing Set oXL = Nothing
Set WShell = CreateObject("WScript.Shell") ''This will close all the instances of task iexplore.exe. ''Parameter /f kills the process forcefully WShell.Exec("taskkill /fi ""imagename eq iexplore.exe"" /f") WScript.Sleep 100
''At time Internet explorer is not responding error is displayed ''The task has window title as internet explorer not responding. ''We can close the task based on window title with regular expression as Internet * WShell.Exec("taskkill /fi ""WINDOWTITLE eq Internet*"" /f") WScript.Sleep 100
''Once the window is closed, another window appear with similar title
'' which needs to be closed WShell.Exec("taskkill /fi ""WINDOWTITLE eq Internet*"" /f")
''Below command will return the service for the process and save it in file abc.txt
Set WShell = CreateObject("WScript.Shell")
''This will close all the instances of task iexplore.exe. ''Parameter /f kills the process forcefully
WShell.Exec("tasklist /fi /svc>test.txt")
Function CloseAllOpenbrowsersExcept(browsertitle) Set oBrowser = Description.Create oBrowser("micclass").Value = "Browser" ''Get all browsers open Set oBrowserLst= Desktop.ChildObjects(oBrowser) For i=0 to oBrowserLst.count-1 'Verify the title of the browser and close the browser if it does not the title passed 'in the function If InStr(oBrowserLst(i).GetROProperty("title"), browsertitle) = 0 Then oBrowserLst(i).close End If Next Set oBrowser = nothing Set oBrowser = nothing End Function
Function CloseOpenbrowserWithTitle(browsertitle) Set oBrowser = Description.Create oBrowser("micclass").Value = "Browser" ''Get all browsers open Set oBrowserLst= Desktop.ChildObjects(oBrowser) For i=0 to oBrowserLst.count-1 'Verify the title of the browser and close the browser if it does not the title passed 'in the function If InStr(oBrowserLst(i).GetROProperty("title"), browsertitle) <> 0 Then oBrowserLst(i).close End If Next Set oBrowser = nothing Set oBrowser = nothing End Function
Function CloseAllOpenbrowsers() Set oBrowser = Description.Create oBrowser("micclass").Value = "Browser" ''Get all browsers open Set oBrowserLst= Desktop.ChildObjects(oBrowser) For i=0 to oBrowserLst.count-1 'Verify the title of the browser and close the browser if it does not the title passed 'in the function oBrowserLst(i).close Next Set oBrowser = nothing Set oBrowser = nothing End Function
Function CreateALMConnection(uRLALM,strUserName,strPassword,strDomain, strProject) Set objALMConnection = CreateObject("TDApiOle80.TDConnection") objQCConnection.InitConnectionEx uRLALM objQCConnection.Login strUserName, strPassword objQCConnection.Connect strDomain, strProject ''/* Write the required code for transaction with
'' ALM once the connection is created objQCConnection.Disconnect objQCConnection.Logout objQCConnection.ReleaseConnection Set objQCConnection = Nothing End Function
'''' Naming the sub as auto_open will trigger the sub automatically on opening the file.
'''' We can create a sub with different name and assign macro to a control in the excel
''''file
Sub auto_open()
'''' define the connection string to connect to the database, it can be a dsn created,
'''' or connection string to create to the database. Please refer connectionstrings.com
'''' to connect to different database using connection string.
connStr = "dsn=testthedb''
'''' Query to fetch data from the database
strGetEmployeeDetails = "select * from employee order by UserName asc;"
''''Provide reference of sheet to add data to in the current workbook. We can add a
''''sheet in excel, or refer to an external excel file/Sheet using excel.application.
''''In this example, Sheet Employee_Details exist in the current excel file Set worksht = ThisWorkbook.Sheets("Employee_Details") ''''-------------------------- '''' Create database connection Set Conn = CreateObject("ADODB.Connection") Set rset = CreateObject("ADODB.Recordset") Conn.Open connStr rset.Open strGetEmployeeDetails, Conn, adOpenStatic
''''The recordset is stored in rset.
''''The first row of data is the column details of the query results
''''and is added in the results header
For i = 0 To rset.Fields.Count - 1
worksht.Cells(2, i + 2) = rset.Fields(i).Name
Next i
''''Copy the recorset data into excel file starting at row 3 and column 2 worksht.Cells(3, 2).CopyFromRecordset rset rset.Close Set rset = Nothing ThisWorkbook.Save Conn.Close Set Conn = Nothing End Sub
Sub auto_open() MsgBox "Show message while opening the excel file" End Sub
Sub auto_close()
MsgBox "Show message while closing the excel file"
End Sub 
