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

NESTA - Next Gen.

via nesta.org.uk Following on from an article on the BBC about Raspberry Pi, this next gen report has some interesting findings. The scariest stat which I picked out from the BBC website was "out of the 28,767 teachers who were awarded Qualified Teacher Status... in 2010, only three qualified in computing or computing science as their primary qualification" Having worked as a computer science teacher for a year in a school that was a specialist in Computing I can concur that the uptake in Comp Sci was woeful. 2 Students for A2... The other teachers backgrounds in Computer Science was also fairly woeful (most knowing a bit about Office but still a paltry amount even about that). I couldn't speak for my counterpart that I was covering however. I suspect they were fairly up on things. All in all what kills me is that Computer science is not a secondary level subject. Areas are often covered, a little in IT, a little in DT subjects (if kids choose Systems and Contr

Accessing the UI Thread with Tasks in F#

I have a Windows Forms program written in F# that can deploy a code base to n number of sites at once (you select the sites you would like to deploy to and it goes off and completes a number of tasks (backing up current sites, various unpacking and moving of files etc... ). Once you start it, it begins it's merry journey and begins to update the UI with what has happened. At the moment this method of updating the UI is not pretty because the threads I am doing the work on can't update the UI so I perform some fiendery to make that happen (don't ask). I knew there was a better way using some newer .NET features but I just hadn't got round to having a fiddle yet. I have now found that if you use the built in Task class but break your code up in a nicer way and then chain the tasks together you can then pass the correct context into the task that you want to talk to the UI. Here's a little script to give you a feel for it. You can press the "start" butt

Reigniting posterous again perhaps

I'm taking another look at Posterous again. I'm not really sure why as I haven't written a proper blog for a while but this seems an easy way to share between all my other tings...