dBASE Windows API - OLE - dBase to Excel

Author

OLE - dBase to Excel

Dan Anderson

2006-02-15, 7:24 am

db+ It has been quite some time since I have done this so I need a
refresher. I have an Excel spreadsheet that will be the OLE client and
needs to have cells populated with data from a dBase table (or tables). Can
someone please direct me to an explanation of the code needed to make this
work?

--
Dan Anderson
UBI Processing Dept.
andersond@ubinc.com
800-444-4824 ext 101

Rick Gearardo

2006-02-15, 9:24 am

Look for Dan White's excel.how. It has a very good explanation.

Basically it goes like this

proc populate(r)
local x, a, o
x = new oleAutoclient("excel.application")
x.sheetsInNewWorkBook := 1
x.workbooks.add()
x.visible := true
a = x.activeSheet

o = a.cells(1,1)
o.formula := r.fields["myFields1"].value

o = a.cells(1,2)
o.formula := r.fields["myField2"].value

etc.

Rick

> db+ It has been quite some time since I have done this so I need a
> refresher. I have an Excel spreadsheet that will be the OLE client and
> needs to have cells populated with data from a dBase table (or tables).
> Can someone please direct me to an explanation of the code needed to make
> this work?

Rick Gearardo

2006-02-15, 9:24 am

Sorry, I mean Gary White I believe

"Rick Gearardo" < dbase@NO_SPAMsecurit
ycorp.com> wrote in message
news:9H$YAYjMGHA.2292@news-server...
> Look for Dan White's excel.how. It has a very good explanation.
>
> Basically it goes like this
>
> proc populate(r)
> local x, a, o
> x = new oleAutoclient("excel.application")
> x.sheetsInNewWorkBook := 1
> x.workbooks.add()
> x.visible := true
> a = x.activeSheet
>
> o = a.cells(1,1)
> o.formula := r.fields["myFields1"].value
>
> o = a.cells(1,2)
> o.formula := r.fields["myField2"].value
>
> etc.
>
> Rick
>
>
>
>

Eric Logan

2006-02-15, 11:24 am

> "Dan Anderson" wrote

> db+ It has been quite some time since I have done this so I need a
> refresher. I have an Excel spreadsheet that will be the OLE client and
> needs to have cells populated with data from a dBase table (or tables).
Can
> someone please direct me to an explanation of the code needed to make this
> work?
> Dan Anderson
> UBI Processing Dept.

Dan;
Check out my example below, which may be deficient in one way or another,
but works for me, and has a few nice features. i.e view XLS or prog bar,
choose input and output directories and file names.
Eric Logan

******** Begin ********
* DBF2XLS_ole.prg *
* use OleAutomation to move data from DBF
* to XLS. Important when using version 7
* DBFs, which cannot be opened directly in Excel 97 or 2000.
* Some code from Gary White examples.

set talk off
Try

local i, j, mrows

// Create instances of our variables so we can use
// the assignment (:=) operator from here on out.

store 0 to oExcel, i, j, mrows

mans = msgbox('This program will use Ole Automation to copy '+chr(13) ;
+ 'the selected dBASE table to an Excel file.'+chr(13) +chr(13)+chr(13) ;
+ 'Continue?','Copy to Excel', 4)
If mans <> 6
cancel
endif

mview = msgbox('Do you want to view the Excel file as data are
inserted?'+chr(13) ;
+'(This may make the data transfer take longer.)','View Process?',4)

mcent = set('century')
set century on

if type("UpdateWindow") # "FP"
extern cLogical UpdateWindow( cHandle ) user32
endif

// begin in current directory, get name of DBF

mdir = set('directory')
mskel = '"'+mdir+'\*.dbf"'
clear
mcmnd = "mflnm = getfile("+mskel+" ,'Database file to copy and paste.')"
&mcmnd.

if upper(substr(mflnm,r
at('.',mflnm),4)) <> '.DBF'
msgbox('Not a DBF file','Canceling Program') // if not dbf, cancel
return
endif

