Script BASIC Excel

[John]

This is an example of Script BASIC creating an Excel spreadsheet with some test data and saving the file.

Code: ScriptBasic

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.