I’ve been helping another dev hit a deadline last week.
I fleshed out a VB6 exe form from a prototype he had done. I wrote a class for the client dll to access local and server based .mdbs.
I used Access’s QBE grid to generate the SQL, then stuck it in the class (the db is rebuilt frequently so we decided to leave the SQL in the app rather than the db). I wonder why I feel bad about not keeping the queries in the db? its only like LINQ after all.
I then dropped the recordset into Excel and created a half decent graph. Getting the recordset in took less than 5 minutes, coding the graph took much longer. Normally I just set one up in advance and populate the worksheet then point the graph at the data. To save install hassles in this case I decided to code it from scratch. For me that means hours of tidying up macro recorder code. (which is still better than manually coding it).
I did finally manage to get rid of all those select/selections and get a reasonable chart. But did manage to pick up a dangling reference problem. First time through everything worked great, next time all sorts of things crashed. I’ve had this problem before, although difficult to find they are pretty obvious once found.
In simple terms when automating Excel all objects need to be fully qualified, I’d managed to let one ‘Range(..)’ slip through from the recorder. I changed it to ‘ws.Range(..)’ and was back in business. The full story is here:
I then emailed my 3 bits of code to my fellow dev , he spent less than 5 mins integrating it and everything was working.
This mini project really brought home to me (again) just how powerful and flexible MS Office is. And also how easy it is to dip in and out of VB6. Another thing was how easy the integration was, I have always found multiple devs in Excel to be a real pain. (has anyone found a decent way to resolve this?)
I saw a post elsewhere that Outlook is the big reason people stay with MS Office, personally though I am convinced its VBA and the easy interoperation of all these technologies. What do you think?