how test if shared file is already in use? 1

[rudo]

My VBA-programs use shared files for reading and writing.
When shared file is already in use, application stops, micosoft window appears with remark "file already in use" and question "would you like to open a read-only copy". I do not want users to be confronted to this message. Does anyone know the code to detect if shared file is already in use by someone-else?

Thank you very much in advance

 

[TonyU]

[tt]rudo, I'll post the code when I find it in my notes. But one way I do this is:

Make the folder where the documents reside read only making user save the file somewhere else.

[tt]"A Successful man is one who can build a firm foundation with the bricks that others throw at him"[/tt]

​

 

[ratman]

Application.DisplayAlerts = False
Workbooks.Open ("\\LOUISE\prodcont\LaborNew.xls&quot
If ActiveWorkbook.ReadOnly Then
'yourcode
End If

Although I have NEVER seen a shared file show that message you wrote: "file already in use" and question "would you like to open a read-only copy"....since that is the purpose of using a shared file.

It will alert you if the file was saved with changes made by another user.

 

[ratman]

I NEED TO FOLLOW UP ON THIS OLDER THREAD WITH THE CORRECT CODE:

Ratman's "Canada is on fire=sun is red" special of the day!

I'm not sure if it's a semantics issue here or if there's some confusion with file sharing because true file sharing in Excel was intended for Multi-User functionality.

Consider this....

This code determines if the workbook is:

1. Shared....if so are there more users than yourself ?...if so close it without any alerts or changes.

2. Unshared...as in exclusive access.....if another user has it opened it will return a readonly property.....if so then same close routine.

------------------------------------------------
Sub IsFileShareOrNotOrOpened()

ChDir "C:\Inventory Files"
'----validate path----
FileInQuestion = Dir("C:\Inventory Files\Inventory.xls&quot
If FileInQuestion <> &quot;&quot; Then

'----open file to test properties----
Workbooks.Open filename:=&quot;C:\Inventory Files\Inventory.xls&quot;
Workbooks(&quot;Inventory.xls&quot.Activate

'----handles shared workbooks----
If ActiveWorkbook.MultiUserEditing = True Then
'----if shared and other users have opened then close without save----
users = ActiveWorkbook.UserStatus
numofusers = UBound(users, 1)
If numofusers > 1 Then
GoTo closeit
End If
'shared code stuff here
GoTo closeit

'----handles unshared exclusive access workbooks----
End If
If ActiveWorkbook.ReadOnly Then
closeit:
Application.DisplayAlerts = False
Workbooks(&quot;Inventory.xls&quot.Close
End If
'exclusive access code goes here----
End If
endo:
End Sub
-----------------------------------------------------


If you need more help with this just reply....