check if a file is open
 

Hi

bit of a beginner in excel VBA but i am trying to find out if can check to
see if a file is open

as in

IF 'file a is open' THEN
Code
code
code
ELSE
msg "file a is not open please try again"
END IF

is this possible and does anyone have any code for this

Thanks
C

check if a file is open
 

Hi
WBFullName is the full path to the file. WBFileName gets the name of
the file. The functions IsFileOpen and IsWorkBookOpen decide if the
file is open on another or your machine.

WBFileName = GetFileName(WBFullName)
If IsFileOpen(WBFullName) Then 'if someone has it open on network
If Not IsWorkBookOpen(WBFileName) Then 'if that someone isn't
you
MsgBox "This file is in use by another user"
Else
MsgBox "You have it open already!"
End If
Else
'open it etc
End If


Uses the functions:
'See Green p80
'Returns the full file name from the end of a path by looking for first
\
'If no \, returns the file name
Public Function GetFileName(FullPathString As String) As String
Dim stPathSep As String 'Path separator, \
Dim FPLength As Integer 'length of FullPathString
Dim i As Integer 'counter
stPathSep = Application.PathSeparator
FPLength = Len(FullPathString)
For i = FPLength To 1 Step -1
If Mid(FullPathString, i, 1) = stPathSep Then Exit For
Next i
GetFileName = Right(FullPathString, FPLength - i)
End Function

'Lifted from Microsoft KB
' This function checks to see if a file is open or not. If the file is
' already open, it returns True. If the file is not open, it returns
' False. Otherwise, a run-time error will occur because there is
' some other problem accessing the file.
Function IsFileOpen(filename As String)
Dim filenum As Integer, errnum As Integer
Err.Clear
On Error Resume Next ' Turn error checking off.
filenum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open filename For Input Lock Read As #filenum
Close filenum ' Close the file.
errnum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case errnum
' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False
' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True
' Another error occurred.
Case Else
IsFileOpen = False
End Select
End Function

'See Green p81
Function IsWorkBookOpen(WorkBookName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next
Set Wkb = Workbooks(WorkBookName)
If Not Wkb Is Nothing Then
IsWorkBookOpen = True
End If
Set Wkb = Nothing
End Function

regards
Paul

check if a file is open
 

also i dont always have the path name of the file

it moves but the file name is always the same

can i check to see if a window is open




"C" wrote in message
...
Hi

bit of a beginner in excel VBA but i am trying to find out if can check to
see if a file is open

as in

IF 'file a is open' THEN
Code
code
code
ELSE
msg "file a is not open please try again"
END IF

is this possible and does anyone have any code for this

Thanks
C

check if a file is open
 

thanks for before

i know this isnt as complex with the checking who has it open but i have
come up with this


Dim chkfile
chkfile = 0
Update.Hide
For Each ws In Worksheets
If ws.Name = "revenue schedules 2006.xls" Then
chkfile = 1
End If
Next ws

If chkfile = 1 Then
'do code
Else
MsgBox "the Rev Schedules is not open. please open the file and try
again"

End If


C

"C" wrote in message
...
also i dont always have the path name of the file

it moves but the file name is always the same

can i check to see if a window is open




"C" wrote in message
...
Hi

bit of a beginner in excel VBA but i am trying to find out if can check
to see if a file is open

as in

IF 'file a is open' THEN
Code
code
code
ELSE
msg "file a is not open please try again"
END IF

is this possible and does anyone have any code for this

Thanks
C

check if a file is open
 

There probably aren't enough files to check to worry about it, but it would
be a little more efficient to add the "Exit For" as I've indicated in the
code below.

"C" wrote:

thanks for before

i know this isnt as complex with the checking who has it open but i have
come up with this


Dim chkfile
chkfile = 0
Update.Hide
For Each ws In Worksheets
If ws.Name = "revenue schedules 2006.xls" Then
chkfile = 1
== exit for
End If
Next ws

If chkfile = 1 Then
'do code
Else
MsgBox "the Rev Schedules is not open. please open the file and try
again"

End If


C

"C" wrote in message
...
also i dont always have the path name of the file

it moves but the file name is always the same

can i check to see if a window is open




"C" wrote in message
...
Hi

bit of a beginner in excel VBA but i am trying to find out if can check
to see if a file is open

as in

IF 'file a is open' THEN
Code
code
code
ELSE
msg "file a is not open please try again"
END IF

is this possible and does anyone have any code for this

Thanks
C

check if a file is open
 

Dim chkfile
chkfile = 0
Update.Hide
For Each ws In Workbooks
If ws.Name = "revenue schedules 2006.xls" Then
chkfile = 1
End If
Next ws


"C" wrote in message
...
thanks for before

i know this isnt as complex with the checking who has it open but i have
come up with this


Dim chkfile
chkfile = 0
Update.Hide
For Each ws In Worksheets
If ws.Name = "revenue schedules 2006.xls" Then
chkfile = 1
End If
Next ws

If chkfile = 1 Then
'do code
Else
MsgBox "the Rev Schedules is not open. please open the file and try
again"

End If


C

"C" wrote in message
...
also i dont always have the path name of the file

it moves but the file name is always the same

can i check to see if a window is open




"C" wrote in message
...
Hi

bit of a beginner in excel VBA but i am trying to find out if can check
to see if a file is open

as in

IF 'file a is open' THEN
Code
code
code
ELSE
msg "file a is not open please try again"
END IF

is this possible and does anyone have any code for this

Thanks
C

check if a file is open
 

nice one

thanks


"Patricia Shannon" wrote in
message ...
There probably aren't enough files to check to worry about it, but it
would
be a little more efficient to add the "Exit For" as I've indicated in the
code below.

"C" wrote:

thanks for before

i know this isnt as complex with the checking who has it open but i have
come up with this


Dim chkfile
chkfile = 0
Update.Hide
For Each ws In Worksheets
If ws.Name = "revenue schedules 2006.xls" Then
chkfile = 1
== exit for
End If
Next ws

If chkfile = 1 Then
'do code
Else
MsgBox "the Rev Schedules is not open. please open the file and try
again"

End If


C

"C" wrote in message
...
also i dont always have the path name of the file

it moves but the file name is always the same

can i check to see if a window is open




"C" wrote in message
...
Hi

bit of a beginner in excel VBA but i am trying to find out if can
check
to see if a file is open

as in

IF 'file a is open' THEN
Code
code
code
ELSE
msg "file a is not open please try again"
END IF

is this possible and does anyone have any code for this

Thanks
C