Creating a new Excel Sheet Via VbScript

Excel - Vbscript

To Create an Excel workbook via Vbscript, again first you need to create the COM of Excel and then provide the location where you want to save your file.

Step 1 - Create COM of Excel


Set ObjExcel = CreateObject("Excel.Application")   'Create COM of excel

Step 2 - Add a naive workbook





Set objWorkbook = ObjExcel.Workbooks.Add (1)   'Add WorkBook
objExcel.Application.DisplayAlerts = False 
objExcel.Application.Visible = True     

Step 3 - Specify the Name of WorkSheet

Set ObjWorkSheet=objWorkbook.Worksheets("Sheet1")  'By Default 1 sheet is name as Sheet1



Step 4 - Enter the value in Cell

  ObjWorkSheet.Cells(1,1).Value="My Sheet"

Step 5 - Save your workbook

 objWorkbook.SaveAs("D:\MySheet.xls")
 ObjExcel.Quit

Step 6 - Free the memory used by Excel

Set ObjExcel = Nothing
Set objWorkbook = Nothing
Set ObjWorkSheet = Nothing

You can even add the more than one sheet at Run Time and update the values up there.

Set ObjExcel = CreateObject("Excel.Application")  

Set objWorkbook = ObjExcel.Workbooks.Add (1)    '
objExcel.Application.DisplayAlerts = False  
objExcel.Application.Visible = True                                                       
Set ObjWorkSheet=objWorkbook.Sheets.add

  ObjWorkSheet.Cells(1,1).Value="My Sheet" ' Now value is entered in New sheet

 objWorkbook.SaveAs("D:\MySheet.xls")
 ObjExcel.Quit

Set ObjExcel = Nothing
Set objWorkbook = Nothing
Set ObjWorkSheet = Nothing