Excel Automation : Open excel file, Sort Excel file, Delete Rows, Delete Columns, Create new excel file,copy data from one file and past in other file

In below code covered Excel Automation with the following actions:-
1) Open existing excel file
2) Sort Excel file
3) Delete Rows
4) Delete Columns
5) Create new excel file
6) copy data from one excel file and past it in another excel file.


‘ Give new excel file path for creation
NewExcelFile="C:\NewExcelFile.xls"
' Give path for Data having excel file
ExistingExcelFile="C:\ExistingExcelFile.xls"
'Represents the sorting type 1 for Ascending 2 for Desc
Const xlAscending = 1
Const xlDescending=2
Const xlYes = 1
Dim objExcel, xlBook, xlSheet, objWorkbook, objWorksheet, objRange, objRange2, oSheet , objWorkbook2
Const xlExcel7 = 39

'Create the excel object, Activate worksheet and disable alerts
Set objExcel = CreateObject("Excel.Application")
Set xlBook=objExcel.workbooks.add
Set xlSheet=xlBook.activesheet
objExcel.DisplayAlerts = False

‘Save new excel file
xlBook.saveas ExistingExcelFile
objExcel.Visible = True

'Open the created excel file
Set objWorkbook = objExcel.Workbooks.Open(NewExcelFile)

'Select the Work sheet based on the index ..1,2 ,3 …
Set objWorksheet = objWorkbook.Worksheets(1)

'Which select the range of the cells has some data other than blank
Set objRange = objWorksheet.UsedRange

‘Select the column to sort
Set objRange2 = objExcel.Range("B1")

‘Sort the Excel file in ascending order
objRange.Sort objRange2, xlAscending, , , , , , xlYes

‘Sort the Excel file in descending order
'objRange.Sort objRange2, xlDescending, , , , , , xlYes
'Set Activesheet
Set oSheet = objExcel.Activesheet
objExcel.DisplayAlerts = False
'‘Delete rows or row in the excel file
oSheet.Rows("10:30").Delete
wait(1)
‘Delete Column or Columns in the excel file
oSheet.Columns("C:P").Delete
wait(1)
‘Save Workbook
objWorkbook.Save
‘
Set objWorkbook2= objExcel.Workbooks.Open(ExistingExcelFile)
‘Copy the data from used range excel file
objWorkbook.Worksheets(1).UsedRange.Copy
‘Past the Copy data
objWorkbook2.Worksheets(1).Range("A1").PasteSpecial Paste =xlValues
‘Save the files and close open files
objWorkbook.save
objWorkbook2.save
objWorkbook.close
objWorkbook2.close
set objExcel=nothing