in any open excel file:
-how to assign the last row#/cell# to a variable
-how to get the last not-empty row#. (like eof())

-how to assign the value/content of any cell to a variable
(is it: thetax:=osheet:cells(xx,yy):value)

thank you

On Friday, March 4, 2022 at 4:12:02 PM UTC+5:30, timepro timesheet wrote:
> in any open excel file:
> -how to assign the last row#/cell# to a variable
> -how to get the last not-empty row#. (like eof())
>
> -how to assign the value/content of any cell to a variable
> (is it: thetax:=osheet:cells(xx,yy):value)
>
> thank you

in the cell's format, even if 'thetax:=osheet:cells(xx,yy):value' is Text
type(thetax) shows as N

On Friday, March 4, 2022 at 5:38:47 PM UTC+5:30, timepro timesheet wrote:
> On Friday, March 4, 2022 at 4:12:02 PM UTC+5:30, timepro timesheet wrote:
> > in any open excel file:
> > -how to assign the last row#/cell# to a variable
> > -how to get the last not-empty row#. (like eof())
> >
> > -how to assign the value/content of any cell to a variable
> > (is it: thetax:=osheet:cells(xx,yy):value)
> >
> > thank you
> in the cell's format, even if 'thetax:=osheet:cells(xx,yy):value' is Text
> type(thetax) shows as N

save the value/content of a cell(Text cell) to a dbf field
*
oexcel=createobject('excel.application')
cdest=diskname()+':\tax\subfold\filename.xlsx'
oexcel:workbooks:open(cdest)
osheet=oexcel:activesheet
oexcel:visible=.t.
for xxx=the-first-row-that-is-not-blank to the-last-row-of-the-open-excel-file
xcelinv:=osheet:cells(xxx,6):value && CRASHES HERE
if type('xcelinv')='N';xcelinv=str(xcelinv);end && have to as even if cell(xxx,6) is Text
sele dbffile
seek upper(left(xcelinv,10))
if found();repl taxinvoice with xcelinv;next
next

*error log
|Error excel.application:ACTIVESHEET/1 Unknown error: CELLS Arguments: ( [ 1] = |
|Type: N Val: 38 [ 2] = Type: N Val: 6) |
| |
|Error at ...: TOLEAUTO:CELLS(0) in Module: |

On Friday, March 4, 2022 at 6:06:51 PM UTC+5:30, timepro timesheet wrote:
> On Friday, March 4, 2022 at 5:38:47 PM UTC+5:30, timepro timesheet wrote:
> > On Friday, March 4, 2022 at 4:12:02 PM UTC+5:30, timepro timesheet wrote:
> > > in any open excel file:
> > > -how to assign the last row#/cell# to a variable
> > > -how to get the last not-empty row#. (like eof())
> > >
> > > -how to assign the value/content of any cell to a variable
> > > (is it: thetax:=osheet:cells(xx,yy):value)
> > >
> > > thank you
> > in the cell's format, even if 'thetax:=osheet:cells(xx,yy):value' is Text
> > type(thetax) shows as N
> save the value/content of a cell(Text cell) to a dbf field
> *
> oexcel=createobject('excel.application')
> cdest=diskname()+':\tax\subfold\filename.xlsx'
> oexcel:workbooks:open(cdest)
> osheet=oexcel:activesheet
> oexcel:visible=.t.
> for xxx=the-first-row-that-is-not-blank to the-last-row-of-the-open-excel-file
> xcelinv:=osheet:cells(xxx,6):value && CRASHES HERE
> if type('xcelinv')='N';xcelinv=str(xcelinv);end && have to as even if cell(xxx,6) is Text
> sele dbffile
> seek upper(left(xcelinv,10))
> if found();repl taxinvoice with xcelinv;next
> next
>
> *error log
> |Error excel.application:ACTIVESHEET/1 Unknown error: CELLS Arguments: ( [ 1] = |
> |Type: N Val: 38 [ 2] = Type: N Val: 6) |
> | |
> |Error at ...: TOLEAUTO:CELLS(0) in Module: |

hi:

i am opening an existing excel file to read the 'values/content' of 'F' cell and save to a .dbf field.

