VB Access Excel

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:

http://support.microsoft.com/kb/q178510/

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?

Cheers

Simon

Open Office 2.2 released

http://www.desktoplinux.com/news/NS3561829136.html

I’ve been using the dev version of this for a while with no problems.

And whilst on the subject Gnumeric had a release in March too:

http://www.gnome.org/projects/gnumeric/

Cheers

Simon

Linux on Dells

http://www.theregister.co.uk/2007/03/29/dell_linux_systems/

Dell are going to start selling pc’s with Linux preinstalled. This could be an interesting trend.

I bought a sony vaio last year to use with Linux, but it proved to be such a PITA (the sony not Linux) that I went back to Windows, and barely use it. It came with a ton of pointless crapware, no install media and just a recovery partition. That meant every re-install brought back all the crapware. I’ll make sure any future PCs I buy are better set up than the Sony. If I get time I’ll rebuild it properly, but as Sony don’t seem too keen to provide Linux drivers, it will probably stay as a Windows (XP) machine.

It sounds like my next pc may be a Dell. The plan is to run Linux and then layer Windows on top using vmWare. I’ve also got half an eye out for the rumoured new Mac sub-notebook.

Anyone else got any plans to investigate Linux further? (a very easy way to try before comitting is to download a live CD or DVD, these just run from the CD drive, they install nothing, and make no changes. Knoppix is the best known, but ubuntu (and plenty of others) do them too).

Has anyone tried crossover office?

On a related note did you know the next version of Office for Macs (due 2007/8) won’t have VBA? AFAIK there are no similar plans for Office for Windows.

Cheers

Simon

Office 2007 compatibility packs for 2003

Has anyone installed and tried these?

http://office.microsoft.com/en-us/products/HA101686761033.aspx

I am tempted (just in case I have a spurt of 2007 project offers!), but I wanted to check there is no downside in terms of wrecking my 2003 install?

I’ve been caught out with some Outlook Express bugs since I installed 2007 on one pc, so I’m not keen to put it on my main one. Anyone got any thoughts/experiences?

cheers

simon

Reality Check

 

I use Jobserve (UK) as my reality check for technologies, I’ve been burned too many times learning useless stuff.

So now and again I look to see whats hot. This is the first time I have ever seen a VSTO job, and the technology is 4 years old. I presented about VSTO and Excel in VS2003 and VS2005RC1 at a Microsoft community event way back in 2005. I noted then there seemed to be very few jobs requiring this skill. Now don’t get me wrong, there is lots to love about VSTO from a technology POV, but as a businessman, 1(one) matching job really doesn’t look like a great investment at this time. I know Orcas promises a lot, but when will the demand kick in?

In fact I struggled to find very much at all combining Excel and .net, outside of jobs also requiring investment banking experience, and based in the City.

For comparison there are 123 Excel and VBA jobs listed. (there are 7 Excel and VB6 jobs).

When deciding where to invest your learning time/effort how do you choose?

cheers

Simon

Protection

Its (too) easy to say if it needs protecting you shouldn’t use Excel/VBA. Although I have to confess that is my default view most of the time.

Someone just asked me how to protect a specific Excel/VBA tool of theirs, and ‘use something else’ is probably not overly helpful. The specific issue was how to stop a sensitive pricing model working if taken to a competitor? We are not talking national security at stake, just enough to make copying a not totally trivial exercise.

So here are some of my thoughts, please chip in with your own ideas and experiences. In all cases below there needs to be some separate way of checking the system is in the right place. eg checking the organisation name in the registry, or checking for a specific sentinal registry entry or a (perhaps hidden) file that gets there outside of the normal install. Doing this in something as trivial to crack as VBA is fairly pointless.

1. PED suggests a workbook open password (these are genuinely hard to bypass, in recent (non French) versions), and then a VB6 front loader exe that has the password compiled inside. The exe makes the checks before opening the workbook/add-in. Exes are self registering so this needs no install, VB runtimes are guaranteed to be there from Office 2k onwards. Functional code left in VBA so there is some weakness once the w/b is open.

2. Migrate most functional code to a VB6 dll, then secure that. Downside as with all COM, this will need an install and registry write access.

3. Put the security checks in an xll. Same benefits as the VB dll but needs no install/registry access, downside needs C/C++ skills. You need to put a fair chunk of stuff in the xll otherwise a cracker can just overwrite the validation call with an ok return value.

