MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Who has the file open?

  Asked By: Keana    Date: Dec 08    Category: MS Office    Views: 623

My code opens files using the Workbooks.Open command. If the target
file is busy, I'd like to determine the name of the user in VBA so it
can be reported. Is this possible?



1 Answer Found

Answer #1    Answered By: Eloise Lawrence     Answered On: Dec 08

I assume multiple users could access this file  it must be on a network
server. There are a couple things I have done to track user:

1. It should be possible to put your code  in the autoopen sub to get
the user's id and machine id, etc. then write that information to a
text file somewhere so you could look at it anytime you wish.

I use vbscript to quickly create the report file like this:

'******* watch out for word wrap & missing line breaks **************
Function create_Report(strRepFullName As String, strHeader As String, _
strColumnHeader As String, ByVal intCnt As
Integer, strMainList As String)
Dim FSO, a, os

'create a temp file for the report
Set FSO = CreateObject("Scripting.FileSystemObject")

Set a = FSO.CreateTextFile(strRepFullName, True)

a.WriteLine strHeader
a.WriteLine Format(Now, "mmmm d, yyyy") & "; " & Format(Now,
"h:mm ampm")
a.WriteLine ""
a.WriteLine "Total sites found: " & intCnt
a.WriteLine ""
a.WriteLine strColumnHeader
a.WriteLine strMainList

Set FSO = Nothing
Set a = Nothing

End Function

'use this fn to open  the report
Function OpenReport(strFullFileName as string)
Set os = CreateObject("WScript.Shell")
os.Run "Notepad " & strFullFileName
Set os = Nothing
End Function

2. Same information as above, but write them to the file properties.
Then just write a small vbscript to read the file attributes.

Here's the vbscript I wrote to read an xl file properties...

'******* watch out for word wrap & missing line breaks **************
'VBScript file to get the author and comments
'Tested with Excel 2000, XP
'All rights reserved by Tou X. Yang
' www.modernfalconry.com

Option Explicit

Dim objExcel, xlFile, strAuthor, strComments, xlProp, x, tempProp

Set objExcel = CreateObject("Excel.Application") 'Open an instance of

'Open a Excel document and return the "Documents" object
'set xlFile = objExcel.GetOpenFilename("Excel File (*.xls), *.xls")
' "C:\Documents and Settings\DOTTXY\Desktop\Copy of 76100005

'objExcel.Workbooks.open strPathExcel

set xlFile = objExcel.Workbooks.Open(_
"F:\Dad's Stuff\Hawk Migration\dev
set xlProp = xlFile.BuiltInDocumentProperties
for x = 0 to xlProp.count
err.clear: on error resume next
tempProp = x & ". " & xlFile.BuiltInDocumentProperties(x)
'strComments =
objExcel.ActiveWorkbook.BuiltInDocumentProperties(5) 'wdPropertyComments
if err=0 then _
strAuthor = strAuthor & tempProp & vbcrlf
'MsgBox "This document's author is " & strAuthor & vbCrLf &" Comments:
" & strComments
msgbox strAuthor
'msgbox objExcel.Version
tempProp= xlFile.BuiltInDocumentProperties(18)
xlFile.BuiltInDocumentProperties(18) = "test changing ..." & tempProp
'To avoid the "Do you want to save your changes?" dialog box, the script
'sets the Saved property to TRUE before terminating Excel.
xlFile.Saved = TRUE

set objExcel= nothing

Didn't find what you were looking for? Find more on Who has the file open? Or get search suggestion and latest updates.