my code:
oexcel=createobject('excel.application')
cdest=diskname()+':\tax\gst\einvoice.xlsx'
oexcel:workbooks:open(cdest)
osheet=oexcel:activesheet
*only opening the .xlsx to read, not to edit/write.
for xxx=6 to 10000 && given 10000 as am loss for the syntax for the last row in an open excel file
xceldate:=osheet:cells(xxx,4):value && IS THIS THE PROPER SYNTAX?
xcelinv:=osheet:cells(xxx,3):value && xcelinv type shows N if value is 1 or 2, even if the formatting shows Text
sele taxfile
seek xcelinv
repl gstdate with xceldate
next
oexcel:quit() && but this excel file still shows active in 'task manager', even after i exit my app.

thank you

On Saturday, March 5, 2022 at 3:16:38 PM UTC+5:30, timepro timesheet wrote:
> On Friday, March 4, 2022 at 6:06:51 PM UTC+5:30, timepro timesheet wrote:
> > On Friday, March 4, 2022 at 5:38:47 PM UTC+5:30, timepro timesheet wrote:
> > > On Friday, March 4, 2022 at 4:12:02 PM UTC+5:30, timepro timesheet wrote:
> > > > in any open excel file:
> > > > -how to assign the last row#/cell# to a variable
> > > > -how to get the last not-empty row#. (like eof())
> > > >
> > > > -how to assign the value/content of any cell to a variable
> > > > (is it: thetax:=osheet:cells(xx,yy):value)
> > > >
> > > > thank you
> > > in the cell's format, even if 'thetax:=osheet:cells(xx,yy):value' is Text
> > > type(thetax) shows as N
> > save the value/content of a cell(Text cell) to a dbf field
> > *
> > oexcel=createobject('excel.application')
> > cdest=diskname()+':\tax\subfold\filename.xlsx'
> > oexcel:workbooks:open(cdest)
> > osheet=oexcel:activesheet
> > oexcel:visible=.t.
> > for xxx=the-first-row-that-is-not-blank to the-last-row-of-the-open-excel-file
> > xcelinv:=osheet:cells(xxx,6):value && CRASHES HERE
> > if type('xcelinv')='N';xcelinv=str(xcelinv);end && have to as even if cell(xxx,6) is Text
> > sele dbffile
> > seek upper(left(xcelinv,10))
> > if found();repl taxinvoice with xcelinv;next
> > next
> >
> > *error log
> > |Error excel.application:ACTIVESHEET/1 Unknown error: CELLS Arguments: ( [ 1] = |
> > |Type: N Val: 38 [ 2] = Type: N Val: 6) |
> > | |
> > |Error at ...: TOLEAUTO:CELLS(0) in Module: |
> hi:
>
> i am opening an existing excel file to read the 'values/content' of 'F' cell and save to a .dbf field.
>
> my code:
> oexcel=createobject('excel.application')
> cdest=diskname()+':\tax\gst\einvoice.xlsx'
> oexcel:workbooks:open(cdest)
> osheet=oexcel:activesheet
> *only opening the .xlsx to read, not to edit/write.
> for xxx=6 to 10000 && given 10000 as am loss for the syntax for the last row in an open excel file
> xceldate:=osheet:cells(xxx,4):value && IS THIS THE PROPER SYNTAX?
> xcelinv:=osheet:cells(xxx,3):value && xcelinv type shows N if value is 1 or 2, even if the formatting shows Text
> sele taxfile
> seek xcelinv
> repl gstdate with xceldate
> next
> oexcel:quit() && but this excel file still shows active in 'task manager', even after i exit my app.
>
> thank you

this does not work. (still appears in task manager under 'background process')
oexcel:quit()
oExcel = NIL
release oExcel

On Friday, March 4, 2022 at 4:12:02 PM UTC+5:30, timepro timesheet wrote:
> in any open excel file:
> -how to assign the last row#/cell# to a variable
> -how to get the last not-empty row#. (like eof())
>
> -how to assign the value/content of any cell to a variable
> (is it: thetax:=osheet:cells(xx,yy):value)
>
> thank you

a member mailed me this:
xcelRows:=osheet:UsedRange:Rows:Count()
it works, thank you.