Page 1 of 5 123 ... LastLast
Results 1 to 15 of 75
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    "global" workbook accessed by another workbook.

    I have a situation where sales reps have a complicated commission structure that is calculated in an Excel workbook. The workbook does all the work based on one of the tabs which holds corporate data to feed the calculations. That sheet is inserted into the commission workbook weekly by someone in the finance department, then she runs the commissions for each rep.

    They have a web-based portal which allows each rep can log in and enter into a private rep-based folder on the server. The individual folders contain various files that are specific to ONLY that rep (e.g., some PDF files, some Word docs, etc.). The company wants to put the commission workbook into each rep folder so that the rep personally can run his or her commission statement whenever desired. To make matters even more complicated, they would like to generate the corporate data file DAILY rather than weekly so a rep can see commissions as they are going forward daily before the corp. finance dept runs the weekly report for each rep,

    There is no problem providing each rep with a secured workbook to calculate the personal commissions for only that rep. However, it requires the corporate data tab to be in the workbook.

    The structure now would mean the finance person would have to upload (FTP, for example) a new corporate file daily for EACH rep's folder.

    So, you can picture the pain here let alone the potential issues going forward.

    IS THERE A WAY to have a web-based Excel workbook as a "global" workbook that the current (revised for each rep) commission workbook can read to generate the commissions and enable the finance person to upload the file only once to one location on their server? They run Excel 2010. Would they have to switch to 2013? Would they have to have the commission program rewritten in something other than Excel to pull the data from a database?

    ???

    Thanks for any ideas!!

    /Kevin

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Kevin

    Perhaps the commission workbook could use vba (on workbook-open) to 'check for updates' by referencing a specific corporate file in a specified location.
    The commission workbook would store the filename and datestamp of the corporate file that was last used as the source for importing data to a specified tab in the commission workbook (so, if the corporate file 'date and timestamp' was newer, it would re-import the latest data to 'refresh' a specified sheet).

    zeddy

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Sounds much easier than it might be to implement...especially by me with very limited VBA skills. The concept seems logical.
    How does the commission workbook get the single, daily copy of the corporate workbook inserted as a tab in each rep folder?

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

    The following code placed in the ThisWorkbook VBA module will do the trick of updating the corporate data tab each time the workbook is opened by each salesperson. Please note that the corp data file must be in a location accessible by all sales people.

    Code:
    Option Explicit
    
    Private Sub Workbook_Open()
    
       Dim wkbDest   As Workbook
       Dim wkbSrc    As Workbook
       Dim iCntr     As Integer
       Dim zFileSpec As String
       Dim zTabName  As String
       
       '*** Replace string below with the location\FileName of your corp data ***
       zFileSpec = "\\MYBOOKLIVE\CMShared\TestCode\CorpCommData.xlsx"
       '*** Replace the string below with the Tab Name of your corp date      ***
       zTabName = "CorpTab"
       
       Set wkbDest = ActiveWorkbook
       
       On Error Resume Next
       Application.DisplayAlerts = False
       Set wkbSrc = Workbooks.Open(Filename:=zFileSpec)
       Application.DisplayAlerts = True
       On Error GoTo 0
       
       If wkbSrc Is Nothing Then
         MsgBox "Current Corporate Commission Data is not accessable!" & vbCrLf & _
                vbCrLf & "You may continue using the previous data or" & vbCrLf & _
                "return at a later time.", _
                vbOKOnly + vbInformation, "Error: Corporate data unavailable!"
       Else
        
        For iCntr = 1 To wkbDest.Sheets.Count
           If wkbDest.Sheets(iCntr).Name = zTabName Then
             Application.DisplayAlerts = False
             wkbDest.Sheets(zTabName).Delete
             Application.DisplayAlerts = True
             Exit For
           End If
        Next iCntr
        
        wkbSrc.Sheets(zTabName).Copy After:=wkbDest.Sheets(wkbDest.Sheets.Count)
        wkbSrc.Close
        
       End If
      
    End Sub  'Workbook_Open
    Note: the code is designed to place the corp data at the end (right) of all other tabs.

    If the corp data is not available you'll get this message:
    kevinerror.PNG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RG, I'll give that a try on Monday.

    You said "corporate data tab each time the workbook is opened by each salesperson" but I think you mean (and hope) that the code will update each sales person's workbook by inserting the corporate data workbook at the end of the sales person's workbook.

    What would be the VBA line of code (and placed where) that would make this new tab very hidden?
    When this new tab is inserted in the commission workbook, the rep should not have any access to it other than through the formulas in the workbook. The workbook is protected, so it would have to be unprotected, I assume, before this new tab is inserted, this one then very hidden, then protected again with the same password.
    Last edited by kweaver; 2016-04-10 at 17:20.

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

    Ok, let's try to nail down our jargon so we're working from the same data set.

    When I use the term Corp Data I'm talking about the data that will get loaded into each individual salesperson's workbook.

    I'm assuming, in the code, that there is only one sheet in the Corp Data workbook? If not so it is easily corrected.

    When I say each time it is opened, I mean the individual salesperson's workbook. When each salesperson opens their individual workbook the code will ge fetch the Corp Data and use it to replace the Corp Data currently in the salesperson's workbook with the latest data.

    When you say you want it VERY HIDDEN I'm assuming you mean in the individual salesperson's workbook?

    Moving on to the Protected Workbook problem. Which workbook is protected? The individual salesperson's workbooks or the Corp Data workbook or both?

    Since we're using protected workbooks which will have to be unprotected we'll also have to protect the VBA Project in the individual salesperson's workbooks to keep them from seeing the passwords!

    Awaiting your reply.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Actually, there are 2 sheets in the corporate data workbook (see below). The sheet names will be changed each week.

    If this were live now, the sheet in the global corporate data workbook would be named: Apr152016 (MMMDDYYYY) and per the note below, the other one is Apr152016NA.

    A sales person might open his or her commission workbook several times during the coming week. If the corporate sheets are there, it would be used. They wouldn't be placed there again and again...only one time each week on Friday.

    Ultimately (ideally), the sales person's workbook could have N tabs that are dated and come from corporate. That way, the sales person could look at Apr152016, Apr222016, etc. whenever he feels like it. The sales person would put a date (sheet tab name) in a cell of the commission main sheet which will then look at the right tab on his workbook.

    Very hidden on the sales person's workbook because ALL of the corporate data is there and only the data related to THAT sales person should be accessible. The sales person's workbook is protected so that he cannot get to the corporate data in those tabs in his own workbook or see some of the parameters that are used, depending on which sales person is running the commission workbook. For example, there's a very hidden sheet that contains some info about each sales person like a base salary, commission percent, and some other details, that the commission calculations take into consideration. Hence, the protection.

    I guess the corporate one doesn't need protection since it's appended to the rep's workbook and then that copy is hidden. Other than via program (VBA) control, the rep has no way to get to the corporate file on the server.

    While you're being so generous and helpful, let me throw what I think is minor. There's actually a second tab in the corporate file by the same name as the other except followed by "NA". So, one tab in the corporate file is Apr152016 and the other is Apr152016NA. Both need to be appended to the reps workbook.

    Thanks a GREAT DEAL for your input on this!
    Last edited by kweaver; 2016-04-10 at 18:24.

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

    Ok, here's the modified code that does what I think you want.

    The code will append the 2 tabs found in the current workbook on the server. If the tabs have already been added no import will happen.

    An interesting thing I found out in writing this is you can't xlVeryHidden a sheet by the sheet number you have to use the sheet NAME! Go figure.

    Remember you'll have to Protect the VBA project from viewing, via a password, to keep the users from finding out the password to the corp data file. You'll also need to add the password parameter to the File open command.

    Code:
    Option Explicit
    
    Private Sub Workbook_Open()
    
       Dim wkbDest   As Workbook
       Dim wkbSrc    As Workbook
       Dim iCntr     As Integer
       Dim zFileSpec As String
       Dim zTabName  As String
       
       Application.Calculation = xlCalculationManual
       
       '*** Replace string below with the location\FileName of your corp data ***
       zFileSpec = "\\MYBOOKLIVE\CMShared\TestCode\CorpCommData.xlsx"
       '*** Replace the string below with the Tab Name of your corp date      ***
       
       Set wkbDest = ActiveWorkbook
       
       On Error Resume Next
       Application.DisplayAlerts = False
       Set wkbSrc = Workbooks.Open(Filename:=zFileSpec)  '** add ,Password:=yourpassword in parens **
       Application.DisplayAlerts = True
       On Error GoTo 0
       
       If wkbSrc Is Nothing Then
         MsgBox "Current Corporate Commission Data is not accessable!" & vbCrLf & _
                vbCrLf & "You may continue using the previous data or" & vbCrLf & _
                "return at a later time.", _
                vbOKOnly + vbInformation, "Error: Corporate data unavailable!"
       Else
        
        zTabName = wkbSrc.Sheets(1).Name
        
        For iCntr = 1 To wkbDest.Sheets.Count
           If wkbDest.Sheets(iCntr).Name = zTabName Then
             GoTo AlreadyLoaded
           End If
        Next iCntr
        
        For iCntr = 1 To wkbSrc.Sheets.Count
           zTabName = wkbSrc.Sheets(iCntr).Name '** Note can't hide sheet by number! **
           wkbSrc.Sheets(iCntr).Copy After:=wkbDest.Sheets(wkbDest.Sheets.Count)
           wkbDest.Sheets(zTabName).Visible = xlVeryHidden
        Next iCntr
        
    AlreadyLoaded:
        wkbSrc.Close
        
       End If
      
       Application.Calculation = xlCalculationAutomatic
       
    End Sub  'Workbook_Open
    Kevinsheets.PNG

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  9. The Following User Says Thank You to RetiredGeek For This Useful Post:

    kweaver (2016-04-10)

  10. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Will try Monday. Thanks!!!

  11. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    RG, I ran a test tonight on my own system.

    I put the "corporate" file in the root directory of my domain and the "commission" file in a subfolder.

    I only changed the VBA macro in 2 places according to your notes. Maybe (probably) did something wrong. I assume it has something to do with the root directory? zFileSpec ??

    I didn't get any error message but the two tabs never were entered in the commission file when I opened it on my PC.

    Here's the VBA change:
    Attached Images Attached Images
    Last edited by kweaver; 2016-04-10 at 23:28.

  12. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    I just tried it by copying the CorpCommData.xlsx file to one of my websites and it worked just fine.

    Code:
       zFileSpec = "http://zzzzz.yyyyy.net/xxxxxxx/CorpCommData.xlsx"
    I'll PM you with the actual site data as I don't want to make it public but don't mind if you try using it.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    As you can read from my PM back to you, I see what I did wrong! Really, I'm learning...or at least trying to learn. :-)

    Thanks again for such quick turn-around help that was right on! Where do I send the chocolates?

    /Kevin

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    Glad it works for you. I tried loading the Commissions file up onto my website and after playing with the Trust Center to get rid of the "Locked for Editing" message I could play with the file but I couldn't save it as it said it was Read Only.
    kevinerrormsg.PNG
    Probably the difference between a corporate web portal and my ISP free website.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Something came up today regarding the concern that the 2 sheets inserted upon opening raised some compliant questions.
    So, is there an "on close event" [did I use the right term?] that I can put in that will delete those two added sheets from the rep's workbook when the Excel file is closed?

  16. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Kevin,

    So are you saying that you are NOT going to accumulate the corp data tabs but just load the current 2?

    If so, are you aware that upon deleting them all your formulas that reference them will become INVALID #REF!

    This means that you'll have to write VBA to generate all the formulas on the page each time the workbook is loaded.

    If you are OK with this here's the code you are looking for and it goes into the ThisWorkbook module just like the other code.

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
        Dim zTabName As String
        
        With ActiveWorkbook
           Application.DisplayAlerts = False
           Application.ScreenUpdating = False
           zTabName = .Sheets(.Sheets.Count).Name
           .Sheets(zTabName).Visible = xlSheetVisible
           .Sheets(zTabName).Delete
           zTabName = .Sheets(.Sheets.Count).Name
           .Sheets(zTabName).Visible = xlSheetVisible
           .Sheets(zTabName).Delete
           Application.ScreenUpdating = True
           Application.DisplayAlerts = True
        End With  'ActiveWorkbook
        
    End Sub 'Workbook_BeforeClose
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 5 123 ... 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
  •