Topic: WhoHas (open) for Excel files or other types

[Marc'Antonio Alessi]

Does something like this exist for other types of files (not DWG)? Example for Excel files?

Code: [Select]

; Credits... ?
;
; Description:
;   return info like WhoHas command
;
; Arguments:
;   PatNam: fully qualified file name
;
; Return Values: LIST
;   (
;    "User"                              ; > UserName
;    "PC2 "                              ; > Computer Name
;    "marted́ 8 novembre 2005  16.10.45" ; > date-time
;   )
;
; Example:
; (setq filename (strcat (getvar "dwgprefix")  (getvar "dwgname")))
; (ALE_UtlFileWhoHas filename)
;  > ("User" "PC2 " "marted́ 8 novembre 2005  16.10.45")
;
;
(defun ALE_UtlFileWhoHas (PatNam / FilPtr TmpStr OutLst)
  (if (findfile PatNam)
    (progn
      (if (setq FilPtr (open (strcat (vl-filename-directory PatNam) "\\" (vl-filename-base PatNam) ".DWL") "r"))
        (progn
          (while (setq TmpStr (read-line FilPtr))
            (setq OutLst (cons TmpStr OutLst))
          )
          (close FilPtr)
          (reverse OutLst)
        )
      )
    )
  )
)

[rkmcswain]

Excel and most other apps do not write files like AutoCAD's .DWL/.DWL2 files.
You can easily see all files that are open, and by whom, by looking on the server:

[Marc'Antonio Alessi]

Excel and most other apps do not write files like AutoCAD's .DWL/.DWL2 files.
You can easily see all files that are open, and by whom, by looking on the server:

Thanks, I need a solution also for files opened on "My Computer"... 

[rkmcswain]

You have the same thing on your local PC, presuming you're running a profession version of Win7 or Win10

[Marc'Antonio Alessi]

You have the same thing on your local PC, presuming you're running a profession version of Win7 or Win10

I have tested on Win10 Pro/Home but I do not see Open Files:

[ronjonp]

Try this .. does not tell you who has it open though. Since the files are on your computer I'd imagine you already know that

Code - Auto/Visual Lisp: [Select]

1. (defun _isfileopen (file / o r sf)
2.   ;; RJP - 2.1.2018
3.   ;; Idea translated from here: https://goo.gl/gK3UuX
4.   ;; Use with caution NOT heavily tested
5.   (if (and (findfile file) (setq sf (vlax-get-or-create-object "Scripting.FileSystemObject")))
6.     (progn (setq r (vl-catch-all-error-p
7.                      (setq o (vl-catch-all-apply 'vlax-invoke (list sf 'opentextfile file 8 0)))
8.                    )
9.            )
10.            (or r (vl-catch-all-apply 'vlax-invoke (list o 'close)))
11.            (and sf (vlax-release-object sf))
12.     )
13.   )
14.   r
15. );; (_isfileopen "C:\\myfile.xlsx")

[Marc'Antonio Alessi]

Try this .. does not tell you who has it open though. Since the files are on your computer I'd imagine you already know that

I've used this for many years and I realized now that vl-file-systime in Bricscad does not return NIL if the file is open. > ALE_UtlFileWhoHas only works with DWG ...

Code: [Select]

(defun ALE_Files_OpenP (InpFil / Countr FlgOpn WhoInf WhoStr)
  (setq Countr 5)
  (cond
    ( (vl-file-systime InpFil) nil )  ;                                > return nil
    ( (logand 1 (nth 3 (DOS_FILE InpFil))) ); if the file is read only > return T
    ( (while (not FlgOpn)
        (cond
          ( (or (vl-file-systime InpFil) (zerop Countr)) (setq FlgOpn T) )
          ( T
            (if (setq WhoInf (ALE_UtlFileWhoHas InpFil))
              (setq WhoStr (strcat "\n\nFile open by:  "   (car WhoInf) " " (cadr WhoInf) " " (caddr WhoInf)))
              (setq WhoStr "")
            )
            (alert
              (strcat
                "Attention, the file " InpFil " is already open for writing."   
                "\n\n" (itoa Countr) " more attempts will be made to open it."
                "\nAfter that it will be ignored."
                 WhoStr
              )
            )
            (setq Countr (1- Countr))
          )
        )
      )
    )
    (zerop Countr) ; > return T if the file not been closed within the 5 attempts
  )
)

[Marc'Antonio Alessi]

Another:

Code: [Select]

(defun its_ok (file / dwg)
  (and (findfile file)
       (setq dwg (open file "a"))
    (not (close dwg))
  )
)

[ronjonp]

So do you care if the file is just open or not or actually who has it open?

[Marc'Antonio Alessi]

So do you care if the file is just open or not or actually who has it open?

The second: "or actually who has it open?"

I appreciate also your _isfileopen and maybe I modify my ALE_Files_OpenP with your solution (tested also in Bricscad).
 

[ronjonp]

So do you care if the file is just open or not or actually who has it open?

The second: "or actually who has it open?"

I appreciate also your _isfileopen and maybe I modify my ALE_Files_OpenP with your solution (tested also in Bricscad).
 

Glad you could maybe use the code .. I'm not sure how to accomplish the 'who has it open'. Everything I'm reading on the interwebs comes back to issues with security or lack of admin.

[Lee Mac]

Aside from using a generic method such as that suggested by RK above, it would depend on the filetype, for example Access database files (accdb) generate an associated .laccdb file when the database is open & locked (or partially locked) for writing - this file is a plain text format file which yields the PC name & Access username of the user who has the database open.

However, since most other filetypes are "single-user" files, I don't think they will generate their own accompanying lock files.

[Marc'Antonio Alessi]

Aside from using a generic method such as that suggested by RK above, it would depend on the filetype, for example Access database files (accdb) generate an associated .laccdb file when the database is open & locked (or partially locked) for writing - this file is a plain text format file which yields the PC name & Access username of the user who has the database open.

However, since most other filetypes are "single-user" files, I don't think they will generate their own accompanying lock files.

Right now I need only for Excel files but I thought I could do something generic.
When I open a "foo.xlsx" it creates "~$foo.xlsx" which is hidden and protected ... if I make a copy and look inside the file there is written the name of the user... see att.

[dgorsman]

I've got an odd question: how often is it that someone else has a local file open on your own computer?  That should be so rare as to have a "whohas" type function just returning the active user.

[Marc'Antonio Alessi]

I've got an odd question: how often is it that someone else has a local file open on your own computer?  That should be so rare as to have a "whohas" type function just returning the active user.

What is your solution to know who opened an Excel file on the net? (with lisp/visuallisp)

[Marc'Antonio Alessi]

Try this .. does not tell you who has it open though. Since the files are on your computer I'd imagine you already know that

Code - Auto/Visual Lisp: [Select]

1. (defun _isfileopen (file / o r sf)
2.   ;; RJP - 2.1.2018
3.   ;; Idea translated from here: https://goo.gl/gK3UuX
4.   ;; Use with caution NOT heavily tested
5.   (if (and (findfile file) (setq sf (vlax-get-or-create-object "Scripting.FileSystemObject")))
6.     (progn (setq r (vl-catch-all-error-p
7.                      (setq o (vl-catch-all-apply 'vlax-invoke (list sf 'opentextfile file 8 0)))
8.                    )
9.            )
10.            (vl-catch-all-apply 'vlax-invoke (list o 'close))
11.            (and sf (vlax-release-object sf))
12.     )
13.   )
14.   r
15. )
16. ;; (_isfileopen "C:\\myfile.xlsx")

Code: [Select]

Comando: (setq SFsObj (vlax-get-or-create-object "Scripting.FileSystemObject"))
#<VLA-OBJECT IFileSystem3 000000000bd0eed0>

Comando: (setq FilObj (vl-catch-all-apply 'vlax-invoke (list SFsObj 'opentextfile "C:\\Asso\\assoita.xlsx" 8 0)))
#<%catch-all-apply-error%>

Comando: (setq ErrFlg (vl-catch-all-error-p FilObj))
T

Comando: !FilObj
#<%catch-all-apply-error%>

Comando: (vl-catch-all-apply 'vlax-invoke (list FilObj 'close))         
#<%catch-all-apply-error%>                                                     <<<<<<<<<<<<<<<<<

Comando: (and SFsObj (vlax-release-object SFsObj))
T
Is this line >>> (vl-catch-all-apply 'vlax-invoke (list FilObj 'close)) better in this form if the file is open:
(or r (vl-catch-all-apply 'vlax-invoke (list o 'close)))

[ChrisCarlson]

I've got an odd question: how often is it that someone else has a local file open on your own computer?  That should be so rare as to have a "whohas" type function just returning the active user.

What is your solution to know who opened an Excel file on the net? (with lisp/visuallisp)

I don't believe Excel will directly release that information. You can check if its open (read-only) or closed (read/write) but to return the user would require domain information.

[ronjonp]

Try this .. does not tell you who has it open though. Since the files are on your computer I'd imagine you already know that

Code - Auto/Visual Lisp: [Select]

1. (defun _isfileopen (file / o r sf)
2.   ;; RJP - 2.1.2018
3.   ;; Idea translated from here: https://goo.gl/gK3UuX
4.   ;; Use with caution NOT heavily tested
5.   (if (and (findfile file) (setq sf (vlax-get-or-create-object "Scripting.FileSystemObject")))
6.     (progn (setq r (vl-catch-all-error-p
7.            (setq o (vl-catch-all-apply 'vlax-invoke (list sf 'opentextfile file 8 0)))
8.          )
9.       )
10.       (vl-catch-all-apply 'vlax-invoke (list o 'close))
11.       (and sf (vlax-release-object sf))
12.     )
13.   )
14.   r
15. )
16. ;; (_isfileopen "C:\\myfile.xlsx")

