Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: comflict in access flat file

    Hi,

    I have written an excel vb routine in the commandbutton_click() event. That routine saves some info. picked up from various cells in sheet1 in the this directory: \\LOGCALL\USERSTATS\userstats.log.

    Problem:
    Many users will be doing the same thing over the network. It is possible for two people to save his/her work at the same time. It there some code I can add to prevent a comflict or a way I can lock the *.log file that needs updating and make the other wait in line until the file is unlocked?

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    make your workbook a shared Workbook. See 'Tools->Share Workbook' With a shared workbook Excel will handle conflicts and advise if 2 users attempt to update a value. If you have many users this may be problematic, then you may consider using an Access Database for your needs. You can not edit VB code in a shared workbook, you'll have to switch to non-shared mode or keep you code in a separate workbook from the UI book.
    ~

    Bill

  3. #3
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    shared workbook

    How do you do that? --> "..or keep you code in a separate workbook from the UI book." How do you make the User Interface common to all and the VB code in another workbook?

  4. #4
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by Alexxx12
    How do you do that? --> "..or keep you code in a separate workbook from the UI book." How do you make the User Interface common to all and the VB code in another workbook?
    Code does not have to be in the same workbook as your data or the same workbook the user sees. You can refer to another worksheet that is not active, or another worksheet in another workbook or the active workbook for example ActiveSheet.Cells(2,3) works on the active sheet as long as you give the correct reference for the state of the application. Excel requires at least 1 worksheet in a workbook. If I wish to have a code workbook that works on other workbooks, I don't want the user to see the code book or be able to mess with it. You can hide the workbook from view close and save it in the hidden state, set the file properties to read only, since it is only code there's no reason to edit after deploying. attaching a toolbar to the code book can allow access to the procedures/functions.

    Here's a senario: you have three workbooks one is a control panel file, One is a Working File, One is a Process or code file.
    controlPnl.xls (code file has menu, and toolbar logic, code, hidden, Readonly)
    workdata.xls (file users access to view edit data, data, visible, Read Write)
    workdataProc.xls (Process for automation of workdata, code file, hidden, ReadOnly)

    You load the controlPnl.xls at XL startup as an addin or put a shortcut in the users XLStart folder. This displays a custom menu and toolbar, showing all the stuff you use, the file is hidden so the user only sees the toolbar and menu, the code is available but transparent to the user.

    When the user selects the menu item or TB button to "Open Work Data" the workdata.xls file opens and the workdataProc.xls file opens. Because workdataProc is hidden the user only sees workdata and any custom toolbar and menu you decide to show for access to automation.

    In the code files you have a OnClose event that deletes the custom Toolbars and Menus. This way if you update the code file and place it in the public location they get all the new menus etc. when they open it without any old stuff lingering.

    So you should get the idea. you can separate code from the data, you can make a modular structure where code for specific tasks are in separate workbooks, you can ease maintenance because you can mirror development files to the production files, you can update files in a public folder without having to distribute to the individual user machines, you can have a master control panel to give a menu for access to all the user files and utilities.
    Last edited by savbill; 08-14-04 at 19:11.
    ~

    Bill

  5. #5
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    how about a sample example

    one commandButton
    column J on the same workbook has a list of names
    richard
    patrick
    gilles
    corinne
    damien
    ..
    ..
    ..


    private sub commandbutton1()
    dim i as integer
    for i = 1 to 10
    range("A" & i).value = range("J" & i).value
    next i
    end sub

Posting Permissions

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