Check if excel file is open 1

[mharcourt]

Need some help to determine if an excel spreadsheet is open. If you attempt to open a spreadsheet (outside of a script) you'll receive a notice that the spreadsheet is open and have a choice to open a read only copy. I would like to offer the similar functionality in a vbscript.

Basically, the spreadsheet is used to input data and the script is used to process the data and write results to the spreadsheet. I want to control the use of the spreadsheet so if someone is running the script and if another user attempts to run the script they we'll be advised that the spreadsheet is in use and to try later.

Any help would be greatly appreciated.

Thanks

 

[mharcourt]

found this script - works like a charm
Set objServerObject = GetObject("WinNT://PUT SERVERNAME HERE/LanmanServer")
If (IsEmpty(objServerObject) = False) Then
iCounter = 0
For Each objresource In objServerObject.resources
If (Not objresource.User = "") And (Not Right(objresource.User,1) = "$") Then
iCounter = iCounter + 1
WScript.echo iCounter & " -> " & objresource.user & " " & objresource.path
On Error Resume Next
End If
Next
Else
WScript.Echo "Object Empty"
End If

 

[mharcourt]

I found another solution because of a slight problem. If the users' creditials, running the script, does not have permissions to return any information then there is a possibility of a problem. However, there is another solution within Excel. Here it is:
'Open the workbook (.xls) file
Set objBook = objApp.Workbooks.Open(<path>\<name>)
'Check if active workbook is in read-only mode
If objBook.ReadOnly Then
WSH.Echo "spreadsheet is in Read ONLY mode"
Else
WSH.Echo "spreadsheet is not in Read ONLY mode"
End If

 

[infinitelo]

Cool

objbook.readonly

i run a script weekly to load a spreadsheet, sometimes, the company owner is looking at it
when i try to run.
It would be nice if i could
open in notify mode if readonly..... and
have the script wait until he is done.

mharcort, do you happen to know how????



if it is to be it's up to me

 

[mharcourt]

There are additional parameters you can add to the open command.
Set objApp = CreateObject("Excel.Application")
Set objBook = objApp.Workbooks.open(filename,Notify)

I have not tested this so be sure to run thru the mill.
All the available parameters are:
UpdateLinks,ReadOnly,Format,Password,WriteResPassword, IgnoreReadOnlyRecommended,Origin,Delimiter,Editable, Notify,Converter,AddToMru,Local, and CorruptLoad

 

[infinitelo]

thanks,for the help, as soon i get ahead, ill play with
the notify option.



if it is to be it's up to me