Excel handling

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)

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.

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)

(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
_$ 

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))

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#))
   )

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)

(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.

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 (:

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 .

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?

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.

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

(vlax-put-property cel "Comment"

Alquien sabe como agregar comentarios en una celda de excel?

(vlax-put-property cel "AddComment" ....)

No logro me funcione

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)

Perfec twork.... thanks.... ufff

Glad I could help

Cheers

¿You know with that method or property as may now read the comment of a cell? . Thanks

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)
 )