Subject Re: Exportdata sinks
From Bamidele Onwu <bamionwu@yahoo.com>
Date Sun, 02 Aug 2020 12:35:41 -0400
Newsgroups dbase.getting-started

Thank you Mervyn. The program was a success
Am out of the woods. Thank you.

Bami




Mervyn Bick Wrote:

> On 2020-08-02 14:19, Bamidele Onwu wrote:
 > > Wow, I didn't know about this. Thanks to you Mervyn.
 > >
> > I seriously need help but I use mysql as backend. I will be very grateful if I can get sample program of how to export from sql to excel.
 > >
>
> There is a comprehensive article on moving data from dBASE to Excel in
> the Advanced Topics of the knowledgebase
 >
> http://www.dbase.com/Knowledgebase/adv/activex/ExcelOle/excel.htm
 >
> Attached is a rudimentary (but working) program to extract 3 fields from
> a MySQL table and place them in an Excel spreadsheet.  You will, of
> course, need to change the connection string as well as the table and
> field names.
 >
> Mervyn.
 >
>
> d = new adodatabase()
 > d.connectionString = "Provider=MSDASQL.1;Persist Security Info=True;Data Source=energydata"
 > d.active = true
 > q = new adoquery()
 > q.sql = "select siteId,eTimestamp,econs from energydata where siteId = '227105' "
 > q.sql += "and eTimestamp between '2020-06-05 010:00:00' and '2020-06-05 12:55:00' "
 > q.active = true
 > oExcel = new oleAutoclient("excel.application")
 > // oExcel.visible = true
 > oExcel.workbooks.add()
 > nRow = 1
 > //Add headings
 > for i = 1 to q.rowset.fields.size
 >   oExcel.ActiveSheet.cells( nRow, i ).select()
 >   with ( oExcel.ActiveCell )
 >     if i = 1
 >       ColumnWidth := 8
 >       formula := 'siteID'
 >     elseif i = 2
 >       ColumnWidth := 20
 >       formula := 'Timestamp'
 >     elseif i = 3
 >       ColumnWidth := 10
 >       formula := 'Econs'
 >     endif
 >   endwith  
 > next
 > nRow++
 > do while not q.rowset.endofset
 >    for i = 1 to q.rowset.fields.size
 >       oExcel.ActiveSheet.cells( nRow, i ).select()
 >       if i = 1
 >           oExcel.Activecell.formula = "'"+q.rowset.fields[i].value
 >       elseif i = 2
 >          oExcel.Activecell.formula = "'"+dttoc(q.rowset.fields[i].value)
 >       elseif i = 3
 >          oExcel.Activecell.formula = q.rowset.fields[i].value
 >       endif
 >    next    
 >    nRow++  
>    q.rowset.next()
 > enddo
 >
> q.active= false
 > d.active = false
 > oExcel.visible = true