Results 1 to 7 of 7

Thread: Is it open?

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is it open?

    I have a spreadsheet which logs calls. Each row in this sheet is a call. The process I would like to develop would occur at the end of the day, but I need to know what would be the best way to go about it.

    Ideally, the user would click on 'Finalize.' The code would then:

    Save a copy of the database to a temporary directory.
    Copy all rows which contain data, and paste them to a master sheet on the network.

    I'm thinking the best way to do this is to have one 'master' sheet per user on the network, then a 'compiler' to combine the master sheets into one mammoth spreadsheet.

    However, if I bypass the master sheets and have the users paste direct to one mammoth spreadsheet at the end of the day, I may encounter problems, as more than one user may be 'finalizing' at a time. (I.E. Multiple users accessing one sheet.)

    So, without getting into the details, how can I (programatticaly) determine that the spreadsheet they're attempting to access is already in use? the process should only take a few minutes, so I want the code to automatically "retry" in the background every few minutes until the process is sucessful, and if it isn't sucessful before shutdown, is there any way to make it continue in the morning?

    The whole thing is giving me a huge <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>, and making me <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> at that! Any help would be greatly appreciated!

    Regards,
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is it open?

    Maybe you can use code to check if a workbook is open:

    <pre>Private Function TestWorkbookIsOpen(WbName As String) As Boolean
    Dim Wb As Workbook
    On Error Resume Next
    Set Wb = Workbooks(WbName)
    If Err.Number <> 0 Then
    Err.Clear
    TestWorkbookIsOpen = False
    Else
    TestWorkbookIsOpen = True
    End If
    End Function


    Sub test()
    Dim WbName As String
    WbName = "test1.xls"
    MsgBox "Workbook is Open is " & TestWorkbookIsOpen(WbName)
    End Sub
    </pre>


    Another possibility might be to use the Notify:=True property of the Workbooks.Open method. However, I never tried it out.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is it open?

    That code will test if the workbook is open by this user. It will not test if another user on the network has the workbook open, which is what I think he wanted.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is it open?

    Yes, I need to know if the workbook is open by Any user on the network... Any ideas?

    Thanks for the quick reply Gentlemen! <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is it open?

    At the moment I am not on a network where I can test this, so it is untested. However, the following is what I would try:

    <pre>Dim oSum As Workbook
    Application.DisplayAlerts = False
    On Error Resume Next
    Workbooks.Open FileName:="C:MyFilesSummary.XLS"
    On Error GoTo 0
    Application.DisplayAlerts = True
    Set oSum = Workbooks("Summary.XLS")
    If oSum Is Nothing Then
    'Can't open code here
    End If
    </pre>


    That would need to be beefed up a bit to look at the error code to see if the reason the file could not be opened was because someone else had it open.
    Legare Coleman

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is it open?

    Could it be that the file will be opened read-only, not generating an error, when in use? Should you not specify the Notify option?

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is it open?

    I wasn't sure, and can't test it. Notify didn't sound like something you would want to set as it is sound like it would result in something that he does not want (the notification whatever that is). My guess would be to set ReadOnly to False.
    Legare Coleman

Posting Permissions

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