Thanks for any help.
I have a macro (Excel 2000) in a workbook that copies and pastes some tabs
into the workbook it opens. But sometimes the files that it opens are already
open by another user. In that case, there is an error message and some other
stuff can happen that messes up the program. I would like to stop this, is
there any properties or code that I can do to tell if a file is already open?
I thought of using an On Error stuff, but is there another way?
Thanks again.
You can check if the ReadOnly property of the newly opened workbook is true. If so, its open with another user.
Martin
Ian,
Have a look at the links on from the post below:
http://groups.google.co.uk/group/mic...06e446e41e7314
Best regards
John
"Ian Elliott" <[email protected]> wrote in message
news:[email protected]...
> Thanks for any help.
> I have a macro (Excel 2000) in a workbook that copies and pastes some tabs
> into the workbook it opens. But sometimes the files that it opens are
> already
> open by another user. In that case, there is an error message and some
> other
> stuff can happen that messes up the program. I would like to stop this, is
> there any properties or code that I can do to tell if a file is already
> open?
> I thought of using an On Error stuff, but is there another way?
> Thanks again.
>
Ivan Moala has a solution to checking if a file is open on a network or not,
an API method and a VBA method here:
http://www.xcelfiles.com/IsFileOpen.html
HTH
--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM
"Ian Elliott" <[email protected]> wrote in message
news:[email protected]...
> Thanks for any help.
> I have a macro (Excel 2000) in a workbook that copies and pastes some tabs
> into the workbook it opens. But sometimes the files that it opens are
> already
> open by another user. In that case, there is an error message and some
> other
> stuff can happen that messes up the program. I would like to stop this, is
> there any properties or code that I can do to tell if a file is already
> open?
> I thought of using an On Error stuff, but is there another way?
> Thanks again.
>
If you don't care about who has the file open, you can modify Ivan's code or
just start out using this simple code (the guts of which is identical to the
approach used by Ivan).
http://support.microsoft.com?kbid=138621
XL: Macro Code to Check Whether a File Is Already Open
http://support.microsoft.com?kbid=291295
XL2002: Macro Code to Check Whether a File Is Already Open
http://support.microsoft.com?kbid=213383
XL2000: Macro Code to Check Whether a File Is Already Open
http://support.microsoft.com?kbid=184982
WD97: VBA Function to Check If File or Document Is Open
--
Regards,
Tom Ogilvy
"Zack Barresse" wrote:
> Ivan Moala has a solution to checking if a file is open on a network or not,
> an API method and a VBA method here:
> http://www.xcelfiles.com/IsFileOpen.html
>
> HTH
>
> --
> Regards,
> Zack Barresse, aka firefytr
> To email, remove NOSPAM
>
>
> "Ian Elliott" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks for any help.
> > I have a macro (Excel 2000) in a workbook that copies and pastes some tabs
> > into the workbook it opens. But sometimes the files that it opens are
> > already
> > open by another user. In that case, there is an error message and some
> > other
> > stuff can happen that messes up the program. I would like to stop this, is
> > there any properties or code that I can do to tell if a file is already
> > open?
> > I thought of using an On Error stuff, but is there another way?
> > Thanks again.
> >
>
>
>
For a local approach, I generally take the easy two-liner..
Function IsWbOpen(wbName as string) as boolean
On error resume next
IsWbOpen = Len(Workbooks(wbName).Name)
End function
--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> If you don't care about who has the file open, you can modify Ivan's code
> or
> just start out using this simple code (the guts of which is identical to
> the
> approach used by Ivan).
>
>
> http://support.microsoft.com?kbid=138621
> XL: Macro Code to Check Whether a File Is Already Open
>
> http://support.microsoft.com?kbid=291295
> XL2002: Macro Code to Check Whether a File Is Already Open
>
> http://support.microsoft.com?kbid=213383
> XL2000: Macro Code to Check Whether a File Is Already Open
>
> http://support.microsoft.com?kbid=184982
> WD97: VBA Function to Check If File or Document Is Open
>
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Zack Barresse" wrote:
>
>> Ivan Moala has a solution to checking if a file is open on a network or
>> not,
>> an API method and a VBA method here:
>> http://www.xcelfiles.com/IsFileOpen.html
>>
>> HTH
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr
>> To email, remove NOSPAM
>>
>>
>> "Ian Elliott" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks for any help.
>> > I have a macro (Excel 2000) in a workbook that copies and pastes some
>> > tabs
>> > into the workbook it opens. But sometimes the files that it opens are
>> > already
>> > open by another user. In that case, there is an error message and some
>> > other
>> > stuff can happen that messes up the program. I would like to stop this,
>> > is
>> > there any properties or code that I can do to tell if a file is already
>> > open?
>> > I thought of using an On Error stuff, but is there another way?
>> > Thanks again.
>> >
>>
>>
>>
Neither the OP's question or my suggestion was for a workbook opened in the
same instance of Excel, so I assume this is some added information.
--
Regards,
Tom Ogilvy
"Zack Barresse" <[email protected]> wrote in message
news:[email protected]...
> For a local approach, I generally take the easy two-liner..
>
> Function IsWbOpen(wbName as string) as boolean
> On error resume next
> IsWbOpen = Len(Workbooks(wbName).Name)
> End function
>
> --
> Regards,
> Zack Barresse, aka firefytr
> To email, remove NOSPAM
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > If you don't care about who has the file open, you can modify Ivan's
code
> > or
> > just start out using this simple code (the guts of which is identical to
> > the
> > approach used by Ivan).
> >
> >
> > http://support.microsoft.com?kbid=138621
> > XL: Macro Code to Check Whether a File Is Already Open
> >
> > http://support.microsoft.com?kbid=291295
> > XL2002: Macro Code to Check Whether a File Is Already Open
> >
> > http://support.microsoft.com?kbid=213383
> > XL2000: Macro Code to Check Whether a File Is Already Open
> >
> > http://support.microsoft.com?kbid=184982
> > WD97: VBA Function to Check If File or Document Is Open
> >
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Zack Barresse" wrote:
> >
> >> Ivan Moala has a solution to checking if a file is open on a network or
> >> not,
> >> an API method and a VBA method here:
> >> http://www.xcelfiles.com/IsFileOpen.html
> >>
> >> HTH
> >>
> >> --
> >> Regards,
> >> Zack Barresse, aka firefytr
> >> To email, remove NOSPAM
> >>
> >>
> >> "Ian Elliott" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Thanks for any help.
> >> > I have a macro (Excel 2000) in a workbook that copies and pastes some
> >> > tabs
> >> > into the workbook it opens. But sometimes the files that it opens are
> >> > already
> >> > open by another user. In that case, there is an error message and
some
> >> > other
> >> > stuff can happen that messes up the program. I would like to stop
this,
> >> > is
> >> > there any properties or code that I can do to tell if a file is
already
> >> > open?
> >> > I thought of using an On Error stuff, but is there another way?
> >> > Thanks again.
> >> >
> >>
> >>
> >>
>
>
You got it.
--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM
"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Neither the OP's question or my suggestion was for a workbook opened in
> the
> same instance of Excel, so I assume this is some added information.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Zack Barresse" <[email protected]> wrote in message
> news:[email protected]...
>> For a local approach, I generally take the easy two-liner..
>>
>> Function IsWbOpen(wbName as string) as boolean
>> On error resume next
>> IsWbOpen = Len(Workbooks(wbName).Name)
>> End function
>>
>> --
>> Regards,
>> Zack Barresse, aka firefytr
>> To email, remove NOSPAM
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > If you don't care about who has the file open, you can modify Ivan's
> code
>> > or
>> > just start out using this simple code (the guts of which is identical
>> > to
>> > the
>> > approach used by Ivan).
>> >
>> >
>> > http://support.microsoft.com?kbid=138621
>> > XL: Macro Code to Check Whether a File Is Already Open
>> >
>> > http://support.microsoft.com?kbid=291295
>> > XL2002: Macro Code to Check Whether a File Is Already Open
>> >
>> > http://support.microsoft.com?kbid=213383
>> > XL2000: Macro Code to Check Whether a File Is Already Open
>> >
>> > http://support.microsoft.com?kbid=184982
>> > WD97: VBA Function to Check If File or Document Is Open
>> >
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Zack Barresse" wrote:
>> >
>> >> Ivan Moala has a solution to checking if a file is open on a network
>> >> or
>> >> not,
>> >> an API method and a VBA method here:
>> >> http://www.xcelfiles.com/IsFileOpen.html
>> >>
>> >> HTH
>> >>
>> >> --
>> >> Regards,
>> >> Zack Barresse, aka firefytr
>> >> To email, remove NOSPAM
>> >>
>> >>
>> >> "Ian Elliott" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Thanks for any help.
>> >> > I have a macro (Excel 2000) in a workbook that copies and pastes
>> >> > some
>> >> > tabs
>> >> > into the workbook it opens. But sometimes the files that it opens
>> >> > are
>> >> > already
>> >> > open by another user. In that case, there is an error message and
> some
>> >> > other
>> >> > stuff can happen that messes up the program. I would like to stop
> this,
>> >> > is
>> >> > there any properties or code that I can do to tell if a file is
> already
>> >> > open?
>> >> > I thought of using an On Error stuff, but is there another way?
>> >> > Thanks again.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks