Costinbos77 Posted March 9, 2013 Share Posted March 9, 2013 (edited) Can be written expression for val and otherwise ( taking Excel cell content ) ? (setq XlsApp (vlax-get-or-create-object "Excel.Application") myWBooks (vlax-get-property XlsApp 'Workbooks) myWBook (vla-Add myWBooks) aSht (vlax-get-property XlsApp 'ActiveSheet) cel (vlax-get-property aSht 'Cells) ) (vla-put-Visible XlsApp 1) [color=red](setq val (vlax-variant-value (vla-get-Value (vlax-variant-value [/color][color=red](vlax-get-property cel 'Item r c))) ) )[/color] Because writing in Excel cell, is very simple : (vlax-put-property cel 'Item r c value) Edited March 12, 2013 by Costinbos77 Quote Link to comment Share on other sites More sharing options...
Lee Mac Posted March 9, 2013 Share Posted March 9, 2013 The following is untested, but try: (vlax-get cel 'item r c) The undocumented vlax-get function should return data expressed using native AutoLISP data types, avoiding the need to convert from Visual LISP Variants & Safearrays. Quote Link to comment Share on other sites More sharing options...
Costinbos77 Posted March 9, 2013 Author Share Posted March 9, 2013 (edited) Tested : (vlax-get cel 'item 1 1) ; error: too many arguments or : (vlax-get cel 'item "A1") ; error: too many arguments or : (vlax-get cel "A1") ; error: ActiveX Server returned the error: unknown name: "A1" vlax-get is only for compatibility with AutoCAD R14 (vlax-get cel 'Item r c) Edited March 12, 2013 by Costinbos77 Quote Link to comment Share on other sites More sharing options...
Costinbos77 Posted March 9, 2013 Author Share Posted March 9, 2013 (vlax-dump-object cel t) ; Range: nil ; Property values: ; AddIndent = 0 ; Address (RO) = ...Indexed contents not shown... ; AddressLocal (RO) = ...Indexed contents not shown... ; AllowEdit (RO) = -1 ; Application (RO) = #<VLA-OBJECT _Application 18fee4dc> ; Areas (RO) = #<VLA-OBJECT Areas 1afea0f4> ; Borders (RO) = #<VLA-OBJECT Borders 1afea184> ; Cells (RO) = #<VLA-OBJECT Range 1afea214> ; Characters (RO) = ...Indexed contents not shown... ; Column (RO) = 1 ; Columns (RO) = #<VLA-OBJECT Range 1afea2a4> ; ColumnWidth = 8.43 ; Comment (RO) = nil ; Count (RO) = Exception occurred ; CountLarge (RO) = unsupported result type: 20 ; Creator (RO) = 1480803660 ; CurrentArray (RO) = Microsoft Office Excel: No cells were found. ; CurrentRegion (RO) = #<VLA-OBJECT Range 1afea334> ; Dependents (RO) = Microsoft Office Excel: No cells were found. ; DirectDependents (RO) = Microsoft Office Excel: No cells were found. ; DirectPrecedents (RO) = Microsoft Office Excel: No cells were found. ; End (RO) = ...Indexed contents not shown... ; EntireColumn (RO) = #<VLA-OBJECT Range 1afea3c4> ; EntireRow (RO) = #<VLA-OBJECT Range 1b111314> ; Errors (RO) = Exception occurred ; Font (RO) = #<VLA-OBJECT Font 1b1113a4> ; FormatConditions (RO) = #<VLA-OBJECT FormatConditions 1b111434> ; Formula = Exception occurred ; FormulaArray = "123" ; FormulaHidden = 0 ; FormulaLocal = Exception occurred ; FormulaR1C1 = Exception occurred ; FormulaR1C1Local = Exception occurred ; HasArray (RO) = 0 ; HasFormula (RO) = 0 ; Height (RO) = 1.57286e+007 ; Hidden = Microsoft Office Excel: Unable to get the Hidden property of the Range class ; HorizontalAlignment = 1 ; Hyperlinks (RO) = #<VLA-OBJECT Hyperlinks 1b1114c4> ; ID = "" ; IndentLevel = 0 ; Interior (RO) = #<VLA-OBJECT Interior 1b111554> ; Item = ...Indexed contents not shown... ; Left (RO) = 0.0 ; ListHeaderRows (RO) = 0 ; ListObject (RO) = nil ; LocationInTable (RO) = Microsoft Office Excel: Unable to get the LocationInTable property of the Range class ; Locked = -1 ; MDX (RO) = Exception occurred ; MergeArea (RO) = Exception occurred ; MergeCells = 0 ; Name = Exception occurred ; Next (RO) = #<VLA-OBJECT Range 1b1115e4> ; NumberFormat = "General" ; NumberFormatLocal = "General" ; Offset (RO) = ...Indexed contents not shown... ; Orientation = -4128 ; OutlineLevel = Microsoft Office Excel: Unable to get the OutlineLevel property of the Range class ; PageBreak = Microsoft Office Excel: Unable to get the PageBreak property of the Range class ; Parent (RO) = #<VLA-OBJECT _Worksheet 1b06702c> ; Phonetic (RO) = #<VLA-OBJECT Phonetic 1b110ecc> ; Phonetics (RO) = nil ; PivotCell (RO) = Exception occurred ; PivotField (RO) = Microsoft Office Excel: Unable to get the PivotField property of the Range class ; PivotItem (RO) = Microsoft Office Excel: Unable to get the PivotItem property of the Range class ; PivotTable (RO) = Microsoft Office Excel: Unable to get the PivotTable property of the Range class ; Precedents (RO) = Microsoft Office Excel: No cells were found. ; PrefixCharacter (RO) = "" ; Previous (RO) = Microsoft Office Excel: Unable to get the Previous property of the Range class ; QueryTable (RO) = Exception occurred ; Range (RO) = ...Indexed contents not shown... ; ReadingOrder = -5002 ; Resize (RO) = ...Indexed contents not shown... ; Row (RO) = 1 ; RowHeight = 15.0 ; Rows (RO) = #<VLA-OBJECT Range 1b110f5c> ; ServerActions (RO) = Exception occurred ; ShowDetail = Microsoft Office Excel: Unable to get the ShowDetail property of the Range class ; ShrinkToFit = 0 ; SmartTags (RO) = #<VLA-OBJECT SmartTags 1b110fec> ; SoundNote (RO) = #<VLA-OBJECT SoundNote 1b11107c> ; Style = #<VLA-OBJECT Style 1b11110c> ; Summary (RO) = Microsoft Office Excel: Unable to get the Summary property of the Range class ; Text (RO) = "123" ; Top (RO) = 0.0 ; UseStandardHeight = -1 ; UseStandardWidth = -1 ; Validation (RO) = #<VLA-OBJECT Validation 1b11119c> ; Value = ...Indexed contents not shown... ; Value2 = Exception occurred ; VerticalAlignment = -4107 ; Width (RO) = 786432.0 ; Worksheet (RO) = #<VLA-OBJECT _Worksheet 1b06702c> ; WrapText = 0 ; XPath (RO) = Microsoft Office Excel: The specified range is invalid due to one or more of the following reasons: •The range refers to cells in more than one column. •The range contains mapped cells from more than one XML map. •The range contains both mapped and unmapped cells. •The range contains both table and non-table cells. •The range is not contiguous. ; _Default = ...Indexed contents not shown... ; _NewEnum (RO) = #<IUnknown 1b0bfd6c> ; Methods supported: ; Activate () ; AddComment (1) ; AdvancedFilter (4) ; ApplyNames (7) ; ApplyOutlineStyles () ; AutoComplete (1) ; AutoFill (2) ; AutoFilter (5) ; AutoFit () ; AutoOutline () ; BorderAround (4) ; Calculate () ; CalculateRowMajorOrder () ; CheckSpelling (4) ; Clear () ; ClearComments () ; ClearContents () ; ClearFormats () ; ClearNotes () ; ClearOutline () ; ColumnDifferences (1) ; Consolidate (5) ; Copy (1) ; CopyFromRecordset (3) ; CopyPicture (2) ; CreateNames (4) ; Cut (1) ; DataSeries (6) ; Delete (1) ; DialogBox () ; Dirty () ; EditionOptions (7) ; ExportAsFixedFormat (9) ; FillDown () ; FillLeft () ; FillRight () ; FillUp () ; Find (9) ; FindNext (1) ; FindPrevious (1) ; FunctionWizard () ; Group (4) ; Insert (2) ; InsertIndent (1) ; Justify () ; ListNames () ; Merge (1) ; NavigateArrow (3) ; NoteText (3) ; Parse (2) ; PasteSpecial (4) ; PrintOut ( ; PrintPreview (1) ; RemoveDuplicates (2) ; RemoveSubtotal () ; Replace ( ; RowDifferences (1) ; Run (30) ; Select () ; SetPhonetic () ; Show () ; ShowDependents (1) ; ShowErrors () ; ShowPrecedents (1) ; Sort (15) ; SortSpecial (15) ; Speak (2) ; SpecialCells (2) ; SubscribeTo (2) ; Subtotal (6) ; Table (2) ; TextToColumns (14) ; Ungroup () ; UnMerge () T _$ Quote Link to comment Share on other sites More sharing options...
BIGAL Posted March 10, 2013 Share Posted March 10, 2013 Have a look at GETEXCEL.lsp bit cut out (setq ExcelRange (vlax-get-property *ExcelApp% "Cells")) (foreach Item Data@ (vlax-put-property ExcelRange "Item" Row# Column# (vl-princ-to-string Item)) Quote Link to comment Share on other sites More sharing options...
Costinbos77 Posted March 10, 2013 Author Share Posted March 10, 2013 in GETEXCEL.lsp it Use a similar method only instead of item, value : (repeat MaxColumn# (setq Range$ (strcat (Number2Alpha Column#)(itoa Row#))) (setq ExcelRange^ (vlax-get-property *ExcelApp% "Range" Range$)) (setq ExcelVariant^ (vlax-get-property ExcelRange^ [color=red]'Value[/color])) (setq ExcelValue (vlax-variant-value ExcelVariant^)) (setq ExcelValue (cond ((= (type ExcelValue) 'INT) (itoa ExcelValue)) ((= (type ExcelValue) 'REAL) (rtosr ExcelValue)) ((= (type ExcelValue) 'STR) (vl-string-trim " " ExcelValue)) ((/= (type ExcelValue) 'STR) "") );cond );setq (setq Data@ (append Data@ (list ExcelValue))) (setq Column# (1+ Column#)) ) Quote Link to comment Share on other sites More sharing options...
Lee Mac Posted March 10, 2013 Share Posted March 10, 2013 Tested : (vlax-get cel 'item 1 1) ; error: too many arguments or : (vlax-get cel 'item "A1") ; error: too many arguments Since the above return 'too many arguments', perhaps try: (vlax-get cel 'item) Quote Link to comment Share on other sites More sharing options...
Costinbos77 Posted March 10, 2013 Author Share Posted March 10, 2013 (vlax-get cel 'item) ; error: Invalid number of parameters (vlax-get-property cel 'Item 1 1) #<variant 9 [color=red]1234.567[/color]> I filled topic #1 with everything you need, so you can make attempts. Quote Link to comment Share on other sites More sharing options...
fabriciorby Posted March 11, 2013 Share Posted March 11, 2013 (edited) I've made something like this ;for example (setq row 1 column 1) (setq cell (vlax-variant-value(vlax-get-property cells 'Item (vlax-make-variant row)(vlax-make-variant column)))) (setq celltext (vlax-variant-value (vlax-get-property cell 'Value))) It worked for me (: Edited March 12, 2013 by fabriciorby mistakes in code Quote Link to comment Share on other sites More sharing options...
Costinbos77 Posted March 12, 2013 Author Share Posted March 12, 2013 (edited) Thanks for the reply, but fabriciorby, who is cells variable ? It is a disadvantage because you have to determine cell variable for each cell you read. In my version, the cel variable is available for entire sheet . Edited March 13, 2013 by Costinbos77 Quote Link to comment Share on other sites More sharing options...
fabriciorby Posted March 12, 2013 Share Posted March 12, 2013 in this case i got the cells from the activesheet, that is by default sheet1 (setq excel (vlax-create-object "Excel.Application") ;; open Excel activesheet (vlax-get-property excel 'ActiveSheet) ;; get the active sheet cells (vlax-get-property activesheet 'Cells) ;; get cells from a sheet ) and sorry, i forgot to say that haha but if you don't use the 'value function, how do you get the text from the cell? Quote Link to comment Share on other sites More sharing options...
Costinbos77 Posted March 13, 2013 Author Share Posted March 13, 2013 (edited) Topic # 1, variable val in the red line. If the two lines merge into one from #9, you get the same thing as me: (setq celltext (vlax-variant-value (vlax-get-property (vlax-variant-value(vlax-get-property cells 'Item (vlax-make-variant row)(vlax-make-variant column))) 'Value))) It is not necessary row and column to be variants, they can be integers. Edited March 13, 2013 by Costinbos77 Quote Link to comment Share on other sites More sharing options...
fabriciorby Posted March 13, 2013 Share Posted March 13, 2013 Topic # 1, variable val in the red line. If the two lines merge into one from #9, you get the same thing as me: (setq celltext (vlax-variant-value (vlax-get-property (vlax-variant-value(vlax-get-property cells 'Item (vlax-make-variant row)(vlax-make-variant column))) 'Value))) It is not necessary row and column to be variants, they can be integers. Yeah, you're right. I just saw your first post again haha Quote Link to comment Share on other sites More sharing options...
cadotecnia Posted July 8, 2013 Share Posted July 8, 2013 (vlax-put-property cel "Comment" Quote Link to comment Share on other sites More sharing options...
cadotecnia Posted July 8, 2013 Share Posted July 8, 2013 Alquien sabe como agregar comentarios en una celda de excel? (vlax-put-property cel "AddComment" ....) No logro me funcione Quote Link to comment Share on other sites More sharing options...
fixo Posted July 8, 2013 Share Posted July 8, 2013 Here is a quick demo for newly created Excel file, grab code block toadd comment from there (defun C:ADC (/ *error* comments fname xlapp xlbook xlbooks xlcells xlrange xlsheet xlsheets) (defun *error* (msg) (if (vl-position msg '("console break" "Function cancelled" "quit / exit abort" ) ) (princ "Error!") (princ msg) ) (princ) ) (if (setq comments "Put your comments\nin this line\nor and/or next line") (progn (alert "Wait...") (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 xlrange(vl-catch-all-apply 'vlax-get-property (list xlsheet 'range (vlax-make-variant "B2" 8 ))))) (vl-catch-all-apply 'vlax-invoke-method (list xlrange 'select)) (setq xlcomment (vl-catch-all-apply 'vlax-invoke-method (list xlrange 'addcomment (vlax-make-variant comments ))) (vl-catch-all-apply 'vlax-put-property (list xlcomment 'visible :vlax-true)) (setq fname (strcat (getvar "dwgprefix")(vl-string-right-trim ".dwg" (getvar "dwgname")) "_Comments.xls")); <~~.xlsx (vlax-invoke-method xlbook 'SaveAs fname nil nil nil :vlax-false :vlax-false 1 2 ) (vlax-invoke-method xlbook 'Close) (gc) (vlax-invoke-method xlapp 'Quit) (mapcar '(lambda (x) (vl-catch-all-apply '(lambda () (vlax-release-object x) ) ) ) (list xlrange xlcells xlsheet xlsheets xlbook xlbooks xlapp) ) (setq xlapp nil) (gc)(gc) ;(alert (strcat "File saved as:\n" fname)) ) ) (*error* nil) (princ) ) (prompt "\n\t\t---\tStart command with ADC\t---\n") (princ) Quote Link to comment Share on other sites More sharing options...
cadotecnia Posted July 8, 2013 Share Posted July 8, 2013 Perfec twork.... thanks.... ufff Quote Link to comment Share on other sites More sharing options...
fixo Posted July 8, 2013 Share Posted July 8, 2013 Glad I could help Cheers Quote Link to comment Share on other sites More sharing options...
cadotecnia Posted July 8, 2013 Share Posted July 8, 2013 ¿You know with that method or property as may now read the comment of a cell? . Thanks Quote Link to comment Share on other sites More sharing options...
fixo Posted July 8, 2013 Share Posted July 8, 2013 Try this out (defun C:RCOM (/ desc x xlapp xlbook xlbooks xlcomment xlrange xlshape xlsheet xlsheets) (vl-load-com) ;make proper excel objects (setq xlapp (vlax-get-or-create-object "Excel.Application") xlbooks (vlax-get xlApp 'Workbooks) xlbook (vlax-invoke-method xlbooks 'Open "C:\\YourFilenamePath.xls"); <~~ full name of excel file xlsheets (vlax-get xlbook 'sheets) xlsheet (vlax-get-property xlsheets 'item 1) ) (vla-put-visible xlapp :vlax-true) (setq xlrange(vl-catch-all-apply 'vlax-get-property (list xlsheet 'range (vlax-make-variant "B2" 8 )))) (vl-catch-all-apply 'vlax-invoke-method (list xlrange 'select)) (setq xlcomment (vl-catch-all-apply 'vlax-get (list xlrange 'Comment))) (vl-catch-all-apply 'vlax-put-property (list xlcomment 'visible :vlax-true)) (setq xlshape (vl-catch-all-apply 'vlax-get (list xlcomment 'Shape))) (setq desc (vl-catch-all-apply 'vlax-get-property (list xlshape 'alternativetext))) (vlax-invoke-method xlbook 'close :vlax-false) (gc); <-- might be added right after workbook closed ! (vlax-invoke-method xlapp 'quit) (mapcar '(lambda(x)(vl-catch-all-apply 'vlax-release-object (list x))) (list xlshape xlcomment xlRange xlsheet xlsheets xlbook xlapp)) (setq xlapp nil) (gc);; repeat clean memory (optional) (if desc (alert desc)) (princ) ) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.