Checking to see if a paticular file is open or not


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: Checking to see if a paticular file is open or not

Hybrid View

  1. #1
    Join Date
    Nov 2004
    Posts
    293

    Checking to see if a paticular file is open or not

    I have a VB6 program that opens an existing Excel workbook fill's in some data and then closes the Excel workbook. My problem is that if two people are using my program at the same time the program could be trying to open the same Excel workbook at the same time and this causes my program problems. Is there a way I can check to see if this paticular Excel sheet is open already by someone else prior to opening it for this user? The paticular Excel workbook is on a common drive that everone can access.

  2. #2
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120

  3. #3
    Join Date
    Nov 2004
    Posts
    293
    This did not work. The file that I need to check, if its open or not, is located on a server and this code only seems to only work if the file is open on the users computer also.

    Let me ********, the paticuler file is on a server drive. One user, on computer "A" uses my VB6 program (loaded on there desktop) to open that file on the server, then another user on a different computer, computer "B", opens my program (loaded on there desktop), and if the first user has that paticular file open ( the file located on the server), then his program recognizes that and waits for the other person to finish and the file to close before it then opens the paticular file for itself.
    That code only checks to see if it is open on the same computer, I need to know if anyone else has it open on some other computer.


    Option Explicit
    Option Compare Text

    Function IsWbOpen(wbName As String) As Boolean
    Dim i As Long
    For i = Workbooks.Count To 1 Step -1
    If Workbooks(i).Name = wbName Then Exit For
    Next
    If i <> 0 Then IsWbOpen = True
    End Function


    '\\ Example
    Sub Test()
    Dim wb As Workbook, strName As String, strPath As String
    strName = "Target Book.xls"
    strPath = "C:\Documents and Settings\Rob\Desktop\" '<<== CHANGE TO SUIT
    If IsWbOpen(strName) Then
    Set wb = Workbooks(strName)
    'Make the workbook the Active Workbook
    wb.Activate
    Else
    Set wb = Workbooks.Open(strPath & strName)
    End If
    End Sub

  4. #4
    Join Date
    Mar 2009
    Location
    Italy - Breganze (VI)
    Posts
    120
    With this function you can check both on local and server:

    Code:
    Public Function XLS_IsOpen(ByVal sXLSFile As String) As Boolean
        Dim fNum As Integer
        fNum = FreeFile
        On Error GoTo ERR_HANDLER
        Open sXLSFile For Binary Access Write As #fNum
            DoEvents
        Close #fNum
        XLS_IsOpen = False
        
        Exit Function
        
    ERR_HANDLER:
        XLS_IsOpen = True
    End Function

  5. #5
    Join Date
    Nov 2004
    Posts
    293
    Well this latest did not seem to work either. What seems to be happening is that just to open and read is not a problem if someone else has the same file open or not. However if you do a save on it, then it does matter and I'll get an error at that point if that file was open by someone else. Now yes I can captur that error and work accordingly but I need to find out when I first open the file if somone else already has it opened.

    If I open the file outside of code (by double clicking on the icon) I get the "File in use..." message, if it is already open by someone else, but if I open it from code I do not.

  6. #6
    Join Date
    Nov 2004
    Posts
    293
    Interesting thing happened. When I change the line of code
    from this
    Open sXLSFile For Binary Access Write As #fNum
    to this
    Open FileName For Binary Access Read Write Lock Read Write As #1
    I then get my error on opening that I expected. Now I can work things out.

    Thanks

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Did you try simply setting the workbook up as "Shared"?
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  8. #8
    Join Date
    Nov 2004
    Posts
    293
    No, I don't know anything about "Shared" workbooks but I'll read up on it. That might be a usefull tool in this case. Thanks for the heads up.

  9. #9
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Open up the workbook and click: Tools/Share Workbook

    Check the box: "Allow changes by more than one user at the same time. This also allows workbook merging"

    "OK" yourself back to the workbool.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  10. #10
    Join Date
    Nov 2004
    Posts
    293
    This is good but I think when it has changes by more then one it then asks one of the users how to proceed, and my users are not capable of making that kind of decision. (that would take thinking on there part).

Similar Threads

  1. Replies: 1
    Last Post: 01-28-2006, 07:59 AM
  2. Need help checking if file is already saved VB.Net 03
    By boomstick425 in forum VB Classic
    Replies: 1
    Last Post: 01-12-2006, 10:54 AM
  3. How to open a bmp file?
    By FrankWu in forum Java
    Replies: 0
    Last Post: 08-16-2002, 05:21 PM
  4. open text file using shell
    By Hian Chew in forum VB Classic
    Replies: 0
    Last Post: 03-06-2001, 01:28 PM
  5. Replies: 0
    Last Post: 12-19-2000, 04:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center