Excel link - Sanity check please

[swanny89]

Hi all,

I have an idea for an AutoCAD lisp, but I don't know if it's possible. I'd like to get a sanity check before I attempt creating it! The process I'm thinking of is as follows;

1. Iterate through a number of blocks in the .dwg (blocks are always in model space)

2. For each block grab an attribute value held within the block (FIN-B / FIN-C) in the example dwg - This data will be entered manually for each block

3. Search for this "value" in an external excel file (prompt user to pick a file) - The value will always be in Column A

4. Copy the information held in the three relevant cells (Column B - the the same row and the next two rows below) back into the block (FIN-LINE-1 / 2 / 3)

5. Repeat this process until all named blocks are populated (theExcel.xlsxre are 10 blocks maximum)

 

I don't mind if I have to change the formatting a little etc. But I'd just like to know if this would actually be possible or if I will hit a barrier that will prevent it from working. Could anyone please tell me if this is possible or would it be a waste of time to try?

 

I've attached an example .dwg and excel file for reference.

Thank you in advance for any help.

Cad Blocks.dwg

[Lee Mac]

Yes, this is possible; though, you'll find it easier using a .csv/.txt file (which can be read using standard AutoLISP IO functions), in lieu of an .xls/.xlsx file (for which you'll need to use ActiveX(COM) to interface with the installed Excel application). Once the data has been acquired however, updating the block attributes is straightforward.

[swanny89]

@Lee Mac

Ok fantastic that's great news thank you. The problem is that this data will ALWAYS be held and monitored in an excel spreadsheet. The company uses this method as a standard and it will never change, I'm trying to gather the data without changing any procedures as this would invoke carnage. 

I've got the code to read from and write to the blocks working fine. As the next step, I'm trying to simply read a cell value from Excel, but it keeps throwing "error: bad argument type: VLA-OBJECT nil" and I don't have the experience to figure out why (I'm new to LISP). This is the code I have. Are you able to give me a nudge in the right direction? Or provide some sample code that will let me read a cell value?

Many thanks;

 

(defun c:FindExcelValue ( / fname xlApp xlBook xlSheet searchStr foundVal row col cellVal)
  ;; Prompt for Excel file path
  (setq fname (getfiled "Select Excel file" "" "xls;xlsx" 0))
  (if fname
    (progn
      ;; Ask for search string
      (setq searchStr (getstring T "\nEnter string to search for: "))

      ;; Start Excel
      (setq xlApp (vlax-get-or-create-object "Excel.Application"))
      (setq xlBook (vlax-invoke-method (vlax-get-property xlApp 'Workbooks) 'Open fname))
      (setq xlSheet (vlax-get-property xlBook 'ActiveSheet))

      ;; Assume search in column A (col = 1)
      (setq row 1
            col 1
            foundVal nil)

      ;; Loop until empty cell
      (while (and (not foundVal)
                  (setq cellVal (vlax-get-property (vlax-get-property xlSheet 'Cells) 'Item row col))
                  (/= (vlax-get-property cellVal 'Value) nil))
        (if (= (strcase (vlax-get-property cellVal 'Value)) (strcase searchStr))
          (setq foundVal (vlax-get-property (vlax-get-property xlSheet 'Cells) 'Item row (1+ col)) 'Value))
        (setq row (1+ row))
      )

      ;; Print result
      (if foundVal
        (princ (strcat "\nFound value: " (vl-princ-to-string foundVal)))
        (princ "\nString not found.")
      )

      ;; Clean up
      (vlax-invoke-method xlBook 'Close :vlax-false)
      (vlax-release-object xlSheet)
      (vlax-release-object xlBook)
      (vlax-release-object xlApp)
    )
  )
  (princ)
)

 

[Danielm103]

I have an app on the store, XLSX Field Evaluator

https://apps.autodesk.com/ACD/en/Detail/Index?id=4867035866745163447&appLang=en&os=Win64

 

You can add fields to block attributes that are linked to excel

There’s trial version

https://apps.autodesk.com/ACD/en/Detail/Index?id=4446191126131749248&appLang=en&os=Win64

 

it never expires, capped at 20 fields though

 

[Steven P]

BigAl is pretty good with Excel - got to wait for him to wake up - he's got some stuff stashed away that might also do the trick, also look in 'downloads' at the top here, Alan Excel might do what you want with the excel part.

[swanny89]

@Danielm103

Thank you for this, it looks really good. The problem is that I'm building this for people that are exceptionally resistant to change, and really I need a standalone lisp that can just be launched from within autocad and handles everything. If there is any more complexity to it than that, then it simply wont even be considered for adoption

@Steven P

Thanks for the info, I'll take a look this evening once I've got my actual work out of the way for the day

Edited 9 hours ago by swanny89

[SLW210]

Are you using AutoCAD LT 2025?

 

 

[swanny89]

I have access to AutoCAD 2025 and AutoCAD LT 2025, however I'm trying to build it in a manner where it functions in LT as thats what most of the guys use

[Steven P]

Might be tricker in LT - not every function works

 

 

Resistant to change... just show them it is quicker, subtle ways like doing everything and grabbing a coffee while they are still piling through it the 'old' way

[Danielm103]

Yeah, LT is a problem, you won’t have vlax-get-or-create-object,  you could probably parse .CSV files though

[swanny89]

That's unfortunate

@Steven P haha if only it were this simple...Honestly I've never experienced resistance like this before!

Could a LISP in LT export the Excel data to a CSV, pull the data from that, and the delete the CSV? I'm going to assume that the answer is no due to the lack of vlax-get-or-create-object.

[swanny89]

I think I've achieved a workaround. I'm going to set a macro within excel that exports the data to a CSV every time the excel document is saved (overwriting the existing versions). I'm then going to read this using the standard IO functions available to me in LT. It's not the cleanest solution, but I believe it should work.

If anyone has any advice regarding pitfalls etc. that I might encounter please feel free to enlighten me