CLOSE ALL EXCEL SPREADSHEETS TO CONTINUE

Andrew

Hi
I am using an Autocad Lisp to update multiple Titleblock Revision and Change Numbers.
It works OK except this message appears at every drawing that updates and I have to click OK
"CLOSE ALL EXCEL SPREADSHEETS TO CONTINUE"
Do I need to change the Lisp so this will close automatically ?
Thanks

Piet

Hi Andrew,
it is hard to give feedback without seeing the script or at least pars of it. Do you open the excel file during the script? Where is the data written? In a table? Then you might consider using a datalink between table and excel and you would not need a script.

Andrew

Thanks for your reply Piet.
Yes , the excel is opened during the script.
I did not write the lisp.
The lisp works perfectly in Autocad , only in Bricscad does this message appear

ALANH

Is it Getexcel.lsp your using ? I wrote my own excel functions to avoid this type of error message, checking what is open or which worksheet is being used. Its not really a debugged package at this stage needs some more work and testing.

Andrew

Hi AlanH ,
Appreciate your reply , yes , the lisp is "Getexcel.lsp" , release 4.
Any chance of getting a copy of your updated Lisp please ?

ALANH

My version of excel.lsp uses some of the stuff out of getexcel, there were a couple of things I did not like so wrote my own getcell. Includes the great functions by Gile.

I added get a selected range handy when asking user to select a range. You do an alert asking user to select range in excel then press Ok in Acad.

I have attached it but it is a work in progress, you have to appreciate it needs the functions to be done in correct order depending on circumstances.

There are a few others also playing with excel functions. Look at this one also by denon detering excel functions. alx.zip

Andrew

Thanks very much again.
I haven't had much experience at lisps, and the one I am using was created by someone else.
Their response was to get rid of Bricscad and buy Autocad as thee is no issue when run in Autocad.
Thanks for the lisps , I will have a look and see what happens.

Andrew

this is the lisp I have been using

ALANH

I use Bricscad for majority of programming so not sure why your getexcel is not working properly.

If you explain more what it is your trying to do I can look into testing with my or Denton version. I have Denton contact email. I know the excel is open was a problem with getexcel I thought in Acad as well.

The check in the code open already I thought was here.

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

Explain excel open Y or N
Open excel file ?
Just use current ?
Open a file but is it open already

Need to look at current circumstances. Just a few of the questions.

Andrew

Thanks Alan ,
Yes , its strange how it works in Autocad but not Bricscad.
The function of the Lisp is to open each drawing sheet that I load into ScriptPro ,and using the associated excel spreadsheet, updates the Revision and Change Number in the Titleblock.
The problem is each drawing that opens , I have to click on a dialogue box "close all excel spreadsheets to continue". When run in Autocad , this does not happen.
Thanks
Andrew

ALANH

That was the bug I have seen I think the couple of lines I posted helps.

Andrew

thanks , you mean these lines ?

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

Explain excel open Y or N
Open excel file ?
Just use current ?
Open a file but is it open already

ALANH

The questions after the code is what is current excel state and the code needs to take it into account.

Andrew

OK , Thanks , writing Lisp codes is not my thing.
is there any tutorials you know of that could help me correct the Lisp please ?
I could try myself , do i need to somehow add a line telling the Excel to close ?
After this line - (setq myxl (vlax-get-or-create-object "excel.Application")) ?

ALANH

You would need say 3 close types, Save, Saveas & Quit. That is why I started to write my own defuns. The AH:ex is a variable about save etc.

