how to close a workbook on network when its open by another user.

Hi All,

I actually need help on below codes for closing an open workbook on network.

Am using the following codes :

[vba]
FILE_NAME = FILE_TO_OPEN & ".xls"
Set WBOOK = Workbooks.Open("\\130.1.1.138\FACTORY_WIP\PROGRESS\" & FILE_NAME, ReadOnly:=False)
If WBOOK Is Nothing Then 'Not open
MsgBox "Workbook is not open"

Set WBOOK = Nothing
' On Error GoTo 0
Else
Workbooks.Item(FILE_NAME).Close SAVECHANGES:=False
Set WBOOK = Nothing
'On Error GoTo 0
End If
[/vba]
but the problem is when another user already has it open its giving me readonly or notify msg which i don't want.

can this be done that it do not ask the user for changes but it just close the workbook and continue the other procedure .

thanks for the help.

You are asking to force another user out of a workbook they are using? Bit arrogant isn't it?

yes it shall force the user out. No it not arrogant because the report is just for viewing purpose only.

Before it close it shall give the user a msg that report need to be updated as this is progress evolution for production then it closes for the data to be updated on that.

all these is being control by anaother workbook which is the Main. thanks for the advise.

If you need that kind of control, why don't you make the file Read Only when "you" close it? The savvy user can bypass that but then if you have that much power, you can reprimand them for it.

[VBA]Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
stName = GetBaseName(stName)
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then
Set Wkb = Nothing
IsWorkbookOpen = True
End If
'Boolean Function assumed To be False unless Set To True
End Function

Function LastUser(strPath As String) As String
'// Code by Helen from http://www.visualbasicforum.com/index.php?s=
'// This routine gets the Username of the File In Use
'// Credit goes to Helen for code & Mark for the idea
'// Insomniac for xl97 inStrRev
'// Amendment 25th June 2004 by IFM
'// : Name changes will show old setting
'// : you need to get the Len of the Name stored just before
'// : the double Padded Nullstrings
Dim strXl As String
Dim strFlag1 As String, strflag2 As String
Dim i As Integer, j As Integer
Dim hdlFile As Long
Dim lNameLen As Byte


strFlag1 = Chr(0) & Chr(0)
strflag2 = Chr(32) & Chr(32)

hdlFile = FreeFile
Open strPath For Binary As #hdlFile

strXl = Space(LOF(hdlFile))

Get 1, , strXl
Close #hdlFile

j = InStr(1, strXl, strflag2)

#If Not VBA6 Then
'// Xl97
For i = j - 1 To 1 Step -1
If Mid(strXl, i, 1) = Chr(0) Then Exit For
Next
i = i + 1
#Else
'// Xl2000+
i = InStrRev(strXl, strFlag1, j) + Len(strFlag1)
#End If

'// IFM

lNameLen = Asc(Mid(strXl, i - 3, 1))
LastUser = Mid(strXl, i, lNameLen)
End Function[/VBA]

You can passowrd protect it when you save it that way ...

A string (WriteResPassword) that indicates the write-reservation password for this file. If a file is saved with the password and the password isn't supplied when the file is opened, the file is opened as read-only.

Oh yes i have not think about that. then in that case the report will be opened as read only.

thanks for these wonderful idea. i have been struggling for days on that how to close the workbook but a simple password protected will do the job.

thanks Bob and Kenneth

Hi Bob / Kenneth,

Again with the same question. I have tried it with the pasword protection and its working well.

but the problem now if its open on my computer as a read only file or on the user updating the progress can it be close before as i am having an error as this :
A document with the same name is alreadu open You cannot open two doc with the same name.

can you please on that that i checks on the user PC now if the said report is open if so then it closes it and update the progress.

Something like

[vba]
On Error Resume Next
Set wb = Workbooks("workbook_name.inc_extension")
On Error Goto 0
If Not wb Is Nothing Then

wb.Close SaveChanges:=False
End If

'then open not read-only[/vba]