Thursday, 27 May 2010

The bad old days of VBA, and opening Word from Excel

You tend to forget how good Visual Studio has become as a development environment, until you jump back into something like writing VBA macros in Excel.

This is pretty much a VB6 environment, but maybe it's just my memory but I didn't think VB6 was as bad as VBA is. Even more amazing is that if you open up Excel 2010 and click on the Visual Basic icon in the Developer tab, nothing has changed. There's good old VBA in all its glory.

Sure, you can use .NET with Excel, but only to write add-ins. These are installed separately, so you can't just send a spreadsheet off to a colleague and expect it to work if they don't also have the add-in installed. Another option would be to write a full-blown .NET application that used COM Interop to call out to Word and Excel.

So what lead me to be messing around in VBA? The scenario involves aggregating data from a large number of spreadsheets into a single Word document. If you're thinking this sounds like something better handled with SharePoint or an intranet web application, you'd be right. Probably the ".NET application with COM Interop" would also have been a better choice,  but for now resource constraints meant that the "improvised" solution was the best that could be done, and as a proof of concept it appears to be doing the job.

Just to be clear, this isn't for a LobsterPot client, rather I'm just helping out a friend. The particular problem I was asked to advise on was that the Excel VBA code was launching Word, but Word would be opened in the background. The request was to make the new Word document appear in front of Excel so that the end-user could more easily see it. Sure, they could Alt-Tab or click on the Windows task bar to bring it into focus, but anything you can do to make things easier and more obvious for users is usually a good thing.

The original code opened Word like this:

Dim wordApplication

Set wordApplication = CreateObject("Word.Application")

This works fine, but Excel (where the VBA code is running from) stays in the foreground. Adding one extra line to the code solves the problem:

Dim wordApplication

Set wordApplication = CreateObject("Word.Application")

Application.ActivateMicrosoftApp xlMicrosoftWord

The call to ActivateMicrosoftApp does the trick. One trap to be aware of is calling that method before CreateObject. That does work, but it results in 2 instances of Word opening, which is usually not desirable.

Tuesday, 25 May 2010

Laptop fixed, passed 70-448, SQL 2008 R2 and a new Lobster in the pot

Some good things have happened in the last 24 hours:

  • The Dell technician fixed my laptop.
  • LobsterPot Solutions welcomed its newest member – Roger Noble! It will be great to work with Roger again. Since leaving UniSA he's been specialising in SharePoint development, which will be a great asset for LobsterPot's Business Intelligence expertise.
  • I attended the Adelaide SQL Server 2008 R2 launch event presented by Microsoft Australia's Alan Eldridge. A nice overview of new features in R2.
  • Next Buck Woody gave a humorous and interesting talk on two new features in SQL 2008 R2 at the SQL User Group meeting:
    • Utility Control Point – managing and monitor multiple instances of SQL Server 2008 R2
    • Data-tier Applications (DAC) – deploying SQL applications. DAC is only for R2 and is distinct from the DataDude deployment technology that also shipped as part of VS2010, but the word on the street is to expect these two to converge in the future.
  • Finally to finish off the day, I passed exam 70-448 - TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance. Yeehaa!

Monday, 24 May 2010

Data mining algorithms in SQL Server 2008

Microsoft SQL Server 2008 Analysis Services ships with 9 built-in algorithms that can be used in data mining solutions. Apparently these algorithms are unchanged in SQL Server 2008 R2.

Here's my own summary of these algorithms, based on their descriptions and detail from the Data Mining Algorithms page in SQL 2008 Books Online.


Useful for generating recommendations. The classic example is people who buy beer and shampoo.


Group similar items together.

Decision Trees

Predicts both discrete and continuous attributes based on relationships. Example is figuring out the common characteristics of old customers that determine whether they are more likely to purchase again.

Linear Regression

Calculates a "line of best fit" for a series of data, and then allows prediction based on that line.

Logistic Regression

Variation of Neural Network, good for yes/no outcomes.

Naive Bayes

