Author Topic: Excel library subs  (Read 26071 times)

0 Members and 1 Guest are viewing this topic.

HasanCAD

  • Swamp Rat
  • Posts: 1347
Re: Excel library subs
« Reply #30 on: March 14, 2012, 08:38:47 AM »
Haha
sleep with angels
:)
In Arab area we saying eating rice with angels

HasanCAD

  • Swamp Rat
  • Posts: 1347
Re: Excel library subs
« Reply #31 on: March 14, 2012, 08:52:12 AM »


CAB

  • Global Moderator
  • Seagull
  • Posts: 10395
Re: Excel library subs
« Reply #33 on: March 14, 2012, 11:07:45 AM »
 :evil:
I've reached the age where the happy hour is a nap. ()
Windows 10 core i7 4790k 4Ghz 32GB GTX 970
Please support this web site.

adalea03

  • Guest
Re: Excel library subs
« Reply #34 on: March 15, 2012, 02:19:36 PM »
fixo
I've been off the grid a few days; very busy.
But, good news; the clips you provided are working.
I have written 34 separate cells and saved the file successfully.
Your help has inspired some new ideas.

Thank you,
Tony

fixo

  • Guest
Re: Excel library subs
« Reply #35 on: March 16, 2012, 12:56:11 AM »
Tony
Glad I could help
Cheers :)

Oleg

fixo

  • Guest
Re: Excel library subs
« Reply #36 on: March 16, 2012, 01:12:46 AM »
it there a way to count how many filled rows there are in an excel file?
Sorry I missed your question
See user function, there is Rows Cout property of UsedRange object of Sheet within the code
Code: [Select]
(defun readcolumn (xlsheet colnum / c1 cel cels celvalue columndata r1 rows xlrange xlrow)
 
  (vlax-invoke-method xlsheet "Activate")   ;optional
 
  (setq xlrange (vlax-get-property xlsheet "UsedRange"))

  (setq xlrow (vlax-variant-value
  (vlax-get-property
    (vlax-get-property
      xlrange
      "Rows")
    "Item"
    colnum)))
 (setq rows (vlax-get-property
       (vlax-get-property xlrange "Rows")
       "Count")
)

  (vlax-invoke-method xlrange "Activate")   ;optional
  (vlax-invoke-method xlrange "Select")   ;optional


  (setq cels (vlax-get-property xlsheet "Cells"))

  (setq r1  1                   ; initial row number in column
c1  colnum   ; given column number
)
  (while (<= r1 rows)
    (setq cel (vlax-variant-value
(vlax-get-property
  cels
  "Item"
  ;; row number :
  (vlax-make-variant r1 vlax-vblong)
  ;; column number :
  (vlax-make-variant c1 vlax-vblong))))
    (setq celvalue (vlax-variant-value (vlax-get-property cel "Value2")))
    (setq columndata (cons (vl-princ-to-string celvalue) columndata))
    (setq r1 (1+ r1 )))
    (reverse columndata)
  )

andrew_nao

  • Swamp Rat
  • Posts: 566
Re: Excel library subs
« Reply #37 on: March 16, 2012, 07:46:02 AM »
sorry for being a little dense with this one
but im getting bad argument type. am i missing something?

is this a snippet that is supposed to be added to your previous code?


fixo

  • Guest
Re: Excel library subs
« Reply #38 on: March 16, 2012, 10:36:39 AM »
I repeat see Rows Count property of UsedRange object of Sheet within the code
But what is your goal?
Guess you want to get the last cell in the particular sheet, isn't it?

~'J'~

andrew_nao

  • Swamp Rat
  • Posts: 566
Re: Excel library subs
« Reply #39 on: March 16, 2012, 12:13:46 PM »
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


adalea03

  • Guest
Re: Excel library subs
« Reply #40 on: March 16, 2012, 01:49:52 PM »
Fixo
There seems to be a hitch...
Code: [Select]
(setq filepath (getfiled "* Open an Excel file to get value of selection : *"
                           (getvar "dwgprefix") "xls*" 12))

(setq xlapp (vlax-get-or-create-object "Excel.Application"))

