Skip to main content

VBA Data Connection Problems

Although I try and avoid the use of VBA now where I can. It can still come in really handy for the occasional app.
I created a reporting tool that collects stats from various web pages that we have and then displays them in a way that is nice and client friendly. Apart from a couple of niggles it works really well. The biggest problem I find when programming Excel with VBA is that when something changes it can be a bit of a pain to make the changes in VBA to cope with it (such as a design change in the spreadsheet, moving of columns ect.. ).
We are moving over to a new project management / development tool at the moment and as a result my report has had to change what data it collects and where from. Whilst making the change I realised that I had made a foolish error and was not properley cleaning up my data connections when finishing the update (not to mention a couple of other problems). I had been deleting the connections but not the named ranges when doing the tidy up. Anyway, in case anybody else has had this problem something like this should do the trick in the future...



First you delete the connections to the various sites / databases that you have, then you delete the named ranges you have. Obviously if there are named ranges you have that you don't want to be deleted you would need to put in the for next an if statement to step over the ones you want to keep.

Labels:

Comments

Post a Comment

Popular posts from this blog

Creating star ratings in HTML and Javascript

I'd searched around a little for some shortcuts to help in doing this but I couldn't find anything satisfactory that included the ability to pull the rating off again for saving. I'd ended up coming up with this rather cheeky solution. Hopefully it helps you too! This is my first post in a while (I stopped blogging properly about 8 years ago!) It's strange coming back to it. Blogger feels very crusty and old by todays standards too.
Post a Comment
Read more

Make your objects immutable by default

More about the Good Dojo In my post last week , I discussed creating objects that are instantiated safely. Please go back and read if you are interested. At the end of the post, I mentioned that I'd also written the class so it was immutable when instantiated. This is important!!! I feel like a broken record in repeating this but I am sure at the time of writing your code, you aren't modifying your object all over the place and so are safe in the belief that protecting against mutability is overkill. Please remember though, your code could be around for a hell of a long time. You aren't writing your code for now... you are writing for the next fool that comes along (including you) . Nothing is more upsetting that coming back to fix a bug on some wonderfully crafted code to say "Who has butchered my code?!", but often you were involved at the start of the process. You made the code easy to modify, allowing objects to be used / reused / modified without thi
Post a Comment
Read more

An instantiated object should be "ok"

I've been QA'ing quite a bit of work recently and one common theme I've noticed across both Java and C# projects I have been looking at is that we occasionally open ourselves up unessacarily to Exceptions by the way objects are being created. My general rule of thumb (which I have seen mentioned in a Pluralsight video recently but also always re-iterate in various Robust Software talks I have done) is that you shouldn't be able to create an object and then call a method or access a property that then throws an exception. At worst, it should return null (I'm not going to moan about that now). I've created an example below. We have two Dojos, one is good and one is bad. The bad dojo looks very familiar though. It's a little class written in the style that seems often encouraged. In fact, many classes start life as something like this. Then as years go on, you and other colleagues add more features to the class and it's instantiation becomes a second
Post a Comment
Read more