Lisp routine to auto update title blocks.

[cforrest2AMQW]

Problem:

• We're attempting to automate title block updates in AutoCAD drawings using Lisp, by extracting attribute data from an Excel spreadsheet.
• The Lisp code successfully creates an Excel Application COM object using (vlax-get-or-create-object "Excel.Application").
• However, the code consistently fails when attempting to open an Excel workbook using (vlax-invoke excelApp 'Workbooks 'Open formattedFilePath), resulting in a "Member not found" error.

Troubleshooting Steps Taken:

• Verified Excel file path and file existence.
• Tested with simple, empty Excel files.
• Tested on multiple computers on the same network, with the same result.
• Verified that AutoCAD and Excel are both 64-bit.
• Ran AutoCAD as administrator.
• Repaired and reinstalled Microsoft 365.
• Tested general COM object creation with (vlax-get-or-create-object "Scripting.FileSystemObject") which succeeded.
• Tested excel COM object creation outside of the main function, which succeeded.
• Added a delay to the code, to rule out timing issues.
• Temporarily disabled excel protected view.
• We have not found any relevant excel related entries in DCOMCNFG.
• We have not found any relevant excel related entries in the windows registry.

Key Observations:

• AutoCAD can create general COM objects.
• AutoCAD can create excel COM objects in a simple test function.
• The failure is isolated to the (vlax-invoke excelApp 'Workbooks 'Open formattedFilePath) line.
• The problem persists across different computers on the same network.
• We are using Microsoft 365.

Request for Assistance:

• We are seeking assistance in identifying potential causes for this persistent "Member not found" error.
• We are particularly interested in any AutoCAD-specific settings, configurations, or security restrictions that might be affecting COM object interactions with Excel.
• We are also interested in any obscure excel settings, or windows settings that could be causing the issue.
• We are looking for any known compatibility issues between autocad, and microsoft 365.

Additional Information:

• We are working in a corporate network environment.
• We are working with our IT department.
  
  

The code in question is currently sitting like this:

(defun c:TestExcelOpen (/ excelApp workbook formattedFilePath excelFile)
(vl-load-com)

(setq excelFile (getfiled "Select Excel File" "" "xls;xlsx" 8))
(if (not excelFile) (progn (princ "\nNo file selected. Exiting.") (exit)))

(setq excelApp (vlax-get-or-create-object "Excel.Application"))
(if (not excelApp)
(progn
(princ "\nERROR: Could not start Excel Application.")
(exit)
)
)

(princ "\nExcel App Created. Delaying for 1 second...")
(command "_DELAY" 1000) ; Delay for 1 second

(setq formattedFilePath (vl-string-translate "\\" "/" (strcat "\"" excelFile "\"")))
(princ (strcat "\nFormatted Excel File Path: " formattedFilePath))

(setq workbook (vlax-invoke excelApp 'Workbooks 'Open formattedFilePath))
(if (not workbook)
(princ "\nError opening workbook")
)
(vlax-release-object excelApp)
(princ)
)

Full lisp routine for final goal attached. 

[CodeDing]

@cforrest2AMQW ,

This should help you:

;; Creates Excel Workbook object
;; xlFile - string, of path to existing Excel file
;; returns - vla-object, of Excel workbook object, else nil if error
(defun OpenExcel (xlFile / oXL)
  (setq xlFile (findfile xlFile)) ;double check file exists at location
  (if (and xlFile (setq oXL (vlax-get-or-create-object "Excel.Application")))
    (progn
      ;| ;; Optional Settings, these will display/open the Excel application
      (vla-put-visible oXL :vlax-true) ;; display application
      (vlax-put-property oXL 'DisplayAlerts :vlax-true) ;; display Excel alerts
      |;
      (setq oWB (vl-catch-all-apply 'vla-open (list (vlax-get-property oXL "WorkBooks") xlFile)))
      (if (null (vl-catch-all-error-p oWB))
        oWB
        (prompt (strcat "\nError opening Workbook: " xlFile))
      );if
    );progn
  ;else
    (prompt "\nError opening Excel.")
  );if
);defun

Best,

~DD

[Sea-Haven]

Using the vlax-get-or-create-object will detect a Excel is already open so maybe check is it the one you want, you can get workbook name. If not then open the correct one. 

(vlax-get-property (VLAX-get-property myxl "ActiveWorkbook") 'name)

[daniel_cadext]

Shameless plug, just use this

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

insert the cell value as a field in your titleblock

 

[ec-cad]

Using your Lisp file: UpdateTitleBlocks.lsp

; A line at a time shows some errors.
;
Command: (setq excelFile (getfiled "Select Excel File" "" "xls;xlsx" 8))
"C:\\Disk8\\Book1.xlsx"
Command: (setq excelApp (vlax-get-or-create-object "Excel.Application"))
#<VLA-OBJECT _Application 00000206ae2db158>
Command: (princ (strcat "\nExcel App Object: " (vlax-get-object-name excelApp)))
Error: no function definition: VLAX-GET-OBJECT-NAME <-----------------
Error: Break...
Command: (setq workbook (vlax-invoke excelApp 'Workbooks 'Open excelFile))
Error: Member not found <----------------------
Error: Break...

ECCAD

I don't know a work-around.

[ec-cad]

You could try another approach. Say you do an Excel sheet with just (3) Columns.

Blockname, tagname, value   - then save as a .csv file.

In your Lisp, just select the .csv for input check blockname, get tagname, and set

the textstring to value ..

ECCAD