1. Forum
  2. Usenet
  3. COMP.LANG.XHARBOUR

  • get last row#/cell# in an open excel file.

    From timepro timesheet@21:1/5 to All on Fri Mar 4 02:42:01 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From timepro timesheet@21:1/5 to timepro timesheet on Fri Mar 4 04:08:46 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From timepro timesheet@21:1/5 to timepro timesheet on Fri Mar 4 04:36:49 2022
    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: |

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From timepro timesheet@21:1/5 to timepro timesheet on Sat Mar 5 01:46:37 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From timepro timesheet@21:1/5 to timepro timesheet on Sat Mar 5 03:43:06 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From timepro timesheet@21:1/5 to timepro timesheet on Sat Mar 5 03:44:02 2022
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)