check if a file is open

[C]

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

[paul.robinson@it-tallaght.ie]

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

[C]

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" <c@oco.com.invalid> wrote in message
news:kPadnYSHpbe4tLDZRVnyjg@bt.com...
> 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
>

[C]

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" <c@oco.com.invalid> wrote in message
news:s9udnQEkILMTsLDZRVnyuw@bt.com...
> 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" <c@oco.com.invalid> wrote in message
> news:kPadnYSHpbe4tLDZRVnyjg@bt.com...
>> 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
>>
>
>

[Patricia Shannon]

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" <c@oco.com.invalid> wrote in message
> news:s9udnQEkILMTsLDZRVnyuw@bt.com...
> > 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" <c@oco.com.invalid> wrote in message
> > news:kPadnYSHpbe4tLDZRVnyjg@bt.com...
> >> 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
> >>
> >
> >
>
>
>

[C]

> 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" <c@oco.com.invalid> wrote in message
news:XpGdnSG_Q7GyrLDZRVny0A@bt.com...
> 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" <c@oco.com.invalid> wrote in message
> news:s9udnQEkILMTsLDZRVnyuw@bt.com...
>> 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" <c@oco.com.invalid> wrote in message
>> news:kPadnYSHpbe4tLDZRVnyjg@bt.com...
>>> 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
>>>
>>
>>
>
>

[C]

nice one

thanks


"Patricia Shannon" <PatriciaShannon@discussions.microsoft.com> wrote in
message news:B9E1D5CF-ACF2-4F3D-BE69-46026233353B@microsoft.com...
> 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" <c@oco.com.invalid> wrote in message
>> news:s9udnQEkILMTsLDZRVnyuw@bt.com...
>> > 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" <c@oco.com.invalid> wrote in message
>> > news:kPadnYSHpbe4tLDZRVnyjg@bt.com...
>> >> 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
>> >>
>> >
>> >
>>
>>
>>