Classification algorithm using Bayes theorem. Good for quick results that may then be refined by other algorithms.

Neural Network

Analyse complex relationships with lots of inputs but few outputs.

Sequence Clustering

Finds most common sequences. Good for identifying popular web page site navigation trends on a website.

Time Series

Predict future values of continuous values over time. Forecast next year's sales.

Friday, 21 May 2010

Working with the cloud

So as mentioned yesterday, my laptop is sitting next to me, looking very shiny and pretty, but completely silent. I spoke to Dell support this morning and after managing to exhaust the call centre guy's script of variations on "Have you tried turning it off and on again?", he eventually logged a case for me, and I'm now waiting for a phone call from (presumably the local) support engineer.

One thing I'm not too worried about is the data sitting on the laptop. I know it's there – just a matter of getting a new BIOS (and/or possibly motherboard) and it will be fine. I also know I have a full backup on my Windows Home Server, which is reassuring.

One of the first things I did when I joined LobsterPot was to create a Live Mesh folder to store business-related documents. So that means I've still got access to those documents via the web or my other machine at home – handy as after experimenting with various online solutions, I ended up reverting to using an Excel spreadsheet to track my work hours too!

I've also recently been making use of some of the training content at Microsoft Learning. I download the content to the laptop and have been working my way through it on the bus to and from work (must be riveting for the lucky people who get to sit next to me!). There is an offline reader just for this purpose, though it does have its annoyances (eg. no keyboard shortcuts, doesn't allow text to be enlarged) but one nice thing is that it synchronises your progress. So I can still log into the Microsoft E-Learning site and am able to pick up where I left off, using the "online" mode instead.

Then there's email, IM etc, which all work fine from where ever you are. So, while having a dead laptop is a huge pain, it isn't the end of the world.

Good news.. I just had a call from Dell and they're coming out on Monday. Hope they can fix it quickly.

Thursday, 20 May 2010

One 'bricked' laptop

This evening I thought I'd install the most recent firmware update A09 for my new Dell XPS 1645 laptop. I downloaded the installer and ran it, and it automatically started the update.

Then it got stuck.

I left it for a fair while, but the progress bar was not moving, and then I discovered the mouse and keyboard were also not responding. Holding down the power-button had no effect, so the only way to power off was to remove the battery.

A call to Dell support revealed that as it is a hardware problem I need to talk to their hardware support line which is only open 8am – 8pm (and it was already past 8pm).

So tomorrow morning I'll be on the phone to see what can be done. Very frustrating.

Tuesday, 18 May 2010

SQL Server 2008 R2 Event

To paraphrase Roy and HG, "When too much SQL Server is barely enough"..

If you're interested in seeing some of the new Business Intelligence (BI) features of SQL Server 2008 R2, then you might want to attend one of the free events being held around Australia. The east-coast events are done, but it isn't too late if you're in Adelaide (Tuesday 25th May) or Perth (Wednesday 26th May). Register now via this form.

As an added bonus, for Adelaide attendees you can stay on to catch Buck Woody direct from the USA present on another R2 feature - Data-Tier Application Components at the May meeting of the Adelaide SQL User Group – held for your convenience at the same venue.

See you there!

Saturday, 8 May 2010

Grafx 1.0 for MINIX

Isn't this Internet thing cool! Using Google Group's Usenet archive, I managed to locate the project I completed in the 3rd year of my Bachelor of Applied Science in Computer and Information Science, way back in 1991/92.

