Update the value in Excel using VbScript

Excel - VbScript

To Modify/Update the value from Excel sheet you first need to connect to the excel worksheet and then need to specify the cell whose value you want to change.

Again, To connect to the Excel sheet you have couple of options like by creating COM of Excel, By connect to the Excel using ADODB(Activex DataObject DataBase).

Here we are using the most simple and common method COM.

Step 1 - Connect to the excel

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

Set objWorkbook = ObjExcel.Workbooks.Open("D:\Pulkit.xls")    'Provide the location of your file
objExcel.Application.DisplayAlerts = False   'to suppress any run time error
objExcel.Application.Visible = True        'To display the Excel,
Set ObjWorkSheet=objWorkbook.Worksheets("Sheet1")  'Name/Index of the worksheet which user want 'to open, You can either use ObjWorkSheet=objWorkbook.Worksheets("Sheet1")


Step 2 - Specify the cell where you want to update the value

ObjWorkSheet.Cells(1,1).value  = "My Sheet"
ObjWorkSheet.Cells(1,2).value  = "Update Excel"

Step 3 - Save your Excel file before closing the connection
Note - Excel can save the updated values automatically even without using Save command, but for safer side always, Save the excel sheet before quit the application

objWorkbook.Save
 ObjExcel.Quit

Now you are can free the memory allocated to the excel.

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