OK, before you yell at me I did try to search using "Excel" but you guys use the term "Excellent" WAY too much around here! :-Dtry READ EXCEL
Does any one have a link or code for retrieving info in an excel spreadsheet via AutoLISP / VLisp?
Thank you for any help!!
I use google often for swamp searches too. 8-)Well, I feel like a noob; I just use theSwamp to search theSwamp.
Check this out as well James
http://web2.airmail.net/terrycad/LISP/GetExcel.lsp
let me do that for you (http://www.google.com/search?q=%22%28command+text%22+site%3Awww.theswamp.org&hl=en&num=10&lr=&ft=i&cr=&safe=images&tbs=)or this (http://www.lmgtfy.com/?q=%22(command+text%22+site%3Awww.theswamp.org)
let me do that for you (http://www.google.com/search?q=%22%28command+text%22+site%3Awww.theswamp.org&hl=en&num=10&lr=&ft=i&cr=&safe=images&tbs=)or this (http://www.lmgtfy.com/?q=%22(command+text%22+site%3Awww.theswamp.org)
*you're*let me do that for you (http://www.google.com/search?q=%22%28command+text%22+site%3Awww.theswamp.org&hl=en&num=10&lr=&ft=i&cr=&safe=images&tbs=)or this (http://www.lmgtfy.com/?q=%22(command+text%22+site%3Awww.theswamp.org)
Oh, now your just showing off :evil:
I realize that Google search works, and I appreciate the syntax example since I've never quite figured out Google search syntax works. Maybe its just me, but I prefer to use the search built-in to a web site when possible.I always start here:
:-Dlet me do that for you (http://www.google.com/search?q=%22%28command+text%22+site%3Awww.theswamp.org&hl=en&num=10&lr=&ft=i&cr=&safe=images&tbs=)or this (http://www.lmgtfy.com/?q=%22(command+text%22+site%3Awww.theswamp.org)
Oh, now you're just showing off :evil:
maybe you and se7en could start a support group :PYou'll have to check with Krush and see if he's accepting any new applicants at this time. :wink:
;;-------------------------------------------------------------------------------
;; gc:WriteExcel
;; Ecrit dans un fichier Excel
;;
;; Arguments : 4
;; filename : chemin complet du fichier
;; sheet : nom de la feuille (ou nil pour la feuille courante)
;; startRange : nom de la cellule de départ (ou nil pour "A1")
;; dataList : liste de sous-listes contenant les données (une sous liste par rangée)
;;-------------------------------------------------------------------------------
(defun gc:WriteExcel (filename sheet startRange dataList / *error* xlApp
wBook save sheets active start row col rng
n cell
)
(vl-load-com)
(defun *error* (msg)
(and msg
(/= msg "Fonction annulée")
(princ (strcat "\nErreur: " msg))
)
(and wBook (vlax-invoke-method wBook 'Close :vlax-False))
(and xlApp (vlax-invoke-method xlApp 'Quit))
(and reg (vlax-release-object reg))
(mapcar (function (lambda (obj) (and obj (vlax-release-object obj))))
(list cell rng wBook xlApp)
)
(gc)
)
(setq xlapp (vlax-get-or-create-object "Excel.Application"))
(if (findfile filename)
(setq wBook (vlax-invoke-method (vlax-get-property xlapp 'WorkBooks) 'Open filename)
save T
)
(setq wBook (vlax-invoke-method (vlax-get-property xlapp 'WorkBooks) 'Add))
)
(if sheet
(progn
(setq sheets (vlax-get-property xlApp 'Sheets))
(vlax-for s sheets
(if (= (strcase (vlax-get-property s 'Name)) (strcase sheet))
(progn
(vlax-invoke-method s 'Activate)
(setq active T)
)
)
)
(or active
(vlax-put-property (vlax-invoke-method sheets 'Add) 'Name sheet)
)
)
)
(if startRange
(setq start (gc:ColumnRow startRange)
col (car start)
row (cadr start)
)
(setq col 1
row 1
)
)
(setq rng (vlax-get-property xlApp 'Cells))
(vlax-invoke-method rng 'Clear)
(foreach sub dataList
(setq n col)
(foreach data sub
(setq cell (vlax-variant-value (vlax-get-property rng 'Item row n)))
(if (= (type data) 'STR)
(vlax-put-property cell 'NumberFormat "@")
)
(vlax-put-property cell 'Value2 data)
(setq n (1+ n))
)
(setq row (1+ row))
)
(vlax-invoke-method
(vlax-get-property
(vlax-get-property xlApp 'ActiveSheet)
'Columns
)
'AutoFit
)
(if save
(vlax-invoke-method wBook 'Save)
(if (and
(< "11.0" (vlax-get-property xlapp "Version"))
(= (strcase (vl-filename-extension filename) T) ".xlsx")
)
(vlax-invoke-method wBook 'SaveAs filename 51 "" "" :vlax-false :vlax-false 1 1)
(vlax-invoke-method wBook 'SaveAs filename -4143 "" "" :vlax-false :vlax-false 1 1)
)
)
(*error* nil)
)
;;-------------------------------------------------------------------------------
;; gc:ReadExcel
;; Retourne une liste de sous-listes contenant les données (une sous liste par rangée)
;; d'un fichier Excel
;;
;; Arguments : 4
;; filename : chemin complet du fichier
;; sheet : nom de la feuille (ou nil pour la feuille courante)
;; startRange : nom de la cellule de départ (ou nil pour "A1")
;; maxRange : nom de la cellule où doit s'arrêter la lecture (nil pour toute la plage)
;;-------------------------------------------------------------------------------
(defun gc:ReadExcel (filename sheet startRange maxRange / *error*
xlApp wBook startCell startCol startRow maxCell maxCol
maxRow reg col row data sub lst
)
(defun *error* (msg)
(and msg
(/= msg "Fonction annulée")
(princ (strcat "\nErreur: " msg))
)
(and wBook (vlax-invoke-method wBook 'Close :vlax-False))
(and xlApp (vlax-invoke-method xlApp 'Quit))
(and reg (vlax-release-object reg))
(mapcar (function (lambda (obj) (and obj (vlax-release-object obj))))
(list rng wBook xlApp)
)
(gc)
)
(setq xlapp (vlax-get-or-create-object "Excel.Application")
wBook (vlax-invoke-method (vlax-get-property xlApp 'WorkBooks) 'Open filename)
)
(if sheet
(vlax-for ws (vlax-get-property xlapp 'Sheets)
(if (= (vlax-get-property ws 'Name) sheet)
(vlax-invoke-method ws 'Activate)
)
)
)
(if startRange
(setq startCell (gc:ColumnRow startRange)
startCol (car startCell)
startRow (cadr startCell)
)
(setq startRange
"A1"
startCol 1
startRow 1
)
)
(if maxRange
(setq maxCell (gc:ColumnRow MaxRange)
maxCol (1+ (car MaxCell))
maxRow (1+ (cadr MaxCell))
)
(setq reg (vlax-get-property
(vlax-get-property
(vlax-get-property xlApp 'ActiveSheet)
'Range
startRange
)
'CurrentRegion
)
maxRow (+ (vlax-get-property reg 'Row)
(vlax-get-property (vlax-get-property reg 'Rows) 'Count)
)
maxCol (+ (vlax-get-property reg 'Column)
(vlax-get-property (vlax-get-property reg 'Columns) 'Count)
)
)
)
(setq rng (vlax-get-property xlApp 'Cells)
row maxRow
)
(while (< startRow row)
(setq sub nil
col maxCol
row (1- row)
)
(while (< startCol col)
(setq col (1- col)
sub (cons
(vlax-variant-value
(vlax-get-Property
(vlax-variant-value (vlax-get-property rng 'Item row col))
'Value2
)
)
sub
)
)
)
(setq lst (cons sub lst))
)
(*error* nil)
lst
)
;;-------------------------------------------------------------------------------
;; gc:ColumnRow - Retourne une liste des indices de colonne et rangée
;; Arguments: 1
;; Cell = Référence de la cellule
;; Exemple de syntaxe : (gc:ColumnRow "IV987") -> (256 987)
;;-------------------------------------------------------------------------------
(defun gc:ColumnRow (cell / col char row)
(setq col "")
(while (< 64 (ascii (setq char (strcase (substr cell 1 1)))) 91)
(setq col (strcat col char)
cell (substr cell 2)
)
)
(if (and (/= col "") (numberp (setq row (read Cell))))
(list (gc:Alpha2Number col) row)
'(1 1) ;_ default to "A1" if there's a problem
)
)
;;-------------------------------------------------------------------------------
;; gc:Alpha2Number - Convertit une chaîne alphabétique en nombre entier
;; Function By: Gilles Chanteau from Marseille, France
;; Arguments: 1
;; str = Chaîne à convertir
;; Exemple de syntaxe : (gc:Alpha2Number "BU") = 73
;;-------------------------------------------------------------------------------
(defun gc:Alpha2Number (str / num)
(if (= 0 (setq num (strlen str)))
0
(+ (* (- (ascii (strcase (substr str 1 1))) 64)
(expt 26 (1- num))
)
(gc:Alpha2Number (substr str 2))
)
)
)
;;-------------------------------------------------------------------------------
;; gc:Number2Alpha - Convertit un nombre entier en chaîne alphabétique
;; Function By: Gilles Chanteau from Marseille, France
;; Arguments: 1
;; num = Nombre à convertir
;; Exemple de syntaxe : (gc:Number2Alpha 73) = "BU"
;;-------------------------------------------------------------------------------
(defun gc:Number2Alpha (num / val)
(if (< num 27)
(chr (+ 64 num))
(if (= 0 (setq val (rem num 26)))
(strcat (gc:Number2Alpha (1- (/ num 26))) "Z")
(strcat (gc:Number2Alpha (/ num 26)) (chr (+ 64 val)))
)
)
)
maybe you and se7en could start a support group :P
Somewhat off topic, but I have to agree with jbuzbee. This site is incredibly hard to search and have meaningful results. Punctuation gets stripped out, and there's no way to search for a phrase that I can find. Yesterday I wanted to look for (command "text" as a phrase. 103 pages of results were returned. If I limit the search to the lisp and "show your stuff" forums it is still 51 pages. The search function would be fine for most sites, but for a site that deals with programming languages it leaves a lot to be desired. I also wish that there was a way to make the forum selections sticky instead of having search default to all forums.
I don't mean for this to be taken as a complaint. More a "wish list" suggestion. This is a great site, and one that I've benefited from greatly.
(setq xlapp (vlax-get-or-create-object "Excel.Application")
xlbooks (vlax-get-property xlapp 'Workbooks)
xlbook (vlax-invoke-method xlbooks 'Add)
xlsheets (vlax-get-property xlbook 'Sheets)
xlsheet (vlax-get-property xlsheets 'Item 1)
xlcells (vlax-get-property xlsheet 'Cells)
)
(vla-put-visible xlapp :vlax-true)
;;..........................................
(setq address "B12"
text "ProjectName")
(setq xlrange(vl-catch-all-apply
'vlax-get-property
(list xlcells
'Range
(vlax-make-variant address 8))))
(vl-catch-all-apply
'vlax-put-property
(list xlrange
'Value2
(vlax-make-variant text 8)))OK, before you yell at me I did try to search using "Excel" but you guys use the term "Excellent" WAY too much around here! :-D
Excellent! Thanks!!
:-)
So, how do I apply that snippet to a specific XL file?
Tony
;;----------------------------XLDEMO.LSP-------------------------------;;
;; fixo (02012 * all rights released
;; 3/1/12
;; edited 3/13/12
(defun C:XLDEMO (/ *error* data filepath fname prop selrange value xlapp xlbook xlcells xlsheet xlsheets)
(defun *error* (msg)
(if
(vl-position
msg
'("console break"
"Function cancelled"
"quit / exit abort"
)
)
(princ "Error!")
(princ msg)
)
(princ)
)
;;;local defun
(defun setcelltext(cells row column value)
(vl-catch-all-apply
'vlax-put-property
(list cells 'Item row column
(vlax-make-variant
(vl-princ-to-string value) 8)))
)
;;;local defun
(defun wraptext (strlst / txtvalue)
(setq txtvalue "")
(while (cadr strlst)
(setq txtvalue (strcat txtvalue (strcat (car strlst) (chr 10) (chr 13))))
(setq strlst (cdr strlst))
)
(setq txtvalue (strcat txtvalue (last strlst)))
txtvalue
)
;;; local defun
(defun wrapcelltext (xapp cells xlrange txt_list)
(setq txt_list (wraptext txt_list)
)
(vl-catch-all-apply 'vlax-invoke-method (list xlrange 'Select)
)
(setq selrange (vl-catch-all-apply 'vlax-get-property (list xapp 'Selection)))
(mapcar '(lambda (prop value)
(vl-catch-all-apply
'vlax-put-property
(list selrange
prop
value
)
)
)
(list 'HorizontalAlignment 'VerticalAlignment 'WrapText
'Orientation 'AddIndent 'IndentLevel 'ShrinkToFit 'ReadingOrder
'MergeCells 'Value2
)
(list -4143 -4108 :vlax-true 0 :vlax-false 0 :vlax-false -5102 :vlax-true txt_list)
)
)
;;; local defun
(defun setcellbyaddress(xlcells address text / xlrange)
(setq xlrange(vl-catch-all-apply
'vlax-get-property
(list xlcells
'Range
(vlax-make-variant address 8))))
(vl-catch-all-apply
'vlax-put-property
(list xlrange
'Value2
(vlax-make-variant text 8)))
)
;;; main part ;;;
(setq data (list (cons "A1" "Text in cell A1")
(cons "B2" "Text in cell B2")
(cons "C4" "Text in cell C4")
(cons "B12" "Text in cell B12")
(cons "D20" "Text in cell D20")
(cons "E9" "Text in cell E9")
)
)
(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"))
(vla-put-visible xlapp :vlax-true)
(setq xlbook (vl-catch-all-apply 'vla-open
(list (vlax-get-property xlapp 'Workbooks)
filepath)
)
xlsheets (vlax-get-property xlbook 'Sheets)
xlsheet (vlax-get-property xlsheets 'Item 1);<-- change sheet name or number here
xlcells (vlax-get-property xlsheet 'Cells)
)
(vla-put-visible xlapp :vlax-true)
(foreach item data
(setcellbyaddress xlcells (car item)(cdr item))
)
;;; (vlax-invoke-method
;;; (vlax-get-property xlsheet 'Columns)
;;; 'AutoFit);optional
(vlax-invoke-method
xlbook
'SaveAs
filepath
nil
nil
nil
:vlax-false
:vlax-false
1
2
)
(vlax-invoke-method
xlbook 'Close)
(vlax-invoke-method
xlapp 'Quit)
(mapcar '(lambda (x)
(vl-catch-all-apply
'(lambda ()
(vlax-release-object x)
)
)
)
(list xlcells xlsheet xlsheets xlbook xlapp)
)
(setq xlapp nil)
(gc)(gc)(gc)
(alert (strcat "File saved as:\n" fname))
(*error* nil)
(princ)
)
(prompt "\n\t\t---\tStart command with XlDEMO\t---\n")
(princ)
(or (vl-load-com)
(princ))
;;----------------------------code end-------------------------------;;
[code]
HahaIn Arab area we saying eating rice with angels
sleep with angels
:)
...How did you record this ?
or this (http://www.lmgtfy.com/?q=%22(command+text%22+site%3Awww.theswamp.org)
...How did you record this ?
or this (http://www.lmgtfy.com/?q=%22(command+text%22+site%3Awww.theswamp.org)
it there a way to count how many filled rows there are in an excel file?Sorry I missed your question
(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)
)
(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)))
i just want to count how many filled cells (or rows) are in column ASorry I have not have so much time for coding in Excel
without having to know or open the file and scroll to the end
(setq xlrange (vlax-get-property xlsheet 'UsedRange))(setq xlrange(vlax-get-property (vlax-get-property (vlax-get-property xlsheet 'UsedRange) 'Columns)'Item 1)))) ;;------------------------------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-------------------------------------;;FixoI don't know why, indeed, perhaps by reason of Excel versions or other settings, say Windows features etc
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 just want to count how many filled cells (or rows) are in column AAndrew, try this lisp
without having to know or open the file and scroll to the end
;; §§§§§§§§§§§§§§§§§§§§§§§§§§§§§§§;;
;; = 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
)fixo, thanks for the library and your help.Let me know about :)
ill check this out
fixo, thanks for the library and your help.Let me know about :)
ill check this out
Regards,
~'J'~
Try code again, I uploaded edited version above,fixo, thanks for the library and your help.Let me know about :)
ill check this out
Regards,
~'J'~
thanks again, your library functions work like a charm
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.
Hi all ! is there function to open any excel file if it exist, otherwise create it with path and name are specified in function.
(defun C:XLO(/ as gkw newpath path xlapp xlbook xlbooks xlcell xlrange xlsheet xlsheets)
(vl-load-com)
(alert "I recommend to choose \"Add\" first")
(setq xlapp (xlgetapp))
(_set xlapp 'visible :vlax-false)
;;(xlbooksaveas xlbook path)
(if (xlhasbooks xlapp)
(setq xlbook (xlgetactivebook xlapp))
(progn
(initget 1 "Add Open")
(setq gkw (getkword "\nAdd new Excel document or Open existing file? [Add/Open] <Add> : "))
(if (eq "Open" gkw)
(progn
(setq path (getfiled "Select Excel file :" (getvar "dwgprefix") "xls;xlsx" 16))
(setq xlbooks (xlgetbooks xlapp))
(setq xlbook (xlopenbook xlbooks path))
(setq xlbook (xlgetactivebook xlapp))
)
(progn
(setq as T)
(setq xlbook (xladdbook xlapp))
(setq xlbook (xlgetactivebook xlapp))
))))
(xlshout xlapp :vlax-true)
;| do your work here |;
;;; (setq xlsheets (xlgetsheets xlbook))
;;; (setq xlsheet (xlgetsheet xlsheets 1)) ;or any sheet name
;;; (setq xlsheet (xlactivate xlsheet))
;;; (_set xlapp 'visible :vlax-true)
(if (not as)
(progn
(xlbooksave xlbook)
(xlbookclose xlbook nil))
(progn
(setq newpath (getfiled "To save Enter file name w/o extension:"
(getvar "dwgprefix")
"xls;xlsx"
1))
(if newpath
(xlbooksaveas xlbook newpath)
(progn
(xlshout xlapp :vlax-false)
(xlbookclose xlbook nil))
)
)
)
;|next three lines of code are constant for all programs which will be use 'XLFIXOLIB' library |;
(xlshout xlapp :vlax-true); - 1
(xlquit xlapp); - 2
(xlcleanup (list xlcell xlrange xlsheet xlsheets xlbook xlapp)); - 3
(setq xlapp nil)
(princ)
)