Topic: Excel library subs

[fixo]

Good to hear it, Tony
Have a nice weekend

Oleg

[fixo]

i just want to count how many filled cells (or rows) are in column A
without having to know or open the file and scroll to the end

Andrew, try this lisp
It is not complete library yet but may helps

See edited library and samples in the attachment

[codered8x]

How to append data in a sheet that has already data. Example i have one file abc.xls with data were filled  at A1:H6 range, how do i put continuous data begin A7 . Maybe it relate to Usedrange property of sheet object (A1:H6), but i don't know how to get IDrange of A7 cell ( in many Method and Property of Range).

[fixo]

See if this helps

Code: [Select]

;; §§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§;;
;; = Get last cell in WorkSheet = ;;
;; §§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§;;
(defun xlsheetlastcell (xlapp xlsheet /)

  (vl-catch-all-apply
    'vlax-invoke-method
    (list xlapp 'volatile))
  (setq xlsheet (xlactivate xlsheet))

  (setq findrange (xlgetusedrange xlsheet))
  (setq rownum (vlax-get-property
(vlax-get-property
   findrange
   'rows)
'count))
  (setq lastrow (vlax-variant-value
  (vlax-get-property
    (vlax-get-property
      findrange
      'rows)
    'item
    rownum)))
  (setq lastcell (vlax-get-property lastRow 'end 2))
  lastcell
  )
In addition:
From this cell you may easy get the 'Row and 'Column property

~'J'~

[andrew_nao]

fixo, thanks for the library and your help.

 ill check this out

[fixo]

fixo, thanks for the library and your help.

 ill check this out

Let me know about
Regards,

~'J'~

[codered8x]

Thank Fixo, i have retrieved desire value but i have some question :
- how many column of each row in (vlax-get-property range 'rows), i wonder it equal number column of range or all column in sheet.
- can you explain 'volatile method ?

[fixo]

The Range object has 'Row and 'Column property as you know
There is no difference if this is a row or single cell or any diapazone of cells
Btw I reposted excerpt from last release of this library
where you will be see the methods to look up the first or last cell
This work is not finished yet though, I should to walk to continue to work with
this lisp further
Regards,

Oleg

[codered8x]

i finded your post at here, it is more detailed to understand and get desire value.

[andrew_nao]

fixo, thanks for the library and your help.

 ill check this out

Let me know about
Regards,

~'J'~

thanks again, your library functions work like a charm

[fixo]

Sorry I have disagreed
This a crappy code of mine
I'm still working on this library and I should
reload them later
Now I want to remove this code from there temporarily....
Regards

Oleg

[fixo]

fixo, thanks for the library and your help.

 ill check this out

Let me know about
Regards,

~'J'~

thanks again, your library functions work like a charm

Try code again, I uploaded edited version above,
Regards

~'J'~

[fixo]

Here is latest version of XLFIXOLIB.lsp and one more sample included,
Regards,

~'J'~

[adalea03]

Great stuff Fixo,

I'm still thinking on it and wondering if
a "defined name" in excel is the same as an xlrange.
If I could write to a cell using only its "defined name"
(such as "CubicFeet") it wouldn't matter where in the workbook that cell was,
even if it were moved.

Is that right, or did I miss something.

Your library has inspired me to dig into the Cad/Excel thinggy.

But, I cant quite grasp the syntax for using a "defined name" as the argument
for getting cell address or xlrange and writing to it. Lots of errors so far.

Thanks,
Tony

[gile]

But, I cant quite grasp the syntax for using a "defined name" as the argument
for getting cell address or xlrange and writing to it. Lots of errors so far.

Code - Lisp: [Select]

1. (setq cell (vlax-get-property xlsheet "Range" "defined name"))