Change xlsm to xlam

[hatman]

So I inheritted a workbook that performs transactions with a central Access Database. The workbook is readonly, and is open at any given time on upwards or 50 computers, all performing read/write operations on teh database. I repaired all of technical issues with teh traffic, timing, database loading, etc.

But I still have issues with the architecture. 1) The original designer placed this workbook in the same place as the database... people open the database by mistake all the time. 2) All of the database write actions are keyed froom the Chenge event of the worksheet... the worksheet is basically a "report" of teh database contents, and when the user changes a value, it writes to the database through the worksheet_change event. 3) Users constantly save copies of the workbook to their desktops... thus excluding themselves from the update loop. 4)users get very confused regarding where the file is located, what file to open, what shortcut to use, blah blah blah.

What I want to do is to re-distribute this as an addin, run from the network drive. I have built code that will do teh install automatically, with several distribution methods. That part is child's play. I have been playing with isolated test workbooks, and have worked out the whole Class Module layout so when a user hits a toolbar button, a new workbook is created, populated with data, and all of the events get trapped in the addin class modules for proper code response. I have built it so that if Excel loses it's staate (all globals get cleared) it gets detected before toolbar sub-routines are executed, and the whole global structure gets re-built, recapturing the existing workbook. But where I hung up is how to handle a user changing a vlaue in teh worksheet of the workbook after a state loss has occured. The event won;t get trapped in the class module of the addin, because the global will have been lost... the user will move along fat dumb and happy assuming that the data wrote to teh database, when in fact it didn;t.

I can't see a way to re-establish the global structure and reconnect to the workbook in any automated way. I would even settle for some way to warn the user that the state has been lost in some useful message that instructs them to press a particular toolbar button to re-create the globals before proceeding. I have considered some sort of polling routine using OnTime to constantly verify that the global objects still exist, and rebuild them if they suddenly disappear... but that seems excessively intensive and probably prone to breakage in some way. I can see a different architecture where the user selects a record, then presses a button to edit it in a dialog bax... but I don;t even want to try to fight the pushback from the user community on that approach.

I think I have hit a major roadblock to prevent me from making this an addin. Does anyone have any advice to overcome this one? I suppose I can leave this as a standard xlsm... but I would like to find a way to make this more professional...

 

[RoryA]

Create a COM add-in if you can (I'm 99% certain the state loss would not be an issue there as long as the error is not in your code). If not, you really ought to require a button press of some kind to write the data back to the database, even if it's just the saving of the workbook.

 

[hatman]

Com Addin, huh. I'll have to look into that... no experience right now.

I agree 100% on a confirm button to drive this. It would soleve a lot. But it would require re-training of the 300 or so users, across several business units who have been using it the way it is for teh last year or so. I'm still exploring this solution, but I am not optimistic in making it fly.

 

[RoryA]

Well, besides timers (or a keyboard hook which I think would be OTT) I can't see any other options, I'm afraid.

 

[hatman]

Thanx for your assessment Rory. Same comclusion I came to, but it's good to know i'm not way off base.

I'm definitely going to look into the .com addin. It's a new architecture for me. I would think 95% of the code should come across from the existing xlsm with some wrappering and exncapsulation to make it work. I need to do some test cases irst, though, and try to avoid taking this is one big gulp...

 

[Jaafar Tribak]

... I have built it so that if Excel loses it's staate (all globals get cleared) it gets detected before toolbar sub-routines are executed, and the whole global structure gets re-built, recapturing the existing workbook. But where I hung up is how to handle a user changing a vlaue in teh worksheet of the workbook after a state loss has occured. The event won;t get trapped in the class module of the addin, because the global will have been lost... the user will move along fat dumb and happy assuming that the data wrote to teh database, when in fact it didn;t.
..

Hi hatman.

If the state loss doesn't affect the Addin have you thought about placing the worksheet_change event code of the target workbook in the addin itself ?

Something along these lines in your target workbook module :

Option Explicit

Private WithEvents TargetWorksheet As Worksheet


Private Sub Workbook_Open()

    Set TargetWorksheet = _
    Workbooks("YourTargetWorkbook.xlsm").Worksheets("YourTargetWorksheet")

End Sub


Private Sub TargetWorksheet_Change(ByVal Target As Range)

    'run your code here

End Sub

You couls also check the state loss of the target workbook from the addin against a remote Boolean flag declared (flag set in the taget workbook) so the whole global structure gets re-built before hand.

 

[hatman]

But the state loss DOES affect the addin. That's the point. When the state is lost, then the addin is no longer able to trap events that get raised in the target workbook. Not until the objects in the addin that point to teh target workbook objects get re-assigned... which I can't figure out how to automate.

As far as putting code in the target workbook, the whole point of this excercise is to remove all code from the target workbook. That workbook, if I can implement this architecture as I hope, will be built on the fly without any code or VBA components. Since all of the data exists in an Access database, the target workbook is nothing more than a glorified user initerface, so it doesn't need to exist as a file by itself, the addin should instantiate a new workbook on command, allow user interaction to the database, then be terminated when the user is done.

 

[hatman]

Hey Jaafar. Just re-reading this post, having gotten home from work, and I realize that my earlier reply sounds a little snotty. I apologize: I didn't mean to come across that way. I welcome your suggestions, I was just somewhat frustrated that I was less clear than I had hoped in my explanation, and disappointed that the solution isn't quite as easy as your response suggested.

 

[Jaafar Tribak]

Hey Jaafar. Just re-reading this post, having gotten home from work, and I realize that my earlier reply sounds a little snotty. I apologize: I didn't mean to come across that way. I welcome your suggestions, I was just somewhat frustrated that I was less clear than I had hoped in my explanation, and disappointed that the solution isn't quite as easy as your response suggested.

No worries hatman. I must have misunderstood the scenario you were describing. I must say I find your posts/questions very interesting and thought provoking especially when Rory or other heavy weight members are also involved

No hard feelings.