Jump to content

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


amol1234

Recommended Posts

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 by amol1234
Link to comment
Share on other sites

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 by Jonathan Handojo
Link to comment
Share on other sites

@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

Link to comment
Share on other sites

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)

 

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...