4. If your clients are in the .net minority then a .net version of one of the above. .net of course is not known for its native intellectual property protection.

Do you have any other/better suggestions, or see weaknesses with those above?

Cheers Simon

Data or logic?

What do you think is the biggest issue with having multiple copies of (probably) similar spreadsheets floating around? (ie versionitis?) (I’m just using that as a normal word now, seeing we don’t know who coined the phrase – if it was you claim it in a comment!)

Is it the data duplication/possible modification or is it having multiple copies of business logic floating around? or the opportunity to use the wrong version? or something else?

I think the data thing is fairly easily solved if the s/s has a reasonable structure. You can just pull in the data as and when needed from some server based moderately reliable source.

The business logic, for example the pricing process, seems a bit harder to resolve. This is the bit that is often in a spreadsheet because its not simple enough for some web service type approach. I have worked on projects (well a project anyway) that used ILog Rules, I remain unconvinced that that would work as a replacement for the stuff I see.

Me, I think its the business logic tied up in the formulas that is the critical thing that would benefit from improved management. In my experience much of the reference data is just a replica (in theory!) of some corporate data source.

What do you think and why?

Cheers Simon

Seattle update

Sorry for not posting for a few days. There was no shortage of internet access, but there was a major shortage of time.

The Microsoft Office Developer Advisory Council event was very interesting and I met some great people. Some were devs in the same areas, some fellow Euspriggers, some Microsofties (mainly Excel team, but also some Ribbon folks) and plenty of others.

They worked us pretty hard at the event but the big blog time killer was going straight to the pub after.

Most of what we saw/discussed is covered by NDAs and other agreements, so I can’t really say much at all. But what I will say is that it is excellent that MS involve the community in this way.

Normal blog service will resume in the next couple of days and I have a ton of stuff to do/post about.

Cheers

simon

Technological influence

Are those people with stronger IT skills more influential in setting corporate IT policy?

What I’m getting at is are developers likely to have a significant say in the company’s tech choices?

What I’m really getting at is that the ribbon seems targeted at lower experience users, yet I would say this group do not have significant power or choice within a co. I know some manager level types have limited skills, but the sharp ones will ask a trusted staffer right? More likely is that the devs select an appropriate tool and the users have to fit in. (I reckon – what do you think?)

I can’t see experienced users going for 2007 as it wastes their investment in learning 95-2003 versions (and before actually). And of course it is less flexible than current versions.

I think that companies currently use Excel partly because there is a large talent pool at all levels, but I think it is the work that the devs are doing at the high end that is creating the lock-in. Most novices could easily move to another (any other) spreadsheet, not so the experienced dev, or our oh-so-clever VBA/ADO/COM/etc Excel based applications. I wonder how hard it would be to port a VSTO app to pure .net plus spreadsheetgear (or whatever), probably easier than moving your average Excel/VBA app. (Assuming you are using VSTO to add lots of features). I would have thought Office 2007 would sell better with more dev features rather than a funny fat inflexible toolbar. Do you? (everytime I look at it I’m reminded of ‘The Emperors new clothes’ – It’s just buttons FFS!!)

So back to the question – in general in most orgs do you think that those people who are acknowledged as ‘gurus’ (at least locally) are likely to have a big influence. At least bigger than any nooBs?

Cheers

Simon

Do we still need office suites?

It seems to me there is enough free stuff around to make it hard to justify MS Office for many people.

For me I only really use Excel and Access, I could get away with notepad or wordpad for word processing, except I use Open Office.

(I notice though there is no wordpad equivalent for spreadsheets.)

So I was wondering does anyone think it would be realistic to do away with MS Office and just cherry pick the components that people need from Open Office and Excel/Word/PowerPoint? Would it be worth doing? I guess Office pricing means if you buy one product the rest come almost free.

The reason I ask is that I just can’t see many orgs moving to 2007 because of the incompatible UI, but some of they may want to move somewhere before the next MS Office. Where do you think they will go?

Is MS Office safe or are there realistic alternatives?

Note I didn’t discuss Linux and Mac options, but I have already said I think Excel/VBA is what keeps people on Windows.

(btw I have plenty of internet access so should be able to keep up the post rate)

cheers

Simon