Excel Handling Using VBScript

1.   Create Excel file Using VBScript 

Set xlobj = CreateObject("Excel.Application")   'create the excel object

xlobj.Visible = True   'make excel visible

Set wbookobj = xlobj.workbooks.add     'add a new workbook

wbookobj.saveas("D:\Test.xlsx")  'save excel file

wbookobj.close     'close workbook

xlobj.quit     'exit excel object

Set wbookobj = Nothing    'release workbook object

Set xlobj = Nothing     'release excel object

2.   Add new sheet in Excel

Set xlobj = CreateObject("Excel.Application")    'create the excel object

Set wbookobj = xlobj.workbooks.open("D:\Test.xlsx")    'open a excel file

Set wsheetobj = wbookobj.worksheets.add   ‘add new worksheet

wbookobj.save    'save excel file

wbookobj.close     'close workbook

xlobj.quit     'exit excel object

Set wsheetobj = Nothing   'release worksheet object

Set wbookobj = Nothing    'release workbook object

Set xlobj = Nothing     'release excel object

3.   Delete a sheet from Excel

Set xlobj = CreateObject("Excel.Application")    'create the excel object

Set wbookobj = xlobj.workbooks.open("D:\Test.xlsx")    'open a excel file

Set wsheetobj = wbookobj.worksheets(1)   'pass the index/name of worksheet

wsheetobj.Delete   'assign new name to sheet1

wbookobj.save    'save excel file

wbookobj.close     'close workbook

xlobj.quit     'exit excel object

Set wsheetobj = Nothing   'release worksheet object

Set wbookobj = Nothing    'release workbook object

Set xlobj = Nothing     'release excel object

4.   Rename Sheet in Excel

Set xlobj = CreateObject("Excel.Application")    'create the excel object

Set wbookobj = xlobj.workbooks.open("D:\Test.xlsx")    'open a excel file

Set wsheetobj = wbookobj.worksheets(1)   'passing the index/name of worksheet

wsheetobj.name = "techvbscript"   'assign new name to sheet1

wbookobj.save    'save excel file

wbookobj.close     'close workbook

xlobj.quit     'exit excel object

Set wsheetobj = Nothing   'release worksheet object

Set wbookobj = Nothing    'release workbook object

Set xlobj = Nothing     'release excel object

       5.   Write Data into Excel

Set xlobj = CreateObject("Excel.Application")    'create the excel object

Set wbookobj = xlobj.workbooks.open("D:\Test.xlsx")    'open a excel file

Set wsheetobj = wbookobj.worksheets(1)   'passing the index/name of worksheet

wsheetobj.cells(1,2) = "techvbscript"   'write text in row 1 and column 2

wbookobj.save    'save excel file

wbookobj.close     'close workbook

xlobj.quit     'exit excel object

Set wsheetobj = Nothing   'release worksheet object

Set wbookobj = Nothing    'release workbook object

Set xlobj = Nothing     'release excel object

        6.   Read Data from Excel

Set xlobj = CreateObject("Excel.Application")    'create the excel object

Set wbookobj = xlobj.workbooks.open("D:\Test.xlsx")    'open a excel file

Set wsheetobj = wbookobj.worksheets(1)   'passing the index/name of worksheet

wsheetobj.cells(1,2) = "techvbscript"   'read text from row 1 and column 2

wbookobj.close     'close workbook

xlobj.quit     'exit excel object

Set wsheetobj = Nothing   'release worksheet object

Set wbookobj = Nothing    'release workbook object

Set xlobj = Nothing     'release excel object

        7.   Count Total Number of sheet in Excel

Set xlobj = CreateObject("Excel.Application")    'create the excel object

Set wbookobj = xlobj.workbooks.open("D:\Test.xlsx")    'open a excel file

Set wsheetobj = wbookobj.worksheets 

Msgbox wsheetobj.count   'display total sheet count

wbookobj.close     'close workbook

xlobj.quit     'exit excel object

Set wsheetobj = Nothing   'release worksheet object

Set wbookobj = Nothing    'release workbook object

Set xlobj = Nothing     'release excel object

       8.   Display sheetname in Excel

Set xlobj = CreateObject("Excel.Application")    'create the excel object

Set wbookobj = xlobj.workbooks.open("D:\Test.xlsx")    'open a excel file

Set wsheetobj = wbookobj.worksheets(1)   'passing the index of worksheet

Msgbox wsheetobj.name  'display name of first sheet

wbookobj.save    'save excel file

wbookobj.close     'close workbook

xlobj.quit     'exit excel object

Set wsheetobj = Nothing   'release worksheet object

Set wbookobj = Nothing    'release workbook object

Set xlobj = Nothing     'release excel object