Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi all

    A seemingly simple task.
    I have some Excel datafiles which occasionally need to be opened, updated, saved and closed.
    By any of several people.
    I want to avoid shared workbooks completely.
    I have kept the files very small for fast network access.
    Most of the time, there shouldn't be an issue with the clash of two persons trying to update one of the same datafiles at the same time.
    But, just to be certain, I wanted to check for this rare occasion.
    I can of course check when a datafile is opened if it is in read-only mode.
    If this is the case, I want to close it straightaway (no file saving), wait say, 2 seconds, and then reload the datafile and try the update again.
    Perhaps make five attempts to update, then give up and display a message box.

    Loops drive me nuts.
    Can anyone give me some guidance.
    Where's loop-expert Legare when I need her?

    zeddy

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Not the Full VBA Code but should give you what your looking for.

    First Create the following SUB

    SUB PauseTime()
    Dim PauseTime, Start
    PauseTime = 2 'Pause is set to 2 Seconds
    Start = Timer
    Do While Timer < Start + PauseTime
    DoEvents 'Yield to other processes during Pause
    Loop

    Then you would have a second Sub something like this

    SUB RetreiveFile()
    Insert Proper VBA to Open the File 'First Attempt
    Insert VBA to see if Read Only
    If Read Only Then
    Insert VBA to close the file
    Run PauseTime (See Below)
    End If
    Insert Proper VBA to Open the File
    Insert VBA to see if Read Only
    If Read Only Then
    Insert VBA to close the file
    Run PauseTime (See Below)
    End If

    If all attempts fail Insert VBA for message box to User
    End Sub

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Many thanks!

    Just what I needed to get me going and fix that issue.

    Apologies for the long delay in replying.

    I really really appreciate your help.

    zeddy

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Zeddy,

    You may find this helpful:


    Code:
    If Not oWkBk.ReadOnly Then
      Set oCtlReviewMenu = _
         newMenu.CommandBar.Controls.Add(Type:=msoControlButton, ID:=1)
      With oCtlReviewMenu
          .Caption = "Records Review Input/Update Form"
          .TooltipText = "Records Review Input/Update Form"
          .Style = msoButtonCaption
          .OnAction = "ReviewMenu"
      End With
    End If
    The code above checks the object oWkBK containing a reference to the open workbook to see if it is read only then enther sets up a control on a custom menu for Input/Update or just skips it if the WB is read only.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Thanks for that tip.

    I shall add it to my useful things to know list.
    It is always great to get a new viewpoint and ideas for doing things in different ways.

    Much appreciated.

    zeddy

Posting Permissions

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