amol1234 Posted February 22, 2024 Posted February 22, 2024 (edited) 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, 2024 by amol1234 Quote
Jonathan Handojo Posted February 22, 2024 Posted February 22, 2024 (edited) 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, 2024 by Jonathan Handojo Quote
amol1234 Posted February 23, 2024 Author Posted February 23, 2024 @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 Quote
Jonathan Handojo Posted February 25, 2024 Posted February 25, 2024 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) 1 Quote
BIGAL Posted February 26, 2024 Posted February 26, 2024 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. Quote
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.