Author Topic: Script BASIC Excel  (Read 2803 times)

Offline John

  • Forum Support / SB Dev
  • Posts: 2752
    • ScriptBasic Open Source Project
Script BASIC Excel
« on: October 31, 2015, 12:26:23 PM »
This is an example of Script BASIC creating an Excel spreadsheet with some test data and saving the file.



Code: Script BASIC
  1. IMPORT com.inc
  2.  
  3. filename = "C:\sb22\scriptledger\test.xlsx"
  4.  
  5. IF FileExists(filename) THEN
  6.   PRINT "File already exists deleting: ", filename,"\n"
  7.   DELETE filename
  8. END IF
  9.  
  10. oExcelApp = VBNEW("Excel.Application")
  11.  
  12. IF oExcelApp = 0 THEN
  13.   PRINT "Failed to create Excel Object do you have it installed?"
  14.   GOTO Obj_Release
  15. end if
  16.  
  17. ' VBScript: Set ExcelWorkbook = ExcelApp.Workbooks.Add
  18. oWorkBook = VBCALL(oExcelApp, "Workbooks", vbGet)
  19. oExcelWorkbook = VBCALL(oWorkBook, "Add")
  20.  
  21. ' VBScript: Set ExcelSheet = ExcelWorkbook.Worksheets(1)
  22. oExcelSheet = VBCALL(oExcelWorkbook, "Worksheets", vbGet, 1)
  23.  
  24.  
  25. FOR i = 0 TO 10
  26.   FOR j = 0 TO 10
  27.     ' VBScript: ExcelSheet.Cells(i, j).Value = "test-" & i & "-" & j
  28.    oCell = VBCALL(oExcelSheet, "Cells", vbGet, i, j)
  29.     VBCALL oCell, "Value", vbLet, "test-" & i & "-" & j
  30.     VBREL oCell
  31.   NEXT
  32. NEXT
  33.  
  34. ' Save new workbook and exit
  35.  
  36. VBCALL oExcelWorkbook, "SaveAs", vbMethod, filename
  37. VBCALL oExcelWorkbook, "Close"
  38. VBCALL oExcelApp, "Quit"
  39.  
  40. Obj_Release:
  41.  
  42. VBREL oExcelSheet
  43. VBREL oExcelWorkbook
  44. VBREL oWorkBook
  45. VBREL oExcelApp
  46.