Code: [Select]

Comando: (setq SFsObj (vlax-get-or-create-object "Scripting.FileSystemObject"))
#<VLA-OBJECT IFileSystem3 000000000bd0eed0>

Comando: (setq FilObj (vl-catch-all-apply 'vlax-invoke (list SFsObj 'opentextfile "C:\\Asso\\assoita.xlsx" 8 0)))
#<%catch-all-apply-error%>

Comando: (setq ErrFlg (vl-catch-all-error-p FilObj))
T

Comando: !FilObj
#<%catch-all-apply-error%>

Comando: (vl-catch-all-apply 'vlax-invoke (list FilObj 'close))         
#<%catch-all-apply-error%>                                                     <<<<<<<<<<<<<<<<<

Comando: (and SFsObj (vlax-release-object SFsObj))
T
Is this line >>> (vl-catch-all-apply 'vlax-invoke (list FilObj 'close)) better in this form if the file is open:
(or r (vl-catch-all-apply 'vlax-invoke (list o 'close)))

I would agree with that .. my logic was a little flawed    .. I'll update my earlier post.

[Marc'Antonio Alessi]

I've got an odd question: how often is it that someone else has a local file open on your own computer?  That should be so rare as to have a "whohas" type function just returning the active user.

What is your solution to know who opened an Excel file on the net? (with lisp/visuallisp)

I don't believe Excel will directly release that information. You can check if its open (read-only) or closed (read/write) but to return the user would require domain information.

Did you open the file in my replay #12?
(setq FilPtr (open "z:\\Renamed__$test.xlsx" "r"))
(substr (read-line FilPtr) 2 20) =>> "Marco               "

[rkmcswain]

However, since most other filetypes are "single-user" files, I don't think they will generate their own accompanying lock files.

Slight correction, those are not "lock" files in the sense that the O/S uses them to determine if the file is in use.
At least with Windows.

[ChrisCarlson]

I've got an odd question: how often is it that someone else has a local file open on your own computer?  That should be so rare as to have a "whohas" type function just returning the active user.

What is your solution to know who opened an Excel file on the net? (with lisp/visuallisp)

I don't believe Excel will directly release that information. You can check if its open (read-only) or closed (read/write) but to return the user would require domain information.

Did you open the file in my replay #12?
(setq FilPtr (open "z:\\Renamed__$test.xlsx" "r"))
(substr (read-line FilPtr) 2 20) =>> "Marco               "

It returns jibberish on AutoCAD 2018 and Excel 2013

[Marc'Antonio Alessi]

I've got an odd question: how often is it that someone else has a local file open on your own computer?  That should be so rare as to have a "whohas" type function just returning the active user.

What is your solution to know who opened an Excel file on the net? (with lisp/visuallisp)

I don't believe Excel will directly release that information. You can check if its open (read-only) or closed (read/write) but to return the user would require domain information.

Did you open the file in my replay #12?
(setq FilPtr (open "z:\\Renamed__$test.xlsx" "r"))
(substr (read-line FilPtr) 2 20) =>> "Marco               "

It returns jibberish on AutoCAD 2018 and Excel 2013

OK, thanks for test. 

[Marc'Antonio Alessi]

In regard to the functions that investigate the status of a file open/closed, I tried three types of functions but I get very different results if the file is open or closed.
Why is _isfileopen much slower if the file is open?

Code: [Select]

(defun its_ok (file / dwg)
  (and (findfile file)
       (setq dwg (open file "a"))
    (not (close dwg))
  )
)
; Lee Mac
(defun _FileOpen-p ( filename )
  (not (vl-file-rename filename filename))
)
(defun _isfileopen (file / o r sf)
  ;; RJP - 2.1.2018
  ;; Idea translated from here: https://goo.gl/gK3UuX
  ;; Use with caution NOT heavily tested
  (if (and (findfile file) (setq sf (vlax-get-or-create-object "Scripting.FileSystemObject")))
    (progn (setq r (vl-catch-all-error-p
     (setq o (vl-catch-all-apply 'vlax-invoke (list sf 'opentextfile file 8 0)))
   )
   )
   (or r (vl-catch-all-apply 'vlax-invoke (list o 'close)))
   (and sf (vlax-release-object sf))
    )
  )
  r
)


Code: [Select]

FILE OPENED
Elapsed milliseconds / relative speed for 32768 iteration(s):
    (_FILEOPEN-P "z:\\temp\\LspTest.lsp")......1312 / 13.2 <fastest>
    (_FILEOPEN-P "z:\\temp\\LspTest.lsp")......1328 / 13.04
    (ITS_OK "z:\\temp\\LspTest.lsp")...........4281 / 4.04
    (ITS_OK "z:\\temp\\LspTest.lsp")...........4453 / 3.89
    (_ISFILEOPEN "z:\\temp\\LspTest.lsp").....17141 / 1.01
    (_ISFILEOPEN "z:\\temp\\LspTest.lsp").....17313 / 1 <slowest>


FILE CLOSED
Elapsed milliseconds / relative speed for 8192 iteration(s):
    (ITS_OK "z:\\temp\\LspTest.lsp")..........1406 / 2.08 <fastest>
    (ITS_OK "z:\\temp\\LspTest.lsp")..........1422 / 2.05
    (_ISFILEOPEN "z:\\temp\\LspTest.lsp").....1718 / 1.7
    (_ISFILEOPEN "z:\\temp\\LspTest.lsp").....1734 / 1.69
    (_FILEOPEN-P "z:\\temp\\LspTest.lsp").....2891 / 1.01
    (_FILEOPEN-P "z:\\temp\\LspTest.lsp").....2922 / 1 <slowest>


FILE OPENED
Elapsed milliseconds / relative speed for 32768 iteration(s):
    (_FILEOPEN-P "z:\\temp\\XlsTest.xlsx")......1328 / 13.06 <fastest>
    (_FILEOPEN-P "z:\\temp\\XlsTest.xlsx")......1359 / 12.76
    (ITS_OK "z:\\temp\\XlsTest.xlsx")...........4110 / 4.22
    (ITS_OK "z:\\temp\\XlsTest.xlsx")...........4156 / 4.17
    (_ISFILEOPEN "z:\\temp\\XlsTest.xlsx").....17281 / 1
    (_ISFILEOPEN "z:\\temp\\XlsTest.xlsx").....17344 / 1 <slowest>


FILE CLOSED
lapsed milliseconds / relative speed for 8192 iteration(s):
    (ITS_OK "z:\\temp\\XlsTest.xlsx")..........1390 / 2.07 <fastest>
    (ITS_OK "z:\\temp\\XlsTest.xlsx")..........1421 / 2.02
    (_ISFILEOPEN "z:\\temp\\XlsTest.xlsx").....1719 / 1.67
    (_ISFILEOPEN "z:\\temp\\XlsTest.xlsx").....1734 / 1.66
    (_FILEOPEN-P "z:\\temp\\XlsTest.xlsx").....2875 / 1
    (_FILEOPEN-P "z:\\temp\\XlsTest.xlsx").....2875 / 1 <slowest>