Author Topic: Script BASIC - Excel COM/OLE + ODBC  (Read 679 times)

0 Members and 1 Guest are viewing this topic.

Root

  • Administrator
  • *****
  • Posts: 8
Script BASIC - Excel COM/OLE + ODBC
« on: May 03, 2017, 02:14:47 PM »
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
  1. ' Sage 100 Customers - ABC Demo - Excel
  2.  
  3. IMPORT odbc.sbi
  4. IMPORT com.sbi
  5.  
  6. dbh = odbc::RealConnect("SOTAMAS90","","")
  7.  
  8. oExcelApp = com::CreateObject("Excel.Application")
  9. oWorkBook = com::CallByName(oExcelApp, "Workbooks", vbGet)
  10. oExcelWorkbook = com::CallByName(oWorkBook, "Add")
  11. oExcelSheet = com::CallByName(oExcelWorkbook, "Worksheets", vbGet, 1)
  12.  
  13. odbc::query(dbh,"SELECT * FROM AR_Customer")
  14.  
  15. row = 1
  16.  
  17. WHILE odbc::FetchHash(dbh, column)
  18.   oCell = com::CallByName(oExcelSheet, "Cells", vbGet, row, 1)
  19.   com::CallByName(oCell, "Value", vbLet, column{"CustomerNo"})
  20.   com::ReleaseObject(oCell)
  21.   oCell = com::CallByName(oExcelSheet, "Cells", vbGet, row, 2)
  22.   com::CallByName(oCell, "Value", vbLet, column{"CustomerName"})
  23.   com::ReleaseObject(oCell)
  24.   oCell = com::CallByName(oExcelSheet, "Cells", vbGet, row, 3)
  25.   com::CallByName(oCell, "Value", vbLet, column{"TelephoneNo"})
  26.   oFont = com::CallByName(oCell, "Font", vbGet)
  27.   com::CallByName(oFont, "Bold", vbLet, TRUE)
  28.   com::CallByName(oFont, "Color", vbLet, 0xFF00FF)
  29.   com::ReleaseObject(oFont)  
  30.   com::ReleaseObject(oCell)
  31.   row += 1
  32. WEND
  33.  
  34. com::CallByName(oExcelWorkbook, "SaveAs", vbMethod, "C:\\ScriptBASIC\\examples\\test\\AR_Customer.xlsx")
  35. com::CallByName(oExcelWorkbook, "Close")
  36. com::CallByName(oExcelApp, "Quit")
  37. com::ReleaseObject(oExcelSheet)
  38. com::ReleaseObject(oExcelWorkbook)
  39. com::ReleaseObject(oWorkBook)
  40. com::ReleaseObject(oExcelApp)
  41.  
  42. odbc::Close(dbh)
  43.  
« Last Edit: November 22, 2017, 12:53:34 PM by admin »