Getting Error : bad argument type: VLA-OBJECT #<variant 9 ...>

[amol1234]

I am trying to find search value into I column and fetch its respective P column value from Autolisp program. but its giving me Error : bad argument type: VLA-OBJECT #<variant 9 ...>.

(defun ExcelCellReader (search-value)

  ;; Find the row number where the search value is located in column "A"

  (setq search-column "A")

  (setq data-column "P")



  ;; Find the row number where the search value is located in the search column

  (setq search-row (FindValueInColumn search-column search-value))



  ;; If the search value is found in the search column

  (if search-row

      (progn

        ;; Fetch the data from the corresponding row in the data column

        (setq data-value (GetCellValue search-row data-column))



        ;; Print the fetched data

        (princ "Data found in column P: ")

        (princ data-value)

        (princ "\n")

      )

      ;; If the search value is not found

      (princ "Search value not found.\n")

  )

)



(defun FindValueInColumn (column value)

  ;; Get the range of cells in the specified column

  (setq range (vlax-get-property (vlax-get-property mysheet 'Columns) "Item" column))



  ;; Search for the value in the column

  (setq cell (vlax-invoke-method range 'Find value))



  ;; If the value is found, return the row number; otherwise, return nil

  (if cell

      (vlax-get-property cell 'Row)

    nil

  )

)



(defun GetCellValue (row column)

  ;; Get the cell value at the specified row and column

  (setq cell-address (strcat column (itoa row)))

  (setq cell-range (vlax-get-property (vlax-get-property mysheet 'Cells) "Item" cell-address))

  (vlax-variant-value (vlax-get-property cell-range 'Value))

)



(defun trial_program(excel-file search-value)

  (OpenExcel excel-file)

  (GetTab "MEC_7055")

  (ExcelCellReader search-value)

  (CloseExcel)

)



(defun OpenExcel (exfile)

  (setq myfile (findfile exfile))                                      

  (if (/= myfile nil)                                                  

      (progn                                                            

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

        (vla-put-visible myxl :vlax-false)                              

        (vlax-put-property myxl 'DisplayAlerts :vlax-false)            

        (setq mybook (vl-catch-all-apply 'vla-open (list (vlax-get-property myxl "WorkBooks") myfile)))

      )

  )

)



(defun CloseExcel ()

  (vl-catch-all-apply 'vlax-invoke-method (list mybook "Close"))

  (vl-catch-all-apply 'vlax-invoke-method (list myxl "Quit"))

  (vl-catch-all-apply 'vlax-release-object mycell)

  (vl-catch-all-apply 'vlax-release-object myrange)

  (vl-catch-all-apply 'vlax-release-object mysheet)

  (vl-catch-all-apply 'vlax-release-object mybook)  

  (vl-catch-all-apply 'vlax-release-object myxl)

  (setq myfile nil myxl nil mybook nil mysheet nil myrange nil

        mytab nil mycell nil excell nil)                              

  (gc)                                                              

)



(defun GetTab (mytab)

  (if (/= myxl nil)                                                  

      (progn                                                            

        (setq mysheet (vl-catch-all-apply 'vlax-get-property (list (vlax-get-property mybook "Sheets") "Item" mytab)))

        (if (not (vl-catch-all-error-p mysheet))                      

            (vlax-invoke-method mysheet "Activate")                    

            (setq mysheet nil))                                        

      )

      (setq mysheet nil))                                              

  mysheet                                                            

)



(defun GetCell (excell)

  (if (/= myxl nil)                                                    

      (progn                                                            

        (setq myrange (vlax-get-property (vlax-get-property mysheet 'Cells) "Range" excell))

        (setq mycell (vlax-variant-value (vlax-get-property myrange 'Value2)))

      )

    (setq mycell nil))                                                

  mycell                                                              

)



(trial_program "C:\\Users\\Amol1203\\Documents\\Grob\\Excel_work\\a.xlsx" "b")

I am trying this code I do not know where I am missing anything. Can anyone help me in this. I am new to Autolisp

Edited February 22 by amol1234

[Jonathan Handojo]

I don't know about being new to AutoLISP, but if you're able to link AutoCAD to Excel, then I'd say you're certainly no beginner.

 

You can have a look at this to try and debug your code where it fails: Debugging Code with the Visual LISP IDE. If you still can't resolve the issue, I'll have a closer look a bit later.

 

Meanwhile, when it comes to Excel, I more or less like to use this to get a property to shorten the coding some:

 

(defun Excel:GetProperty (vla lst)
    (foreach x lst
        (if (listp x)
            (setq vla (apply 'vlax-get-property (cons vla x)))
            (setq vla (apply 'vlax-get-property (list vla x)))
        )
        (if (eq (type vla) 'variant) (setq vla (vlax-variant-value vla)))
    )
    vla
)

 

So you can do something like to, for example, get the content of a particular cell in a sheet:

 

(Excel:GetProperty <worksheet_vla>
  (list 
    "Cells"
    (list "Item" <row_number> <column_number>)
    "Value2"
  )
)

 

Edited February 22 by Jonathan Handojo

[amol1234]

@Jonathan Handojo Thanks for replying. I am using VS code to edit my program and cannot add this debugger to it. I assume this is for inbuild VBA editor. Actually I do not know how to use in built Autocad VBA editor. 

 

Anyway, I think you suggested me in you code to fetch cell value of particular row and column I already achieved it in program. I want to apply search operation for certain value in excel in specified column and fetch its respective value from another column.

 

eg. In this case like in my attach excel I give input search value as a from A column, and it will fetch me b from B column. Columns for search operation are fix.

 

 

 

a.xlsx

[Jonathan Handojo]

On 2/23/2024 at 6:27 PM, amol1234 said:

@Jonathan Handojo Thanks for replying. I am using VS code to edit my program and cannot add this debugger to it. I assume this is for inbuild VBA editor. Actually I do not know how to use in built Autocad VBA editor. 

 

Anyway, I think you suggested me in you code to fetch cell value of particular row and column I already achieved it in program. I want to apply search operation for certain value in excel in specified column and fetch its respective value from another column.

 

eg. In this case like in my attach excel I give input search value as a from A column, and it will fetch me b from B column. Columns for search operation are fix.

 

 

 

a.xlsx 8.54 kB · 2 downloads

Sorry for the late reply... try this one, and change the top line file path to your actual file path of the sample that you sent:

 

(setq *Excel_File_Path* "C:\\Users\\your_excel_file\\Downloads\\a.xlsx")

(defun c:testexcel ( / *error* activeundo acadobj adoc pck adoc excel_app excel_sht excel_wb pck)
    (defun *error* ( msg )
        (vla-EndUndoMark adoc)
        (if pck (setvar "PICKADD" pck))
        (if (not (wcmatch (strcase msg T) "*break*,*cancel*,*exit*"))
            (princ (strcat "Error: " msg))
        )
    )
    (setq
        acadobj (vlax-get-acad-object)
        adoc (vla-get-ActiveDocument acadobj)
    )
    (setq pck (getvar "PICKADD"))
    (setvar "PICKADD" 2)
    (if (= 0 (logand 8 (getvar "UNDOCTL"))) (vla-StartUndoMark adoc) (setq activeundo T))
    
    (setq 
        excel_app (vlax-get-or-create-object "Excel.Application")
        excel_wb (vlax-invoke (Excel:GetProperty excel_app '("Workbooks")) "Open" *Excel_File_Path*)
        excel_sht (Excel:GetProperty excel_wb '("Sheets" ("Item" 1)))
    )
    (vlax-put-property excel_app "Visible" :vlax-true)
    (princ
        (strcat "\nCell Address: "
            (Excel:GetProperty
                (Excel:FindTest excel_sht "a" "A" "B")
                '(("Address" :vlax-false :vlax-false 1))
            )
        )
    )
    (setvar "PICKADD" pck)
    (if (not activeundo) (vla-EndUndoMark adoc))
    (princ)
)

(defun Excel:FindTest (sheet search search_column return_column / column_number find_cell)
    (setq column_number (Excel:GetProperty sheet (list (list "Range" (strcat return_column "1")) "Column")))
    (if
        (setq find_cell
            (vlax-invoke
                (Excel:GetProperty sheet
                    (list
                        (list "Range" (strcat search_column ":" search_column))
                    )
                )
                "Find" search nil nil 1 ;; <-- 1 as xlWhole
            )
        )
        (Excel:GetProperty sheet
            (list 
                "Cells"
                (list "Item" (Excel:GetProperty find_cell '("Row")) column_number)
            )
        )
    )
)

(defun Excel:GetProperty (vla lst)
    (foreach x lst
        (if (listp x)
            (setq vla (apply 'vlax-get-property (cons vla x)))
            (setq vla (apply 'vlax-get-property (list vla x)))
        )
        (if (eq (type vla) 'variant) (setq vla (vlax-variant-value vla)))
    )
    vla
)

(vl-load-com)

 

[BIGAL]

Just a couple of hints Excel can work with row & column numbers via lisp, its just a case of sometimes convert to "P33" or (16 33). 

 

In the attached is get & put using both column,row and alpha cell labels. Its not an answer to your question but rather a library of common Excel functions from many contributors. Who I am grateful to and Authors should be named in their code function when you use them.

 

Alan Excel library.lsp Note updated 24/02/2024.