My supervisor (Bob Buckley) offered to post the finished source code to the comp.os.minix newsgroup on my behalf (as students didn't have permission to post to newsgroups at that time). Strangely, the older posts in this newsgroup don't appear to be indexed properly by Google (eg. searching for 'gardiner' doesn't return any matches), but for posterity, here are the links to the overview and 4 parts:

Quoting from the introduction text:

The Grafx package gives MINIX the ability to display graphical output.
It also partially implements the UNIX plot(3X) library.
Graphics is driven through the BIOS (with all the associated implications).

Hardware Requirements:
        IBM-PC or BIOS compatible
        Graphics Adaptor - CGA,EGA,VGA,(Hercules Untested)

        Other platforms eg. 68K should be able to use this package
        with a small amount of work.

The documentation is distributed in the following files:

usrdoc.txt      - User documentation - ASCII text
sysdoc.txt      - System documentation - ASCII text

These are wordperfect output - so bold and underline may look odd
on your screen, but should print OK. Mail a request for the WP
files if you want to print with different fonts, etc.

I would welcome your feedback on this package. It was my 3rd year
project, as part of the Computer Studies Degree course at the
University of South Australia.

- Dave Gardiner, 14/2/92
Internet: [email protected]

Historical notes

  • The 4 separate posts are part of a "shar" shell archive – a popular way of sharing scripts and source code in newsgroups.
  • It was in August 1991 that Linus posted to comp.os.minix about a new operating system he was working on (later to become Linux).
  • I'm pretty sure I got a response back from Prof. Andrew Tanenbaum – I can't find anything online so it may have been an email which has since been lost. I believe he encouraged further work on the idea – but unfortunately that never happened.

Friday, 7 May 2010

ReportViewer 2010 remote processing requires RS 2008 or later

I had an idea today that using the newly released ReportViewer 2010 controls might help with an unusual rendering issue we're having with a web page when displaying very large reports.

Turns out that you can use Report Viewer 2010 with Visual Studio 2008, though I did have to copy the files out of the GAC. Make sure you update the assembly references in the project file, web.config and also any .aspx pages so that they refer to Version=

Firing up the site to view one of the reports then resulted in the following error message:

"Remote report processing requires Microsoft SQL Server 2008 Reporting Services or later"

Checking the current MSDN documentation for Configuring ReportViewer for Remote Processing does indeed state that "to use a server report, you must have access to a SQL Server 2008 or later Reporting Services report server". Contrast that with the Visual Studio 2008 version of the same page which says SQL Server 2005 Reporting Services was the minimum.

Unfortunately in this case Reporting Services are still on 2005, but when that changes, using Report Viewer 2010 will be a viable option.

Tuesday, 4 May 2010

User-settable properties in Reporting Services

SQL Server Reporting Services reports have a number of properties that can be obtained via the GetProperties web method.

The full list of properties is documented in SQL Books Online in Report Server Item Properties. What it fails to mention is that some of these items are read-only. Try passing them in via CreateReport or SetProperties and you'll just get an exception for your trouble.

By trial-and-error (mostly error!) against an instance of the SQL 2005 Reporting Service Web Service I discovered that the following properties are user-settable:

Property User-settable
Description Yes
Hidden Yes
ReportProcessingTimeout Yes
CanRunUnattended Yes

Monday, 3 May 2010

CodeCamps and other events

Looking to learn new things, network with colleagues, be inspired and maybe even have a bit of fun? Here's a list I've compiled of upcoming events. I'm aiming to attend and speak at CodeCampSA. I'll have to see whether I get to any of the others!

June 1-2nd

REMIX 10 - Australia's version of Microsoft's MIX conference - which has a particular focus on web design and development.

July 24-25th

Heading into its fourth year, CodeCampSA is on again this July. Whilst it's always been a bit smaller than the Wagga event (see below), it's always really encouraging to see a solid contingent of interstate speakers volunteer their time to come and support us locals to put on a great training weekend.

August 24-27th

Tech-Ed Australia, back again at the Gold Coast Exhibition Centre.

October 9-10th?

SQL Down Under Code Camp is traditionally held on the weekend after the October long weekend. Keep an eye on Greg Low's blog for any announcements.

November 8-11th

I'm told any SQL enthusiast worth their salt will be aiming to attend the 2010 PASS Summit. True, it is in Seattle, but apparently that has the nice side effect of allowing a lot of the Microsoft SQL team to attend and speak too.

November 20-21st

April has traditionally been the time keen .NET developers head to Wagga Wagga for CodeCampOz, but Mitch has announced that Australia's original Code Camp will be held this year in November.

Updated 5th May to add REMIX