(defun closeexcel ( / )
(if (= ah:ex "T")
(vlax-invoke-method (vlax-get-property myxl "ActiveWorkbook") 'Close :vlax-False)
(vlax-invoke-method (vlax-get-property myxl "ActiveWorkbook") "SaveAs" filename -4143 "" "" :vlax-false :vlax-false nil )
)

; (vlax-invoke-method myxl 'Quit)

(if (not (vlax-object-released-p myRange))(progn(vlax-release-object myRange)(setq myRange nil)))
(if (not (vlax-object-released-p mySheet))(progn(vlax-release-object mySheet)(setq mySheet nil)))
(if (not (vlax-object-released-p myBook))(progn(vlax-release-object myBook)(setq myBook nil)))
(if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))
)

Andrew

Awesome , thanks, I will give this a try.
Much appreciated.

Andrew

Hi
I added your lisp to the lisp like below but still same close excel alert appears -

; CloseExcel - Closes Excel spreadsheet
; Arguments: 1
; ExcelFile$ = Excel saveas filename or nil to close without saving
; Syntax examples:
; (CloseExcel "C:\\Temp\\Temp.xls") = Saveas C:\Temp\Temp.xls and close
; (CloseExcel nil) = Close without saving
;-------------------------------------------------------------------------------
(defun closeexcel ( / )
(if (= ah:ex "T")
(vlax-invoke-method (vlax-get-property myxl "ActiveWorkbook") 'Close :vlax-False)
(vlax-invoke-method (vlax-get-property myxl "ActiveWorkbook") "SaveAs" filename -4143 "" "" :vlax-false :vlax-false nil )
)

; (vlax-invoke-method myxl 'Quit)

(if (not (vlax-object-released-p myRange))(progn(vlax-release-object myRange)(setq myRange nil)))
(if (not (vlax-object-released-p mySheet))(progn(vlax-release-object mySheet)(setq mySheet nil)))
(if (not (vlax-object-released-p myBook))(progn(vlax-release-object myBook)(setq myBook nil)))
(if (not (vlax-object-released-p myXL))(progn(vlax-release-object myXL)(setq myXL nil)))
)
);defun CloseExcel

Its_Alive

Hi, did you play with these properties?

Application.Interactive = False
Application.DisplayAlerts = False

Andrew

Hi Daniel
Thanks for your comment
I haven't played with any properties.
I cant find these lines in the lisp -
Application.Interactive = False
Application.DisplayAlerts = False
Should I add these in ?
Thanks

Its_Alive

Andrew said:

Hi Daniel
Thanks for your comment
I haven't played with any properties.
I cant find these lines in the lisp -
Application.Interactive = False
Application.DisplayAlerts = False
Should I add these in ?
Thanks

Hi, they are properties of the Excel.Application object, you would set these on opening the excel object. I totally forgot my lisp but maybe something like (vlax-puy-property myxl "Interactive" vlax:false)

Andrew

Thanks Daniel
I don't know anything about Lisp code , I appreciate your assistance , but I think I will just have to put up with the way it is in Bricscad

Andrew

when you say "you would set these on opening the excel object" , would you add -

(vlax-puy-property myxl "Interactive" vlax:false)

to the "OpenExcel" part of the lisp ? Ive copied this below -

(defun OpenExcel (ExcelFile$ SheetName$ Visible / Sheet$ Sheets@ Worksheet)
(if (= (type ExcelFile$) 'STR)
(if (findfile ExcelFile$)
(setq *ExcelFile$ ExcelFile$)
(progn
(alert (strcat "Excel file " ExcelFile$ " not found."))
(exit)
);progn
);if
(setq *ExcelFile$ "")
);if
(gc)
(if (setq *ExcelApp% (vlax-get-object "Excel.Application"))
(progn
(alert "Close all Excel spreadsheets to continue!")
(vlax-release-object *ExcelApp%)(gc)
);progn
);if
(setq *ExcelApp% (vlax-get-or-create-object "Excel.Application"))
(if ExcelFile$
(if (findfile ExcelFile$)
(vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Open ExcelFile$)
(vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
);if
(vlax-invoke-method (vlax-get-property *ExcelApp% 'WorkBooks) 'Add)
);if
(if Visible
(vla-put-visible *ExcelApp% :vlax-true)
);if
(if (= (type SheetName$) 'STR)
(progn
(vlax-for Sheet$ (vlax-get-property *ExcelApp% "Sheets")
(setq Sheets@ (append Sheets@ (list (vlax-get-property Sheet$ "Name"))))
);vlax-for
(if (member SheetName$ Sheets@)
(vlax-for Worksheet (vlax-get-property *ExcelApp% "Sheets")
(if (= (vlax-get-property Worksheet "Name") SheetName$)
(vlax-invoke-method Worksheet "Activate")
);if
);vlax-for
(vlax-put-property (vlax-invoke-method (vlax-get-property *ExcelApp% "Sheets") "Add") "Name" SheetName$)
);if
);progn
);if
(princ)
);defun OpenExcel

martin@enco.us

I can't help directly with Excel, but "vlax-puy-property" probably should have been "vlax-put-property".

ALANH

the reason I started again rather than getexcel is this
OpenExcel (ExcelFile$ SheetName$ Visible
It is say one method of opening or checking is excel open, it asks is excel open and current or required file open.

So as I said earlier need more than 1 method when creating link to excel then error should not appear.

Is excel open No Start a new excel
Is my file name in the open sheets Yes / No
Yes /no if yes then set current
Yes /no if no then open the file in excel.

That is why maybe need more than 1 Open function. Will try to go back to my version.