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.

Comments

Popular posts from this blog

My home office upgrade wish list.

My home office is almost due an upgrade. I have been holding off until my youngest daughter is out of her cot as then we can finally dispatch the enormous monstrosity of a cot out from the kids bedroom and the drawers that are in my office can be banished giving me better access to my wonderful whiteboard. My other improvements will be purchasing a new, larger monitor. I currently work from a single 22ich Samsung which just doesn't cut it anymore, I did have two at some point but I can't recall what I did with it. I really enjoy using a touch screen so I think I will go for one of these 27inch Hannspree models that I have used before. I put a lot of hours in at home and whilst I have a reasonable chair I still tend to suffer with some back problems, so my next port of call will be to get a Varidesk for home. It works an absolute treat at work and just lets me switch stuff up when I feel like it. they take a reasonable amount of desk space up but I tend to leave my desk fairly

Arduino ethernet shield

My ethernet shield arrived this morning from Hong Kong. Looking forward to making a little Arduino based Web server! The price for the shield was only ?5 on ebay including delivery :-) super cheap considering how much they cost a couple of years ago.

Specflow

After listening to .Net Rocks with Scott Millett this week I felt a renewed enthusiasm for trying out some BDD. I downloaded Specflow and got straight on with the screen cast they have on their website. The video acts as a good introduction into how to get up and running in Specflow. Interestingly it also gave me a better insight into how bowling works. I have never really thought about it. I normally just wang the balls down the lane until the game is over! Specflow introduces the idea of writing the specification first. It uses a specific language called Gherkin which comes from Ruby land. You will need NUnit installed as well. An example of it is:  [edit: NUnit is what I have used up to now but Specflow is compatible with other testing frameworks aswell. See the comments section below.] Feature : Passwords In order to have a strong password As a new user or existing user changing my password I need to check if my password is alphanumeric and is greater than 6 characters Scenario