if len(mflnm) = 0
return // if nothing chosen, end prog.
endif

q = new query()
q.sql = [select * from "]+mflnm+["] //opens chosen dbf
r = q.rowset
q.active = true
r.first()

mrows = q.rowset.count()
r.first()

// OK: now we start Excel
oExcel := new oleAutoclient("excel.application")

// create a new workbook
oExcel.workbooks.add()
if mview = 6
_app.framewin.windowstate = 1 // minimize dBase
oExcel.Visible = True // show Excel
else
h = new harvprog() // or show Progress
h.open()
UpdateWindow(_app.framewin.hwnd)
endif

for j = 1 to r.fields.size // first copy column names
oExcel.ActiveSheet.cells( 1,j ).select()
oExcel.ActiveCell.value = r.fields[j].fieldname
next

// treat binary and date fields differently from numeric and character

mincr = round(mrows/20,0) // one-twentieth of total rows
z = -2
for i = 1 to mrows // then copy data
for j = 1 to r.fields.size
oExcel.ActiveSheet.cells( i+1,j ).select()
if upper(r.fields[j]. type)$('MEMOBINARYOL
E')
oExcel.ActiveCell.value = 'Binary'
elseif upper(r.fields[j].type) = 'DATE'
oExcel.ActiveCell.value = dtoc(r.fields[j].value)
else
oExcel.ActiveCell.value = iif(not
isbl(r.fields[j].value),r.fields[j].value,null)
endif
endfor
if type('H') = 'O' and mod(i,mincr) = 0 // if prog bar and 1/20th of
total rows
z+=5 // increment by 2 pct
h.rectangle1.width = h.rectangle1.width + 2.5 //box length + 5/2
h.text2.text = Str(z,3,0)+' %' // text shows 5% increase
UpdateWindow(_app.framewin.hwnd)
endif
r.next() // next row
endfor

oExcel.ActiveSheet.cells( 1,1 ).select()

UpdateWindow(_app.framewin.hwnd)
if type('H') = 'O'
h.close()
h = null
UpdateWindow(_app.framewin.hwnd)
endif
q.active = false
oExcel.visible = false
_app.framewin.windowstate = 2
UpdateWindow(_app.framewin.hwnd)

msgbox('Data are in default Excel File (BOOK1.XLS) and directory.' +chr(13)
;
+'You may now save the file to a specific directory and filename.')

mfl = left(mflnm,rat('.',mflnm)-1)
set safety off
mf = putf('Select file name and path for XLS exported table.',mfl,'*.xls')
set safety on
oExcel.ActiveWorkbook.saveas(mf)

oExcel.ActiveWorkbook.close()
oExcel.quit()
oExcel = null
UpdateWindow(_app.framewin.hwnd)

Catch (exception e)
?msgbox(e. message+chr(13)+'Pro
gram halted', 'Error')
if type('oExcel.ActiveWorkbook') = 'O'
oExcel.ActiveWorkbook.close()
endif
if type('oExcel') = 'O'
oExcel.quit()
oExcel = null
endif
if type('H') = 'O'
h.close()
h = null
endif
cancel
Endtry

class harvprog of FORM
with (this)
height = 3.8636
left = 18.5
top = 2.4111
width = 55.7143
text = "DBF2XLS_OLE.PRG Copying data to new Excel file BOOK1.XLS"
topMost = true
mdi = false
sizeable = false
escExit = false
maximize = false
minimize = false
endwith

this.RECTANGLE1 = new RECTANGLE(this)
with (this.RECTANGLE1)
left = 3.2857
top = 1.1818
width = 0.335
height = 1.0586
text = ""
colorNormal = "0x40/0x40"
oldStyle = true
endwith

this.TEXT2 = new TEXT(this)
with (this.TEXT2)
height = 0.7656
left = 23.4286
top = 2.8182
width = 6.5
text = "0 %"
endwith

endclass
********* End **********

Dan Anderson

2006-02-16, 9:23 am

The last time I worked with ole and excel I only used a few commands. One
that I have not seen any of you use is how to activate various existing
sheets in an excel file. That is, I have one excel file with several
worksheets. I need to be able to select one of those worksheets and
populate a specific field with data, then go to a different worksheet and
populate a cell I designate. What commands do I need to just do that?

--
Dan Anderson
UBI Processing Dept.
andersond@ubinc.com
800-444-4824 ext 101
"Eric Logan" <jelogan@pcweb.net> wrote in message
news:v0qC1$kMGHA.2320@news-server...
> Can
>
> Dan;
> Check out my example below, which may be deficient in one way or another,
> but works for me, and has a few nice features. i.e view XLS or prog bar,
> choose input and output directories and file names.
> Eric Logan
>
> ******** Begin ********
> * DBF2XLS_ole.prg *
> * use OleAutomation to move data from DBF
> * to XLS. Important when using version 7
> * DBFs, which cannot be opened directly in Excel 97 or 2000.
> * Some code from Gary White examples.
>
> set talk off
> Try
>
> local i, j, mrows
>
> // Create instances of our variables so we can use
> // the assignment (:=) operator from here on out.
>
> store 0 to oExcel, i, j, mrows
>
> mans = msgbox('This program will use Ole Automation to copy '+chr(13) ;
> + 'the selected dBASE table to an Excel file.'+chr(13) +chr(13)+chr(13)
> ;
> + 'Continue?','Copy to Excel', 4)
> If mans <> 6
> cancel
> endif
>
> mview = msgbox('Do you want to view the Excel file as data are
> inserted?'+chr(13) ;
> +'(This may make the data transfer take longer.)','View Process?',4)
>
> mcent = set('century')
> set century on
>
> if type("UpdateWindow") # "FP"
> extern cLogical UpdateWindow( cHandle ) user32
> endif
>
> // begin in current directory, get name of DBF
>
> mdir = set('directory')
> mskel = '"'+mdir+'\*.dbf"'
> clear
> mcmnd = "mflnm = getfile("+mskel+" ,'Database file to copy and paste.')"
> &mcmnd.
>
> if upper(substr(mflnm,r
at('.',mflnm),4)) <> '.DBF'
> msgbox('Not a DBF file','Canceling Program') // if not dbf, cancel
> return
> endif
>
> if len(mflnm) = 0
> return // if nothing chosen, end prog.
> endif
>
> q = new query()
> q.sql = [select * from "]+mflnm+["] //opens chosen dbf
> r = q.rowset
> q.active = true
> r.first()
>
> mrows = q.rowset.count()
> r.first()
>
> // OK: now we start Excel
> oExcel := new oleAutoclient("excel.application")
>
> // create a new workbook
> oExcel.workbooks.add()
> if mview = 6
> _app.framewin.windowstate = 1 // minimize dBase
> oExcel.Visible = True // show Excel
> else
> h = new harvprog() // or show Progress
> h.open()
> UpdateWindow(_app.framewin.hwnd)
> endif
>
> for j = 1 to r.fields.size // first copy column names
> oExcel.ActiveSheet.cells( 1,j ).select()
> oExcel.ActiveCell.value = r.fields[j].fieldname
> next
>
> // treat binary and date fields differently from numeric and character
>
> mincr = round(mrows/20,0) // one-twentieth of total rows
> z = -2
> for i = 1 to mrows // then copy data
> for j = 1 to r.fields.size
> oExcel.ActiveSheet.cells( i+1,j ).select()
> if upper(r.fields[j]. type)$('MEMOBINARYOL
E')
> oExcel.ActiveCell.value = 'Binary'
> elseif upper(r.fields[j].type) = 'DATE'
> oExcel.ActiveCell.value = dtoc(r.fields[j].value)
> else
> oExcel.ActiveCell.value = iif(not
> isbl(r.fields[j].value),r.fields[j].value,null)
> endif
> endfor
> if type('H') = 'O' and mod(i,mincr) = 0 // if prog bar and 1/20th of
> total rows
> z+=5 // increment by 2 pct
> h.rectangle1.width = h.rectangle1.width + 2.5 //box length + 5/2
> h.text2.text = Str(z,3,0)+' %' // text shows 5% increase
> UpdateWindow(_app.framewin.hwnd)
> endif
> r.next() // next row
> endfor
>
> oExcel.ActiveSheet.cells( 1,1 ).select()
>
> UpdateWindow(_app.framewin.hwnd)
> if type('H') = 'O'
> h.close()
> h = null
> UpdateWindow(_app.framewin.hwnd)
> endif
> q.active = false
> oExcel.visible = false
> _app.framewin.windowstate = 2
> UpdateWindow(_app.framewin.hwnd)
>
> msgbox('Data are in default Excel File (BOOK1.XLS) and directory.'
> +chr(13)
> ;
> +'You may now save the file to a specific directory and filename.')
>
> mfl = left(mflnm,rat('.',mflnm)-1)
> set safety off
> mf = putf('Select file name and path for XLS exported table.',mfl,'*.xls')
> set safety on
> oExcel.ActiveWorkbook.saveas(mf)
>
> oExcel.ActiveWorkbook.close()
> oExcel.quit()
> oExcel = null
> UpdateWindow(_app.framewin.hwnd)
>
> Catch (exception e)
> ?msgbox(e. message+chr(13)+'Pro
gram halted', 'Error')
> if type('oExcel.ActiveWorkbook') = 'O'
> oExcel.ActiveWorkbook.close()
> endif
> if type('oExcel') = 'O'
> oExcel.quit()
> oExcel = null
> endif
> if type('H') = 'O'
> h.close()
> h = null
> endif
> cancel
> Endtry
>
> class harvprog of FORM
> with (this)
> height = 3.8636
> left = 18.5
> top = 2.4111
> width = 55.7143
> text = "DBF2XLS_OLE.PRG Copying data to new Excel file BOOK1.XLS"
> topMost = true
> mdi = false
> sizeable = false
> escExit = false
> maximize = false
> minimize = false
> endwith
>
>
> this.RECTANGLE1 = new RECTANGLE(this)
> with (this.RECTANGLE1)
> left = 3.2857
> top = 1.1818
> width = 0.335
> height = 1.0586
> text = ""
> colorNormal = "0x40/0x40"
> oldStyle = true
> endwith
>
> this.TEXT2 = new TEXT(this)
> with (this.TEXT2)
> height = 0.7656
> left = 23.4286
> top = 2.8182
> width = 6.5
> text = "0 %"
> endwith
>
> endclass
> ********* End **********
>
>
>

Rick Gearardo

2006-02-16, 9:23 am

x = new oleAutoclient("excel.application")
x.workbooks.open("c:\myFolder\myFile.xls")
x.sheets("sheet4").select

x.range("F5").select
x.activeCell.formulaR1C1 = "11111"

If you open Excel and record a macro while doing what you need to do Excel
will give you the VBA commands which, most times, are easy to convert to dBl

Rick

"Dan Anderson" <andersond@ubinc.com> wrote in message
news:Cl$oBSwMGHA.2320@news-server...
> The last time I worked with ole and excel I only used a few commands. One
> that I have not seen any of you use is how to activate various existing
> sheets in an excel file. That is, I have one excel file with several
> worksheets. I need to be able to select one of those worksheets and
> populate a specific field with data, then go to a different worksheet and
> populate a cell I designate. What commands do I need to just do that?
>
>
>
> --
> Dan Anderson
> UBI Processing Dept.
> andersond@ubinc.com
> 800-444-4824 ext 101
> "Eric Logan" <jelogan@pcweb.net> wrote in message
> news:v0qC1$kMGHA.2320@news-server...
>
>

Sponsored Links