Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Checking states? (I think) (XL97/WinNT4)

    This post is referring to the same project as <!post=Naming Ranges,230086>Naming Ranges<!/post> (although a completely different question), but that thread was getting so long I'm surprised someone hadn't locked it!

    I have discovered I need to be able to tell if a workbook (1) doesn't exist, (2) exists but is not open or (3) exists and is open. I've tried to work this out before and not succeeded, so I'd be grateful for any help anyone can give!

    Many thanks
    Beryl M


  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking states? (I think) (XL97/WinNT4)

    These two functions should be of help:

    Option Explicit

    Sub test()
    Dim sName As String
    Dim sPath As String
    sPath = "c:data"
    sName = "test.xls"
    MsgBox IsOpen(sName)
    MsgBox IsOnDisc(sPath, sName)
    End Sub

    Function IsOpen(sName As String) As Boolean
    Dim oBook As Workbook
    On Error Resume Next
    Set oBook = Workbooks(sName)
    If oBook Is Nothing Then
    IsOpen = False
    Else
    IsOpen = True
    End If
    End Function

    Function IsOnDisc(sPath As String, sName As String) As Boolean
    ChDrive (Left(sPath, 1))
    If Dir(sPath & sName) = "" Then
    IsOnDisc = False
    Else
    IsOnDisc = True
    End If
    End Function
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Checking states? (I think) (XL97/WinNT4)

    Jan, you're an angel!

    Just one question - what does the ChDrive line do, in IsOnDisc? I can't see that it connects to anything (and ChDrive isn't dim'ed, so with Option Explicit it causes an error!) so I presume it's just been left in by accident?

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Checking states? (I think) (XL97/WinNT4)

    In addition to Jan's functions, the following will return TRUE if the File is locked by anoher user :

    Function IsFileOpen(strFileName As String) As Boolean
    On Error Resume Next
    Open strFileName For Binary Access Read Write Lock Read Write As #1
    Close #1
    IsFileOpen = Err.Number
    Err.Clear
    End Function


    Andrew

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Checking states? (I think) (XL97/WinNT4)

    And that was something I hadn't even thought to check for ...

    Thanks for preventing me falling on my face!! (well, for removing one of the many blocks I would have used to do so, anyway!)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Checking states? (I think) (XL97/WinNT4)

    Beryl,

    ChDrive is a command to change the active Drive.

    If you do not include the Drive in the sPath then it will either fail or possibly lead to erroneous results. If you are just checking the current directory, then you could omit it and just pass the file name as the argument.


    Andrew

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Checking states? (I think) (XL97/WinNT4)

    Ah, I see! <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>

    I thought it was a variable, like sPath, sName, etc!

    Ta muchly!
    Beryl M


  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking states? (I think) (XL97/WinNT4)

    Without testing:

    The function is declared as a boolean, yet err.number may return a long.
    So wouldn't that give a type mismatch if you get an err.number = 70 (file is in use) ?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Checking states? (I think) (XL97/WinNT4)

    Jan,

    It does work, as an implicit conversion is made. The following is from the help files :
    <hr>When other numeric types are converted to Boolean values, 0 becomes False and all other values become True. When Boolean values are converted to other data types, False becomes 0 and True becomes -1.<hr>
    If Err.Number <> 0 Then
    IsFileOpen = True
    EndIf

    could be used instead

    Andrew

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Checking states? (I think) (XL97/WinNT4)

    <hr>It does work<hr>

    Thanks. I should've known <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •