How check if workbook open?
in [Programming]
|
Prev: Excel sorting in VBScript
Next: MMult and MInv
From: Zack Barresse on 13 Apr 2006 16:47 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" <TomOgilvy(a)discussions.microsoft.com> wrote in message news:4D0B8927-76A2-40A8-83BA-DCD3E66B6F3D(a)microsoft.com... > 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" <IanElliott(a)discussions.microsoft.com> wrote in message >> news:2EF287FA-BB99-4E7B-BF63-59BE5F7B7250(a)microsoft.com... >> > 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. >> > >> >> >>
From: Tom Ogilvy on 13 Apr 2006 19:45 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" <firefytrNO(a)SPAMvbaexpress.com> wrote in message news:e5871tzXGHA.4060(a)TK2MSFTNGP02.phx.gbl... > 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" <TomOgilvy(a)discussions.microsoft.com> wrote in message > news:4D0B8927-76A2-40A8-83BA-DCD3E66B6F3D(a)microsoft.com... > > 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" <IanElliott(a)discussions.microsoft.com> wrote in message > >> news:2EF287FA-BB99-4E7B-BF63-59BE5F7B7250(a)microsoft.com... > >> > 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. > >> > > >> > >> > >> > >
From: Zack Barresse on 14 Apr 2006 15:54
You got it. ;) -- Regards, Zack Barresse, aka firefytr To email, remove NOSPAM "Tom Ogilvy" <twogilvy(a)msn.com> wrote in message news:ubQJLR1XGHA.3604(a)TK2MSFTNGP02.phx.gbl... > 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" <firefytrNO(a)SPAMvbaexpress.com> wrote in message > news:e5871tzXGHA.4060(a)TK2MSFTNGP02.phx.gbl... >> 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" <TomOgilvy(a)discussions.microsoft.com> wrote in message >> news:4D0B8927-76A2-40A8-83BA-DCD3E66B6F3D(a)microsoft.com... >> > 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" <IanElliott(a)discussions.microsoft.com> wrote in message >> >> news:2EF287FA-BB99-4E7B-BF63-59BE5F7B7250(a)microsoft.com... >> >> > 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. >> >> > >> >> >> >> >> >> >> >> > > |