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
Go to full version