Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jul 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Front End/Back End solution

    I have designed an excel spreadsheet at my work with some heavy VBA code in the background. Basically the spreadsheet is deliberately coded to only run off a single computer normally, and can be edited on my computer in the office. At the moment if any tweaks need to be made to the spreadsheet, it has to be closed down on one machine, and other staff can't have access to it until I am done.

    What I would like is a way to edit the VBA code in the spreadsheet on my computer without affecting the data, whilst also allowing other staff members to manipulate and save the data in the spreadsheet from another computer uninterrupted, at the same time.

    Is something like this possible in Excel?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Link,

    Welcome to the Lounge as a New Poster!

    Yes it is possible. You'll use two workbooks.

    Workbook #1 will contain ALL of your VBA code.
    Workbook #2 will contain ONLY DATA Sheets.

    You just design Workbook #1 to automatically open Workbook #1 in it's Auto_Open procedure and make sure you code all your macros to access the data on WorkBook#2.

    The easiest way to do this is to define two Object variables as:
    Code:
    Dim Wkb1 as Workbook
    Dim Wkb2 as Workbook
    
    Set Wkb1 = Activeworkbook
    Set Wkb2 = Workbook.Open("C:\Excel\Workbook#2.xls")
    You can then use these variables to control where the work is done.
    I've done this many times and it works just fine. You can modify and test on the workbook on your computer to your hearts content w/o disrupting production. When you are satisfied that your code changes are working correctly just load the new Workbook#1 onto a shared drive where the others can access it. Of course you'll have to do this when it is NOT in use.

    BTW: There are quite a few posts here using this method.

    Hope this is enough to get you started if not post back.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Jul 2015
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Link,

    Welcome to the Lounge as a New Poster!

    Yes it is possible. You'll use two workbooks.

    Workbook #1 will contain ALL of your VBA code.
    Workbook #2 will contain ONLY DATA Sheets.

    You just design Workbook #1 to automatically open Workbook #1 in it's Auto_Open procedure and make sure you code all your macros to access the data on WorkBook#2.

    The easiest way to do this is to define two Object variables as:
    Code:
    Dim Wkb1 as Workbook
    Dim Wkb2 as Workbook
    
    Set Wkb1 = Activeworkbook
    Set Wkb2 = Workbook.Open("C:\Excel\Workbook#2.xls")
    You can then use these variables to control where the work is done.
    I've done this many times and it works just fine. You can modify and test on the workbook on your computer to your hearts content w/o disrupting production. When you are satisfied that your code changes are working correctly just load the new Workbook#1 onto a shared drive where the others can access it. Of course you'll have to do this when it is NOT in use.

    BTW: There are quite a few posts here using this method.

    Hope this is enough to get you started if not post back.
    Thanks for the quick reply, RetiredGeek, it's definitely something I'll look at. The Database manager and I are looking into a few options for setting this kind of thing up.

Tags for this Thread

Posting Permissions

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