Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ALL WBOOK ARE CLOSED... (2000 sr 1)

    I distribute a wbooks to many user.

    The wbook is the same for all.

    I would want to insert a string value "OPEN" in the table CONTROLLO in autoopen of wbook and clear in autoclose wbook the realted record from each wbook....

    Example:
    The user01 open you wbook the field WBOOK1 is free insert in the table CONTROLLO field WBOOK1 the value "OPEN"
    The user02 open you wbook the field WBOOK2 or the next is free insert in the table CONTROLLO field WBOOK2 or the first free the value "OPEN"
    ecc....

    in autoclosed of wbooks
    The user01 close you wbook insert in the table CONTROLLO field WBOOK1 the value null
    The user02 close you wbook insert in the table CONTROLLO field WBOOK2 the value null
    ecc...

    in effect controll if the wbooks is opened and closed...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    Put the following in a standard module, and supply the correct path. Note: you may already have something similar; if so, use your variable instead of strDatabase.
    <code>
    ' Substitute the correct path
    Public Const strDatabase = "F:ExcelProva.mdb"
    </code>
    Put the following in the ThisWorkbook module:
    <code>
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim cnn As Object
    Dim rst As Object
    Dim strCon As String
    Dim i As Integer

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";'"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open strCon
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "Controllo", cnn, 1, 3, 512

    For i = 6 To 1 Step -1
    If rst.Fields("WBOOK" & Format(i, "00")) = "OPEN" Then
    rst.Fields("WBOOK" & Format(i, "00")) = Null
    rst.Update
    Exit For
    End If
    Next i

    If i = 0 Then
    MsgBox "No slots were filled!", vbExclamation
    End If

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub

    Private Sub Workbook_Open()
    Dim cnn As Object
    Dim rst As Object
    Dim strCon As String
    Dim i As Integer

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";'"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open strCon
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "Controllo", cnn, 1, 3, 512

    For i = 1 To 6
    If IsNull(rst.Fields("WBOOK" & Format(i, "00"))) Then
    rst.Fields("WBOOK" & Format(i, "00")) = "OPEN"
    rst.Update
    Exit For
    End If
    Next i

    If i = 7 Then
    MsgBox "All six slots are filled!", vbExclamation
    End If

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub
    </code>
    You can add error handling yourself.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    NO WORDS....
    GREAT!

    One dubt for another suggestion....
    I insert a value "SI" with a macro in the table controllo in the fields LIBERO.
    Is posiible with a macro to intercept when this value is cleared...

    For example i run this macro

    my macro ()

    If filed LIBERO have a value "SI" not is possible to go to the nex line of macro
    when the field libero is nuul go to the nex line of macro

    In effect make a "Ping Pong" from excel and the mdb and when the value in the filed is null go to the nex line of macro.

    Or another idea is to open the mdb in exclusive mode if one user ingaged the mdb for the first not permit from another user to use that...is more simple the last idea....

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    Something like this?

    <code>Sub MyMacro()
    Dim cnn As Object
    Dim rst As Object
    Dim strCon As String

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";'"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open strCon
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "Controllo", cnn, 1, 3, 512

    If rst.Fields("LIBERO") = "SI" Then
    GoTo ExitMacro
    End If

    ' Code goes here
    ' ...

    ExitMacro:
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub</code>

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    GOOD...
    Ok, but is possible to show in automatic mode with a msgbox to allert the user when the condition of null is estabilished?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    Here is a macro that does that. It will call itself every minute until LIBERO is null, then it displays a message box and quits.

    <code>Sub TestLibero()
    Dim cnn As Object
    Dim rst As Object
    Dim strCon As String

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";'"
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open strCon
    Set rst = CreateObject("ADODB.Recordset")
    rst.Open "Controllo", cnn, 1, 3, 512

    If IsNull(rst.Fields("LIBERO")) Then
    MsgBox "LIBERO!", vbInformation
    Else
    Application.OnTime Now + TimeSerial(0, 1, 0), "TestLibero"
    End If

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    End Sub</code>

    You will have to start this macro yourself, since I don't know when you want to run it.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    I hope the life smiled you for ever;-)
    The code naturally is tath i wount!

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    oh oh i have send you a wrong information the condition not is null but when the field contain "SI" i have inverted the param re send with the correction.
    ;-)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    You can replace

    <code>If IsNull(rst.Fields("LIBERO")) Then</code>

    with

    <code>If rst.Fields("LIBERO") = "SI" Then</code>

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    ... for my knoledgment

    Application.OnTime Now + TimeSerial(0, 1, 0), "TestLibero"

    but this is the step of time to controll when the contidion existis?

    It run do until the value "SI" is inserted in the filed, or not?

    If yes, if i heve understand, this routin make a Ping Pong at avery time?

    And the param TimeSerial(0, 1, 0), is in minute, in second or Hour?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    The line Application.OnTime Now + TimeSerial(0, 1, 0), "TestLibero" calls TestLibero again.

    The If ... Then line must test in such a way that this line is called if it is necessary to wait a little bit longer. You must decide what the correct test is.

    The time to wait is TimeSerial(Hours, Minutes, Seconds). In my example, TimeSerial(0, 1, 0) represents 1 minute. If you want to wait 3 minutes, use TimeSerial(0, 3, 0), and if you want to wait 45 seconds, use TimeSerial(0, 0, 45). Do not make the time too short.

    Once the message box hase been displayed, the code stops, otherwise the user would see the message box many times.

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    BIG EXPLAIN....

    Idea but is possible to lock the wbook with a msgbox and clear it when the condition is estabilished...
    For example:
    the macro start
    the condition not is estabilished appera amsgbox" attendere do until the condition is free"
    when the condition is extabilished clear the msgbox and go to the next line...

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    The attached workbook contains a userform and code to make it wait. Before trying it, you must substitute the correct path in the strDatabase constant.

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    OH, OH....
    little modify...
    I have post the problem if all 6 wbook are opened and closed, but in effect problem is, i dont know how match wbook are opened...
    Not know how many user opened the wbook...

    For example: today 3 user, the next day 7, the nex day 1.....

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: ALL WBOOK ARE CLOSED... (2000 sr 1)

    Your CONTROLLO table has fields WBOOK01 to WBOOK06, so you can only register 6 simultaneous users. If there can be more than 6, you could add fields WBOOK07 etc. and adjust the code accordingly.

    This becomes tedious if there are a lot of users, but it would require a completely different design to make it flexible. Instead of having a separate field for each user in a single record, you would have to use a single field, but a separate record for each user.

Page 1 of 2 12 LastLast

Posting Permissions

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