Excel Automation

The Document Object Model (DOM) is an application programming interface (API). It defines the logical structure of documents and the way a document is accessed and manipulated.

Play With MS.Excel in VBScript

How to Open a new Excel Workbook?

Dim objExcel
‘Creating an Excel object
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
‘Creating an Excel Workbook
objExcel.Workbooks.Add

How To save the newly Created Excel Workbook?

Dim objExcel, objSheet
‘Creating an Excel Object
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
‘Creating an Excel Workbook
objExcel.Workbooks.Add
‘Creating and an Excelsheet Object
Set objSheet = objExcel.Sheets(“Sheet1″)
‘Saving the Excel workbook in C drive.
objSheet.SaveAS”C:\HI.XLSX”

How to Add Data to a Spreadsheet Cell?

Dim objExcel, objSheet
‘Creating an Excel Object
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
‘Creating an Excel Workbook
objExcel.Workbooks.Add
‘Creating and an Excelsheet Object
Set objSheet = objExcel.Sheets(“Sheet1″)
‘Entering Test data “Test Value” in the 1st column and 1 row
objSheet.Cells(1, 1).Value = “Test value”
‘Saving the Excel workbook in C drive.
objSheet.SaveAS”C:\HI.XLSX”

How to Create a new Excel Sheet,Add Data to a Spreadsheet Cell,  Save  and Close it?

Dim objExcel, objSheet
‘Creating an Excel Object
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
‘Creating an Excel Workbook
objExcel.Workbooks.Add
‘Creating and an Excelsheet Object
Set objSheet = objExcel.Sheets(“Sheet1″)
‘Entering Test data “Test Value” in the 1st column and 1 row
objSheet.Cells(1, 1).Value = “Test value”
‘Saving the Excel workbook in C drive.
objSheet.SaveAS”C:\HI.XLSX”
objExcel.quit

How to Open a saved Excel book?

Dim objExcel
Set objExcel = CreateObject(“Excel.Application”)
Set objWorkbook = objExcel.Workbooks.Open(“C:\hi.xlsx”)

A Function to sort a culomn in an Excel Sheet

Const xlAscending = 1
Const xlDescending = 2
Call sortExcelSheet (“C:\Documents and Settings\ADMIN\Desktop\k1.xls”,1,”A1″,2)

Function sortExcelSheet(WorkBookPath,WorkSheetNo,ColumnName,Order)
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = true

Set objWorkbook = objExcel.Workbooks.Open(WorkBookPath)
Set objWorksheet = objWorkbook.Worksheets(WorkSheetNo)

Set objRange = objWorksheet.UsedRange
Set objRange2 = objExcel.Range(ColumnName)

objRange.Sort objRange2,Order

objWorkbook.save
objWorkbook.close
objExcel.Application.Quit

Set objRange2=Nothing
Set objRange=Nothing
Set objWorksheet =Nothing
Set objWorkbook =Nothing
Set objExcel =Nothing
End Function