Author Topic: SB COM - Excel  (Read 13873 times)

Support

  • Administrator
  • *****
  • Posts: 22
    • View Profile
SB COM - Excel
« on: July 24, 2014, 08:51:36 PM »
Here is an example of using the Script BASIC COM/OLE extension module with Microsoft Excel.



' Jerry's Excel Example - Script BASIC COM

IMPORT com.inc

CONST XlHAlign_xlHAlignCenter = -4108
CONST XlBorderWeight_xlMedium = -4138

' create Excel worksheet
filename = "c:\\SB22\\sbcom\\excel\\warehouse.xls"
oExcelApp = CreateObject("Excel.Application")
oWorkBook = CallByName(oExcelApp, "Workbooks", vbGet)
oExcelWorkbook = CallByName(oWorkBook, "Add")
oExcelSheet = CallByName(oExcelWorkbook, "Worksheets", vbGet, 1)

' change interior color of cells "B1:B5" rose (solid)
oRange =  CallByName(oExcelSheet, "Range", vbGet, "B1:B5")
oInterior = CallByName(oRange, "Interior", vbGet)
CallByName oInterior, "ColorIndex", vbLet, 38
CallByName oInterior, "Pattern", vbLet, "xlSolid"
ReleaseObject oRange
ReleaseObject oInterior

' put data in cell G3
oRange =  CallByName(oExcelSheet, "Range", vbGet, "G3")
CallByName oRange, "Value", vbLet, 123

' center the data in cell G3
CallByName oRange, "HorizontalAlignment", vbLet, XlHAlign_xlHAlignCenter

' Set the font attribute to BOLD in G3
oFont = CallByName(oRange, "Font", vbGet)
CallByName oFont, "Bold", vbLet, TRUE

' Change font in G3 to purple
CallByName oFont, "Color", vbLet, 0xFF00FF

' Change font in G3 to 20 pt Courier New
CallByName oFont, "Name", vbLet, "Courier New"
CallByName oFont, "Size", vbLet, 20

' Place BOLD border around cell G3
CallByName oRange, "BorderAround", vbMethod, 1, XlBorderWeight_xlMedium, 3
ReleaseObject oFont
ReleaseObject oRange

' Add long string to cell E2, short number to C1
oRange = CallByName(oExcelSheet, "Range", vbGet, "C1")
CallByName oRange, "Value", vbLet, 2
ReleaseObject oRange
oRange = CallByName(oExcelSheet, "Range", vbGet, "E2")
CallByName oRange, "Value", vbLet, "Testing long string"
ReleaseObject oRange

' Save worksheet and release Excel worksheet memory
CallByName oExcelWorkbook, "SaveAs", vbMethod, filename
CallByName oExcelWorkbook, "Close"
CallByName oExcelApp, "Quit"
ReleaseObject oExcelSheet
ReleaseObject oExcelWorkbook
ReleaseObject oWorkBook
ReleaseObject oExcelApp
« Last Edit: May 05, 2015, 08:12:01 PM by support »