This is an example of using the ScriptBasic COM extension module to create an Excel spreadsheet workbook without human interaction. The example pops the DI (Display Interface) to show what the WorkBook object interface offers.
IMPORT COM.sbi
filename = "C:\\ScriptBASIC\\examples\\excel_test.xls"
IF FileExists(filename) THEN
PRINT "File already exists deleting: ", filename,"\n"
DELETE filename
END IF
oExcelApp = COM::CREATE(:SET, "Excel.Application")
' oExcelApp = COM::CREATE(:SET, "{00024500-0000-0000-C000-000000000046}")
IF oExcelApp = 0 THEN
PRINT "Failed to create Excel Object do you have it installed?\n"
END
END IF
' VBS: Set ExcelWorkbook = ExcelApp.Workbooks.Add
oWorkBook = COM::CBN(oExcelApp, "Workbooks", :GET)
IF oWorkBook = 0 THEN
PRINT "Failed to create Workbook\n"
END
END IF
oExcelWorkbook = COM::CBN(oWorkBook, "Add")
COM::DI(oExcelWorkBook)
' VBS: Set ExcelSheet = ExcelWorkbook.Worksheets(1)
oExcelSheet = COM::CBN(oExcelWorkbook, "Worksheets", :GET, 1)
IF oExcelSheet = 0 THEN
PRINT "Failed to get oExcelSheet\n"
END
END IF
' Adding cells...
oRange = COM::CBN(oExcelSheet, "Range", :GET, "G3")
IF oRange = 0 THEN
PRINT "Failed to get oRange\n"
END
END IF
COM::CBN(oRange, "Value", :LET, "123")
COM::RELEASE(oRange)
oRange = COM::CBN(oExcelSheet, "Range", :GET, "B1:B5")
IF oRange = 0 THEN
PRINT "Failed to get oRange\n"
END
END IF
' Place BOLD border around range
CONST XlBorderWeight_xlMedium = -4138
COM::CBN(oRange, "BorderAround", :CALL, 1, XlBorderWeight_xlMedium, 3)
oInterior = COM::CBN(oRange, "Interior", :GET)
IF oInterior = 0 THEN
PRINT "Failed to get oInterior\n"
END
END IF
COM::CBN(oInterior, "ColorIndex", :LET, "38")
COM::CBN(oInterior, "Pattern", :LET, "xlSolid")
COM::RELEASE(oRange)
COM::RELEASE(oInterior)
FOR i=1 TO 10
FOR j=1 TO 10
' VBS: ExcelSheet.Cells(i, j).Value = "test-" & i & "-" & j
oCell = COM::CBN(oExcelSheet, "Cells", :GET, i, j)
COM::CBN(oCell, "Value", :LET, "test-" & i & "-" & j)
COM::RELEASE(oCell)
NEXT
NEXT
' Saving spreadsheet
COM::CBN(oExcelWorkbook, "SaveAs", :CALL, filename)
COM::CBN(oExcelWorkbook, "Close")
COM::CBN(oExcelApp, "Quit")
' Releasing objects from memory
COM::RELEASE(oExcelSheet)
COM::RELEASE(oExcelWorkbook)
COM::RELEASE(oWorkBook)
COM::RELEASE(oExcelApp)
PRINT "Spreadsheet Created.\n"
Output:
C:\ScriptBasic\examples>sbc excel.sb
Spreadsheet Created.
C:\ScriptBasic\examples>