How to release excel application from background running

[savinirsb4u]

Hi there,

can anyone try to help me to check with my below code. with my lisp code I am able to extract data from CAD to excel. But the problem is after creating the excel I need to close my drawing to see the contents in my excel. I thought I need to amend my script to Quit excel application which is running background. Please advise how to fix my code:

 

(defun gc:WriteExcel (filename sheet startRange dataList / *error* xlApp wBook save sheets active start row col rng n cell)

(defun *error* (msg)
(and msg
(/= msg "Function Cancelled")
(princ (strcat "\nError: " msg))
);and
(and wBook (vlax-invoke-method wBook 'Close :vlax-False))
(and xlApp (vlax-invoke-method xlApp 'Quit))
(and rng (vlax-release-object rng))
(mapcar (function (lambda (obj) (and obj (vlax-release-object obj))))(list cell rng wBook xlApp))
(gc)
);defun

(setq filepath (getfiled "Select BOMBOQ File" (getvar "dwgprefix") "xlsx;xlsm" 2))
(setq BOMBOQname (VL-FILENAME-Base filepath))
(setq dwgname (getvar "dwgname"))
(setq cdate_val (strcat "DDD\",\" DD MON YYYY - H:MMam/pm"))
(setq cdate(menucmd (strcat "m=$(edtime,$(getvar,DATE)," cdate_val ")")))
(setq filexls(strcat (getvar "dwgprefix")(vl-filename-base(getvar "dwgname")) "_Variation.xlsx"))
(setq variationreport(strcat (vl-filename-base(getvar "dwgname")) "_Variation.xlsx"))
(setq sheet "Variation")

(setq xlapp(vlax-get-or-create-object "Excel.Application"))

(if 
(findfile filename)
(setq wBook (vlax-invoke-method (vlax-get-property xlapp 'WorkBooks) 'Open filename)save  T)
(setq wBook (vlax-invoke-method (vlax-get-property xlapp 'WorkBooks) 'Add))
)

(if sheet
(progn
(setq sheets (vlax-get-property xlApp 'Sheets))
(vlax-for s sheets
(if 
(= (strcase (vlax-get-property s 'Name)) (strcase sheet))
(progn
(vlax-invoke-method s 'Activate)
(setq active T)
);vlax
);if
);progn
(or active
(vlax-put-property (vlax-invoke-method sheets 'Add) 'Name sheet)
);or
);progn
);if

(if startRange
(setq start (gc:ColumnRow startRange)
 col (car start)
 row (cadr start))
(setq col 8 row 7)
);if

(setq rng (vlax-get-property xlApp 'Cells))

(foreach sub dataList
(setq n col)
(foreach data sub
(setq cell (vlax-variant-value (vlax-get-property rng 'Item row n)))
(if 
(= (type data) 'STR)
(vlax-put-property cell 'NumberFormat "@")
);if
(vlax-put-property cell 'Value2 data)
(setq n (1+ n))
);foreach
(setq row (1+ row))
);foreach

(vlax-put-property rng "Item" 2 2 dwgname);drawing file name
(vlax-put-property rng "Item" 3 2 BOMBOQname);BOMBOQ name
(vlax-put-property rng "Item" 4 2 cdate);current date
(vlax-put-property rng "Item" 7 11 filepath);BOMBOQ file path

(vlax-invoke-method xlapp 'run "RunAll")

(vlax-invoke-method(vlax-get-property(vlax-get-property xlApp 'ActiveSheet)'Columns)'AutoFit)

(setq srisri(vlax-variant-value(vlax-get-property(vlax-variant-value (vlax-get-property rng 'Item 5 2))'Value)))

(vlax-put-property xlApp "DisplayAlerts" :vlax-false)

(if saveas
(vlax-invoke-method wBook 'Saveas)
(if 
(and
(< "11.0" (vlax-get-property xlapp "Version"))
(= (strcase (vl-filename-extension filename) T) ".xlsm")
);and
(vlax-invoke-method wBook 'SaveAs filexls 51 "" "" :vlax-false :vlax-false 1 1)
(vlax-invoke-method wBook 'SaveAs filexls -4143 "" "" :vlax-false :vlax-false 1 1)
);if
);if

(*error* nil)

);; gc:WriteExcel

[savinirsb4u]

Hi All, any advise on how to resolve my issue. 

Below code is causing issues in my script which activates excel macros in my excel. I am trying to fix my code but no luck yet.  Any ideas how to amend below code and  close excel from running background?

 

(vlax-invoke-method xlapp 'run "RunAll")

[pkenewell]

Have you looked into (vlax-release-object)? It's used in your error Handler.

 

Nevermind - I see you are calling *error* at the end of your routine to release all the objects. Sorry I can't help on the Excel app.

Edited January 6, 2021 by pkenewell

[Grrr]

Within the *error* function, try changing:

(and rng (vlax-release-object rng))
(mapcar (function (lambda (obj) (and obj (vlax-release-object obj))))(list cell rng wBook xlApp))
(gc)

To:

(foreach obj (list cell rng wBook xlApp)
  (and
    (eq 'VLA-OBJECT (type obj))
    (vl-catch-all-apply (function vlax-release-object) (list obj))
  )
)
(gc)(gc)

also include this:

(vlax-invoke-method xlApp 'EnableEvents :vlax-false)

after creating the excel application object

(setq xlapp(vlax-get-or-create-object "Excel.Application"))

 

[savinirsb4u]

Thank you of replying me Grrr. I am changed the code as per your suggestion but  I am getting below error. Please help me

 

Error: Automation Error DISP_E_MEMBERNOTFOUND; [_Application] method [ENABLEEVENTS] not found, or read-only, or wrong number of arguments.

[savinirsb4u]

Or is there any otherway to run excel macros from autocad instead of the vlax invoke method to avoid the background excel application? 

 

(vlax-invoke-method xlapp 'run "RunAll")

[Grrr]

On 1/5/2021 at 7:15 PM, savinirsb4u said:

But the problem is after creating the excel I need to close my drawing to see the contents in my excel.

 

On 1/6/2021 at 8:54 AM, savinirsb4u said:

Below code is causing issues in my script which activates excel macros in my excel.

 

11 hours ago, savinirsb4u said:

Or is there any otherway to run excel macros from autocad

 

I'm confused on what are exactly you are trying to do:

1. is it just running the lisp, writing some data into excel, dispose the background excel app and in the end open manually.

2. Or after writing the data to run some excel vba macros.

 

I've assumed the 1st approach with the scenario that you had (event?) macros with async calls, that prevent the functionality of the lisp.

 

11 hours ago, savinirsb4u said:

Or is there any otherway to run excel macros from autocad instead of the vlax invoke method to avoid the background excel application? 

 

If you intend to use VBA macros for excel from AutoCAD in any of the cases you'll need to obtain somehow the excel application object.

AutoCAD supports VBA, so you can load and run VBA macros from there, but you'll still need to reobtain the excel app obj and invoke its run method,

since you run VBA outside of the Excel program.

Third and much cleaner way would be to translate your VBA codes into lisp and perform all of the algorithm in there - this way you'll avoid asynchronous execution.

 

On 1/7/2021 at 2:38 AM, savinirsb4u said:

I am getting below error. Please help me

 

Error: Automation Error DISP_E_MEMBERNOTFOUND; [_Application] method [ENABLEEVENTS] not found, or read-only, or wrong number of arguments.

 

Sorry I'm unable to reproduce the error, since I don't have excel installed on my PC - but it looks to me that its read only or you should try providing nil argument instead of :vlax-false.

Also not sure of your intentions so you might not need it anyway.

[savinirsb4u]

Hi Grrr,

Sorry to say that I have tested your code in BricsCAD instead of AutoCAD. Probably that's the reason why I am getting that error.

I have updated my script with your suggestions and also I have updated my excel which has macros in it. Instead of using Vlax-invoke-method in my lisp, I have applied a trigger (worksheet_change event) in my excel which executes the macros whenever some particular cells are changed through my lisp.
Hence, my query is resolved.

Thank you very much for your assistance to update my code.

Have a great weekend

[Grrr]

1 hour ago, savinirsb4u said:

Hence, my query is resolved

 

Glad you sorted it out! 

 

1 hour ago, savinirsb4u said:

Thank you very much for your assistance to update my code.

Have a great weekend

 

You're wellcome, have a great weekend aswell!