Create and update a excel File at given path

Share this post
FaceBook  Twitter     

To Create a Excel File at given path, use following code:
1) Create a Excel Object

2) Add a workbook

3) write data in sheet

4) Save File to a location

5) Close workbook and Excel


'Create a Xls File at given path
Function Create_Update_Xls(File_Location)
'1) Create a Excel Object'
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = True
  objExcel.DisplayAlerts = False

'2) Add a workbook '  
  Set wb = objExcel.Workbooks.Add()

'3) write data in sheet
  wb.Sheets("Sheet1").Cells(1,1).Value = "Test Data1"
  wb.Sheets("Sheet1").Cells(2,2).Value = "Test Data2"
  wb.Sheets("Sheet1").Cells(3,3).Value = "Test Data3"
  wb.Sheets("Sheet1").Cells(4,4).Value = "Test Data4"
  wb.Sheets("Sheet1").Cells(5,5).Value = "Test Data5"
  wb.Sheets("Sheet1").Cells(6,6).Value = "Test Data5"
  wb.Sheets("Sheet1").Cells(7,7).Value = "Test Data5"
  wb.Sheets("Sheet1").Cells(8,8).Value = "Test Data5"
'4) Save File to a location'  
  wb.SaveAs File_Location
'5) Close workbook and Excel
End function