Discussion Topics > Excel Scripting

Script BASIC - Excel COM/OLE + ODBC

(1/1)

Root:
This example creates an Excel worksheet, populates it from the AR_Customer table via ODBC. The telephone column is set to  bold and color to purple to show formating.This example executes without Excel becoming visible during the process. A nice feature if running from a batch file.


--- Code: Script BASIC ---' Sage 100 Customers - ABC Demo - Excel IMPORT odbc.sbiIMPORT com.sbi dbh = odbc::RealConnect("SOTAMAS90","","") oExcelApp = com::CreateObject("Excel.Application")oWorkBook = com::CallByName(oExcelApp, "Workbooks", vbGet)oExcelWorkbook = com::CallByName(oWorkBook, "Add")oExcelSheet = com::CallByName(oExcelWorkbook, "Worksheets", vbGet, 1) odbc::query(dbh,"SELECT * FROM AR_Customer") row = 1 WHILE odbc::FetchHash(dbh, column)  oCell = com::CallByName(oExcelSheet, "Cells", vbGet, row, 1)  com::CallByName(oCell, "Value", vbLet, column{"CustomerNo"})  com::ReleaseObject(oCell)  oCell = com::CallByName(oExcelSheet, "Cells", vbGet, row, 2)  com::CallByName(oCell, "Value", vbLet, column{"CustomerName"})  com::ReleaseObject(oCell)  oCell = com::CallByName(oExcelSheet, "Cells", vbGet, row, 3)  com::CallByName(oCell, "Value", vbLet, column{"TelephoneNo"})  oFont = com::CallByName(oCell, "Font", vbGet)  com::CallByName(oFont, "Bold", vbLet, TRUE)  com::CallByName(oFont, "Color", vbLet, 0xFF00FF)  com::ReleaseObject(oFont)    com::ReleaseObject(oCell)  row += 1WEND com::CallByName(oExcelWorkbook, "SaveAs", vbMethod, "C:\\ScriptBASIC\\examples\\test\\AR_Customer.xlsx")com::CallByName(oExcelWorkbook, "Close")com::CallByName(oExcelApp, "Quit")com::ReleaseObject(oExcelSheet)com::ReleaseObject(oExcelWorkbook)com::ReleaseObject(oWorkBook)com::ReleaseObject(oExcelApp) odbc::Close(dbh) 

Navigation

[0] Message Index