Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.name = "Subrat"
objSheet.Cells(1,1) = "Column A"
objExcel.ActiveWorkbook.SaveAs "E:\TestXL.xls"
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
2) Overwrite an Existing Excel File by suppressing the alerts :
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.name = "Subrat"
objSheet.Cells(1,1) = "Column B"
objExcel.Visible=True
objExcel.DisplayAlerts=False
objExcel.ActiveWorkbook.SaveAs "E:\TestXL.xls"
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
3) Open an Existing Excel File and write values to it in the following format :
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible=True
objExcel.DisplayAlerts=False
Set objWorkBook=objExcel.Workbooks.open( "E:\TestXL.xls")
Set objSheet= objWorkBook.Worksheets(1)
objSheet.Cells(1,1).value="ColumnA"
objSheet.Cells(1,2).value="ColumnB"
objSheet.Cells(1,3).value="ColumnC"
For i= 0 to 2
For j= 0 to 2
objSheet.Cells(i+2,j+1).value=j+1
Next
Next
objWorkBook.SaveAs "E:\TestXL.xls"
objWorkBook.Close
objExcel.Quit
btw, jst for information (u are a genious and might be already knowing this, but agar yeh code kissi ne pick kar liya to uske kaam aayega... :D
ReplyDeleteWhenever you do a SET operation on an object like,
Set objExcel = xyz
At the end of the code, when you have closed the object, or the object is not required, always do:
Set objExcel = Nothing (this destroys the object from memory).
Yes Anubhab, you are correct in this... but internally VBScript mechanism kills the object at the end of every function call or after the Test ends, even if you do not explicitly set it to 'Nothing' and frees up the memory and this is exactly how the space occupied by the other variables in the function are freed up.
ReplyDelete