(setq xlbooks  (vlax-get-property  xlapp 'workbooks)

        xlbook   (vl-catch-all-apply 'vla-open
                   (list (vlax-get-property xlapp 'workbooks) filepath)) ; [u]I get an error at xlbook[/u]

        xlsheets (vlax-get-property xlbook 'sheets)
        xlsheet  (vlax-get-property xlsheets 'item "BW4 Content") ;<-- change sheet name or number here
        xlcells  (vlax-get-property xlsheet 'cells))

; begin writing ("celladdress" "value")

(foreach item stii-xl-data (setcellbyaddress xlcells (car item) (cadr item)))

This happened when I was testing a few days ago, but later the function worked fine.
I noticed that I had to wait between testing so Excel could release the file.
But today the file had not been opened until I tried the XLdemo function.

fixo

  • Guest
Re: Excel library subs
« Reply #41 on: March 16, 2012, 02:11:56 PM »
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
Sorry I have not have so much time for coding in Excel
Try this code as-is, maybe you will be see the methods
Just instead of :
Code: [Select]
(setq xlrange (vlax-get-property xlsheet 'UsedRange))
use this one:

Code: [Select]
(setq xlrange(vlax-get-property (vlax-get-property (vlax-get-property xlsheet 'UsedRange) 'Columns)'Item 1))))
Code: [Select]
  ;;------------------------------code start-------------------------------------;;
  ((lambda (/ childs numrows path root sname xlapp xlbook xloid xlrange xlsheet)
     (vl-load-com)
      (setq root "HKEY_CURRENT_USER\\Software\\Microsoft\\Office")
     (setq childs (vl-registry-descendents root))
     (setq xloid (strcat
    (strcat "excel.application."
    (itoa (fix (apply 'max (mapcar 'atof childs))))
    )
    )
)    
     (setq path (getfiled "Select Excel file :"
  (getvar "dwgprefix")
  "xls;xlsx"
  16
  )
   )
     (setq xlapp (vlax-get-or-create-object xloid))
     (vla-put-visible xlapp :vlax-true)
     (vlax-put-property xlapp 'DisplayAlerts :vlax-false)
     (setq xlbook (vl-catch-all-apply
    'vla-open
    (list (vlax-get-property xlapp 'WorkBooks) path)))
     (setq xlsheet (vl-catch-all-apply
     'vlax-get-property
     (list (vlax-get-property xlbook 'Sheets)
   'Item
   1)));<-- sheet name or sheet number is allowed
     (vlax-invoke-method xlsheet 'Activate)

     (setq sname (vl-catch-all-apply
   'vlax-get-property
   (list xlsheet
'Name)))

     (setq xlrange (vlax-get-property xlsheet 'UsedRange))


     (setq numrows (vlax-get-property
     (vlax-get-property xlrange 'Rows)
     'Count)
   )
     (alert (strcat "Number of used rows on sheet "
    "\""
    sname
    "\""
    " is: "
    (itoa numrows)))

     (vlax-invoke-method
       xlbook
       'Close)
     (vlax-put-property xlapp 'DisplayAlerts :vlax-true)
     (vlax-invoke-method
       xlapp
       'Quit)
     (mapcar '(lambda (x)
(vl-catch-all-apply
  '(lambda ()
     (vlax-release-object x)
     )
  )
)
     (list xlrange xlsheet xlbook xlapp)
     )
     (setq xlapp nil)
     (gc)
     (gc)
     (gc)
     (princ)))
;;------------------------------code end-------------------------------------;;

~'J'~

fixo

  • Guest
Re: Excel library subs
« Reply #42 on: March 16, 2012, 02:23:13 PM »
Fixo
There seems to be a hitch...

This happened when I was testing a few days ago, but later the function worked fine.
I noticed that I had to wait between testing so Excel could release the file.
But today the file had not been opened until I tried the XLdemo function.
I don't know why, indeed, perhaps by reason of Excel versions or other settings, say Windows features etc
Try to use same way I posted above for @andrew_nao
for build correct Excel application string

~'J'~

adalea03

  • Guest
Re: Excel library subs
« Reply #43 on: March 16, 2012, 02:55:00 PM »
Will do, Fixo.

Thanks.

adalea03

  • Guest
Re: Excel library subs
« Reply #44 on: March 16, 2012, 05:32:28 PM »
That code did it.
All seems well now.

Thanks, Fixo.
Tony