RPA - Excel Manipulations - Deleting Rows - Except Header to last Used Row.

RPA - Excel Manipulations -  Deleting Rows  - Except Header to last Used Row.

'Declare Excel Application Object
Set oExcel = createobject ("Excel.Application")

'Make it Visible False .. So that excel won't appear in screen 
oExcel.visible=False

'Assign Display Alerts False.. So that excel won't give any alert while running
oExcel.DisplayAlerts=False 

'Create Excel Work book object
Set oWorkBook-=oExcel.Workbooks.open ("C:\Siva\Template.xlsx") ' Open a Template Excel from C:\Siva Folder

'Create a Excel Sheet object to use the Sheet1 
Set oSheet=oWorkBook.Worksheets ("Sheet1")

'Active the Sheet using the Sheet Object
oSheet.Activate

'Initialize the LastRow variable value to 1   

LastRow=1
' To get the Last Row Value
LastRow=oSheet.Cells.SpecialCells (11).Row

' Assign the Delete Range from A2 to last Row No of A Column
DeleteRange="A2:A" & LastRow

MsgBox " Last Row : " & LastRow & " Delete Range : " & DeleteRange

' i.e., Used Row Numbers are greater than 1 then Delete the Rang of Rows used
If LastRow > 1 Then
  oSheet . Range (DeleteRange) . EntireRow. Delete
Else
    'Msgbox "Only Header Available"
End IF
'To Verify all rows are cleared using UsedRange again
UsedRange=oSheet . UsedRange . Address (xlR1C1)
UsedRow=oSheet.UsedRange.Rows.Count
MagBox "Used Range: " & UsedRange & "used Row:" & UsedRow

'Now the Save, Close the workbook  and Cleaup the all the objects used
oWorkBook. Save
oExcel.DisplayAlerts=True
oWorkBook.Close
oExcel.Quit
Set oSheet=Nothing
Set oWorkBook=Nothing
Set oExcel =Nothing

RPA - Killing All Running Excel Applications Using VB Scripts

RPA- How to Kill the All Running Excel.Exe From the Process List

   - While developing RPA Bots we may need to kill the all running Excel.exe. But most of the RPA Tools not having such functionality to access the list of process.

- This can be achieved using VB Script. The below VB Script subroutine allows you to do this.

On Error Resume Next
TerminateProcess ' Call the TerminateProcess Sub

Sub TerminateProcess
        Dim Process
         For Each Process In GetObject ("winmgmts:").ExecQuery("Select Name from Win32_Process Where       Name='EXCEL.EXE'")
         Process.Terminate
         Next
End Sub

Steps:

    1. First we need to add error handling step. i.e. On Error Resume Next. It will help you to exit the method in as quiet way without giving any error.

    2.  Sub and End Sub  --  Declaring the new Subroutine

  

    3. Loop through the Each Process of Windows Managements and only select Excel Process and assign into Process variable.

    4.  Now terminate the excel process using Process.Terminate

    5. Continue the loop for all the process.