amol1234 Posted February 22 Share Posted February 22 (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 by amol1234 Quote Link to comment Share on other sites More sharing options...
Jonathan Handojo Posted February 22 Share Posted February 22 (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 by Jonathan Handojo Quote Link to comment Share on other sites More sharing options...
amol1234 Posted February 23 Author Share Posted February 23 @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 Link to comment Share on other sites More sharing options...
Jonathan Handojo Posted February 25 Share Posted February 25 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 Link to comment Share on other sites More sharing options...
BIGAL Posted February 26 Share Posted February 26 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 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.