Solved: Only running MyExcel.xls on named machine?

[Simon Lloyd]

So does that mean that anything in A100 will be overwritten? maybe i should choose something out of the used range like 50000 as the largest sheet has 46,000 rows used.

Regards,
Simon

[Bob Phillips]

RE this point. Bob was just pointing out that the method John used will not work in 2007. It will still work fine in this case. The reason though, is just to get us in the habit of using methods that will work now and forward. There's nothing worse than upgrading to a new version, and finding out that the code broke. Just imagine trying to explain all of this to someone in a help forum 3 years from now. John's code will be a one timer, but it's still good practice to think of the future anyway.

Worse than that, some customer calls you up about some code that you wrote 3 years ago andd complains it doesn't work, and expect you to fix it under 'warranty'.

[Simon Lloyd]

Sounds Like you have been there Xld!

As for my last post i'm afraid i was being a bit dim.....A100 is on the welcome (Prompt) sheet

Regards,
Simon

[johnske]

There was actually a very similar question posted a few days ago about copying code to a large number of workbooks but I was too lazy to write any code for it and naturally replied along the lines "don't be lazy, it's easier to just open all the books and paste the new code..." - but in the face of two very similar requests in such a short time I thort "damn, looks like it's about time to get off my bum and do it"

[Ken Puls]

Worse than that, some customer calls you up about some code that you wrote 3 years ago andd complains it doesn't work, and expect you to fix it under 'warranty'.

Another good point, Bob.

Simon, re database protection. In Access, you need to:
-Open the database using File|Open
-When you select the file, before you say okay, change the option options (bottom left in 2003) to "Open Exclusive"
-Go to Tools|Security|Database password and set it there

Now, in Control.xla, go into the clsDBLink class module and replace the connection string in the dbConnectStr property with this:

[vba] dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbpath & _
";Jet OLEDBatabase Password=MyPassword"[/vba]

-Change "MyPassword" to the new password.
-Save the .xla project from the VBE.

Should be good to go then.

Also, make sure you protect the project on your xla and your workbooks. You don't want anyone sleuthing them, but make sure you share the password with your manager.

Cheers!

[Simon Lloyd]

Well Ken, i've been testing this finished product out at home (using 3 different drives) and the Control.xla is very efficient at checking every .xls that is opened regardless of drive!, only one snag.........if i remove any .xls and lets say copy it to another computer i get a pop up telling me that F:\Control.xls cannot be found blah blah! when i check the xls i moved i find that in the Add-In's Control is still checked, now the .xls that i moved is not in my list in Control.xla is there a way that if it does not exist in the .xla to uncheck the Control Add-in using the Control.xla authentication?

Other than that it works like a dream!

Regards,
Simon

Sorry for the delay in replying my broadband connection seems to have an authentication problem so i had to wait until i was at work!

[Ken Puls]

Err... can you explain that a little more slowly? Pay careful attention to the .xls and .xla extensions too.

Re the checking, you bet. The .xla will check every file that is opened in Excel. Doesn't matter what drive it's from. )

[mvidas]

Sounds to me like maybe the file being copied/saved has a reference set in VBA to your addin? You'd get the "control.xla cannot be found" message if so... just a thought.
Matt

[Ken Puls]

Hmmm... shouldn't be. Never set one that I'm aware of...

[mvidas]

I couldnt imagine why it would be, but who knows. Using a formula in the addin wouldnt do it either; though if it was linked (=[control.xla]sheet1!a100 ?) it would give at least a similar message..

[Ken Puls]

I'm curious on this... seems very strange.

Simon, if you can describe what you do to replicate the issue, step by step, and the exact verbiage, that would be great.

Also, go into the VBE for the workbook, and see if you have anything in a References folder for the project. Check range A100 to see what it says as well.

If you can't find anything, could you email me a copy of your workbook? I'll PM you my email address.

Cheers!

[Ken Puls]

Hey Simon, let's bring this back public now...

The file setup works fine if a user copies the workbook and takes it home. It can't find the addin, so bails out, giving the user a message. It also works nicely on the network when you open the workbook. It installs the addin and regsiters it for future use.

The issue is caused in the following situation:
-User connects to the network and successfully registers the addin
-User disconnects from the network
-User opens Excel and gets a nice little message that the xla cannot be found.

Being that they have users with latops that are transient, this is definately an issue. We don't really want to a) tell the users where the addin is, or b) cause an error to occur on the users machine.

Excel doesn't have any built in way to remove an addin from the collection, although I think it could be unloaded. The question is what to do here. I haven't played with the following situation to test it yet:
-Load and register the addin
-Uncheck the addin
-Remove the addin from the system (leaving it registered)
-Try and trap an error if the user opens the workbook remotely.

I'm hypotesizing that it may be best to, upon any workbook close, check if any of the files are open, and if not, uninstall the addin. It will be reinstalled via the workbook next time one is opened. The issue, of course, is the "BeforeClose" portion could be triggered then cancelled.

Thoughts, anyone?

[mvidas]

I'll admit I haven't read through every post here, but based on the last one..
You could always (using on error resume next) set the .installed property to false of the addin, then if you wanted you could even remove it from the list via the registry
hkcu\software\microsoft\office\x.x\excel\add-in manager
At least in my version (9.0 / 2000) they are listed there. It looks like there are some user-specific ones too though, not quite as easy. I believe the ones there are the non-installed ones, and the installed are listed under software\microsoft\office\x.x\excel\options with a name of OPEN to OPENn (ie OPEN1, OPEN2). I also believe these get updated when excel opens/closes, so you might have to look under \options

Though I suppose these are just rambling thoughts, take it at face value

[Simon Lloyd]

Well Guy's as you know this is way way above my head....i did kind of think that if the workbook opened isnt on my list in Control.xla then dont install the add in....if it is installed uninstall it or unload it!, i did try recording a macro where i unchecked the add in and then removed it but it showed up nothing in the VBE!

Would the installation/unloading an add in every workbook open cause problems with the network?

Regards,
Simon

[Ken Puls]

Okay, I think I've got this licked...

I've added a test in the Control.xla file that uninstalls the add-in (but does not unregister it) every time the add-in is closed. Normally this is an issue, as it can be triggered by the user closing Excel, but when they cancel it leaves Excel open... but the add-in uninstalled. The thing is, with your setup though, that they can't open a workbook without installing the add-in. This means that if they have one open, they were already authorised. If they don't, it will check again to install the add-in.

Now, while the uninstall happens, the unregister doesn't. This isn't an issue, though, as it only throws an error when the add-in is loaded. Because our regular workbooks all have code to test for the existence of the add-in before it's opened, though, we won't get that error as we've intercepted it.

For any other users who happen along, I've attached a zip file with updated files as much as I did them, (database, xla and xls files,) although still not with John's "force macros" code inside. The add-in path and database paths need to be changed in the xls and xla files respectively.

So basically, Simon, unzip this file and use the new Control.xla (update the db path.) You should be set to go. Give it some tests and let me know.

The only time that I can see an issue happening is if the user has Excel open when they disconnect from the network. At that point they'll get the error. Monitoring that event, however, is going to need a VB coder and, most likely, a separate app that you'd need to install. (i.e. I don't think it's really feasible.)

[Simon Lloyd]

Ken i take it you hate being beat!, you have worked tirelessly on this, and to say i appreciate it is an understatement!.

I have downloaded the file and will try it later, right now its 07:30am and its bed time!

Will post back soon.

Kind regards,
Simon

[Ken Puls]

Ken i take it you hate being beat!

That's probably a pretty accurate statement.