Handling EXCEL files silently from a network location

[llorden4]

I've been attempting to read Excel files for data extraction of project data and just when I thought I had it all figured out along comes a new headache in the testing phase.  I've been testing the routines on my local drive and they've worked splendidly, but when I attempt to open a file from a network location then the silent features I thought were in place no longer work as Windows is now asking me if I wish to save the file before closing.  It's the exact same routine I'm using for my local, I just can't figure out why when it's a network location I suddenly need to verify a close without saving.  The goal is to open and read the data without any interaction from the user. Below are my Open and Close subroutines, anyone know what I'm missing?

I'd also like to know how to open the file as read-only if you know the coding for that as well; could score you some bonus points.

;--- Routine to OPEN an existing Excel file
;--- Call routine with path & filename (Example: OpenExcel "C:/Test.xlsx")
;--- File will be opened hidden
(defun OpenExcel (Exfile)
(setq MyFile (findfile Exfile)) ;double check file exists at location
(if Myfile ;nil = file not found
(progn ;if file found open it
(setq MyXL (vlax-get-or-create-object "Excel.Application")) ;find Excel application
(vla-put-visible MyXL :vlax-false) ;hide application from view
(vlax-put-property MyXL 'DisplayAlerts :vlax-false) ;hide Excel alerts
(setq MyBook (vl-catch-all-apply 'vla-open (list (vlax-get-property MyXL "WorkBooks") MyFile)))
))
) ;return - MyFile = nil if file not found

;--- Routine to CLOSE Excel file & Session
;--- Routine to CLOSE Excel file & Session
;--- Assumes previously opened with OpenExcel function
(defun CloseExcel (/)
(if Myfile (progn ;if filename still stored then...
(vl-catch-all-apply 'vlax-invoke-method (list MyBook "Close")) ;close the file
(vl-catch-all-apply 'vlax-invoke-method (list MyXL "Quit")) ;release the app
(setq MyFile nil MyXL nil MyBook nil MySheet nil MyRange nil MyTab nil MyCell nil ExCell nil)
(gc) ;garbage cleanup
))) ;return

[llorden4]

Some additional testing results have revealed that only Excel files with warnings upon open are causing this issue.  For example, if the Excel file has Macro's and you get that option to enable prior to continuing/editing yields in the undesired save on exit dialog box.  Files without such warnings are opening and closing without issue.

I will not be considering disabling security measures on a multitude of PC's to avoid this, surely there must be a means to ignore such features.

[llorden4 · Accepted solution]

Finally figured this one out so I'm back to share.  I thought by setting the DisplayAlerts to false would disable all alerts while the document was active, instead it appears that flag settings appears to be limited to the command being executed and its done, in this case opening the excel.  By adding the same disable alert flag with the CloseExcel sub-routine the files closed silently as desired.  The corrected codes are as follows, hope this helps someone:

;--- Routine to OPEN an existing Excel file
;--- Call routine with path & filename (Example: OpenExcel "C:/Test.xlsx")
;--- File will be opened hidden
(defun OpenExcel (Exfile)
(setq MyFile (findfile Exfile)) ;double check file exists at location
(if Myfile ;nil = file not found
(progn ;if file found open it
(setq MyXL (vlax-get-or-create-object "Excel.Application")) ;find Excel application
(vla-put-visible MyXL :vlax-false) ;hide application from view
(vlax-put-property MyXL 'DisplayAlerts :vlax-false) ;hide Excel alerts
(setq MyBook (vl-catch-all-apply 'vla-open (list (vlax-get-property MyXL "WorkBooks") MyFile)))
))
) ;return - MyFile = nil if file not found 


;--- Routine to CLOSE Excel file & Session
;--- Assumes previously opened with OpenExcel function
(defun CloseExcel (/)
(if Myfile (progn ;if filename still stored then...

(vlax-put-property MyXL 'DisplayAlerts :vlax-false) ;hide Excel alerts
(vl-catch-all-apply 'vlax-invoke-method (list MyBook "Close")) ;close the file
(vl-catch-all-apply 'vlax-invoke-method (list MyXL "Quit")) ;release the app
(setq MyFile nil MyXL nil MyBook nil MySheet nil MyRange nil MyTab nil MyCell nil ExCell nil)
(gc) ;garbage cleanup
))) ;return

[P.S.  No solution found yet for opening Read-Only]

[mid-awe]

Please accept my apology if you've already tried this for opening files Read-Only.

;;| FNA = File Name

(VLA-ACTIVATE (VLA-OPEN (VLA-GET-DOCUMENTS (VLAX-GET-ACAD-OBJECT)) FNA :VLAX-TRUE))

I can easily imagine that this method will need some adjustments for dealing with excel objects. Hope this shines a little light in a helpful way.

[llorden4]

I have tried some variations on that without success.  Your code looks like it's VBA, what I didn't say in my previous posts is that I'm using AutoLISP and that might be my limiting factor.  I did find a web post somewhere for opening .DWG files read only with ActiveX and it specifically noted that it could be done with VBA but not via Lisp; I suspect I might have the same limitation with other file types.  Great suggestion!

[john.uhden]

That's great!  All I could suggest was doing it after work when no one was there to hear or even notice you.  :]