CLOSE ALL EXCEL SPREADSHEETS TO CONTINUE

Andrew
Andrew
in LISP Codes
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

Comments

  • Piet
    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
    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
    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
    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
    ALANH
    edited June 2022
    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
    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
    Andrew
    this is the lisp I have been using
  • ALANH
    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
    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
    ALANH
    That was the bug I have seen I think the couple of lines I posted helps.
  • Andrew
    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
    ALANH
    The questions after the code is what is current excel state and the code needs to take it into account.
  • Andrew
    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
    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
    Andrew
    Awesome , thanks, I will give this a try.
    Much appreciated.
  • Andrew
    Andrew
    edited July 2022
    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
    Its_Alive
    Hi, did you play with these properties?

    Application.Interactive = False
    Application.DisplayAlerts = False
  • Andrew
    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
    Its_Alive
    edited July 2022
    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
    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
    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
    martin@enco.us
    I can't help directly with Excel, but "vlax-puy-property" probably should have been "vlax-put-property".
  • ALANH
    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.