bobpeers -> blog

I came across this very strange error message in Microsoft Excel today. The user has a workbook with 114 charts and he seemed to be hitting some hard limit in Excel.

It turns out that if you create a chart with it’s font set to ‘Auto scale’ then instead of using one font it uses 2 ore more and there is a limit of 512 fonts per workbook. The fix is easy, there’s even a script to loop through all the chart objects and change the setting for you.

For the solution see the page on the Microsoft Help and Support.

On Linux I usually use vi as my text editor or, for html work, Bluefish, but on windows these are not available.

While writing a program to import text files into a database I was using notepad to view the files to see how to best import them. At this point I became very frustrated that notepad doesn’t even support line and column counters so I can see where I am in a document (useful when parsing fixed width files).

After a short search I’ve downloaded Notepad++, it’s powerful, fast and best of all GPL licensed! I recommend you ditch notepad and try this instead.

Being one of those people that ‘knows’ about computers I often get asked if I can recommend computers to people.

I always find this very difficult for quite a few reasons.

• If they give me a budget do they actually want to spend up to the budget or do they really want the cheapest possible under the budget. I can always recommend very cheap computers but would they rather spend more and get one that is slightly more ‘future proof’.
• Understanding their priorities is very difficult. Would they rather have more RAM or is a wireless keyboard and mouse more important. If they want wireless, do they want 802.11g or are they moving large files over a network and are prepared to pay for 802.11n? They say they play games but are we talking hardcore gamer or the odd game of Solitaire?

It’s often made worse by the fact they they probably don’t fully understand what they want either, it’s just that I’m always a bit nervous recommending something that they might not approve of after they’ve bought it. In reality I know that this is very unlikely since they are far less critical than I am.

One interesting problem I have right concerns Windows 64 versions. A friend is looking to buy a gaming pc probably with and Intel E8400 processor. Having looked at the website of the company they might buy it from it’s clear that they recommend Windows 64 Bit especially since the default set-up of the computer has a whopping 8GB or RAM!. My problems are:

• With Vista 64 Bit many of his old games will not run at all since they will not be compatible with Vista, plus Vista 64 Bit requires signed drivers so many of his current peripherals just won’t work and new drivers don’t exist (nor will they ever)
• Vista 32 Bit still has compatibility problems plus only supports 4GB of RAM (see here and here for more information).
• In Denmark Windows XP 64 Bit is only available in the Professional Edition localised to the UK, it must be a Danish edition so that won’t work.
• Windows XP Home 32 Bit will work with his current games but only support 4GB of RAM. This is not a major problem but it seems like I’m going for the lowest common denominator.

Overall the situation is very confusing. I would really like to jump on the 64 Bit bandwagon since it’s they way of the future but until you can be sure all your hardware will work happily together.

I don’t want to obsolete all his games and current hardware by upgrading to Vista 64 Bit so I have no choice but to recommend a 7 year old operating system that will be officially discontinued as of June 30, 2008, not an ideal situation.

My girlfriends computer has Windows Vista installed in Danish but has the UK version of Office 2003 Professional so I was rather surprised this week that I couldn’t change the spell check language in Word 2003 to Danish.

I had (rather naively as it turns out) just assumed that something costing as much as Office 2003 would also include a pile of dictionaries for spell checking, how wrong could I be.

To achieve what I wanted I’d actually have to buy Microsoft Office Proofing Tools 2003, which seems hard right now as all the sites I looked at showed it as discontinued!

To put this in context I use OpenOffice on Linux (cost zero) and installing a new dictionary takes me about 1 minutes using yum (cost also zero).

On a similar theme I was recently helping a friend with her computer when she asked me if she should buy Microsoft Word for her pc just so she can read Word attachments that are sent to her. She had very basic requirements and was not at all computer literate.

It struck me that for most people if they are sent a Word document they will of course assume they need Word to view the file, Microsoft Office document formats are so ubiquitous that many people just connect any text file with Word.

The idea that people fork out for Microsoft Word just for this purpose horrifies me when there are many free alternatives available. But of course knowledge is power in my case, many people are not so lucky.

I installed Vista SP1 on my girlfriends computer yesterday (it’s in Danish which wasn’t one of the original release languages, hence the delay) and was disappointed to find that her computers network problem has not been fixed.

After writing about Microsofts problems with selling Vista to businesses it was funny to read this article where Steve Ballmer describes Vista as a ‘work in progress’.

As the article says, every piece of software is actually a work in progress, we all expect new verisons and enhansments to be released, but for such a figure to say this while at the same time trying to convince corporations to embrace Vista it sounds like for a moment he forgot his marketing spiel and the truth slipped out

When writing HTAs to connect to an MS Access database I used to always use VBScript.
The obvious reason for this being that it seemed easier since both are Microsoft technologies plus I initially had no idea how to create or use ActiveXObjects from JavaScript.

After doing this for a while I became very frustrated at the limitations of using VBScript so looked deeper into changing to JavaScript and found that’s it’s actually very easy to use.

As a starter for anyone else in the same position here some framework code showing how to create an ADO connection, open a recordset and execute the command object all using JavaScript.
(I know technically this is JScript, which is Microsofts version of JavaScript)

// path to database
var DBpath="\\\\Server\\Path\\myDB.mdb"

// set up a few object constants
var adLockReadOnly=1
var adOpenForwardOnly=0
var adCmdText=1

// create and open a new connection (MSAccess)
var cnn=new ActiveXObject("ADODB.connection")
cnn.Provider = "Microsoft.Jet.OLEDB.4.0;Data Source=" + DBpath
try
    {
    cnn.open
    }
catch(err)
    {
    // could not open connection
    // view details in err.Description and err.Number
    return 0
    }

//open a read only recordset
var rs = new ActiveXObject("ADODB.Recordset")
try
	{
	rs.Open("Select * from myTable", cnn, adOpenForwardOnly, adLockReadOnly)
	}
catch(err)
	{
	// could not open recordset
	return 0
	}
while(!rs.EOF)
	{
	// do something
	rs.movenext
	}
rs.close

//insert records with command object
var cmd=new ActiveXObject("ADODB.command")
cmd.ActiveConnection = cnn
cmd.CommandText = "Insert into myTables values(x ,y ,z)"
cmd.CommandType=adCmdText
    try
    {
    cmd.Execute()
    }
    catch(err)
    {
    // could not execute SQL
    return 0
    }

If you are using the ADO object to connect to a database within an HTA in a networked environment then you will get a rather annoying message box warning about ActiveXObjects.

To prevent this in all my HTAs I start the script off with the following statements (this is in Javascript).

var objShell = new ActiveXObject("WScript.Shell")
objShell.RegWrite("HKCU\\Software\\Microsoft\\Windows\\CurrentVersion\\Internet Settings\\Zones\\1\\1406", 0, "REG_DWORD")
objShell.RegWrite("HKCU\\Software\\Microsoft\\Windows\\CurrentVersion\\Internet Settings\\ZoneMap\\Domains\\myServer\\file", 2, "REG_DWORD")
objShell=null

The effect of the first RegWrite is to allow ‘Access data sources across domains’ and then the second write adds my network location (myServer) to the trusted sites.

These can be manually changed from within Internet Explorer as they are the zones settings in Internet Options.

Setting these keys requires no admin rights but they may well get overwritten by Group Policy so it’s best just to include them at the start of the script.

Note that the first time the application is run you will still get the warning as it will have read the old values, but by the next run they will be set.

I recently developed an HTA Application written in Javascript that used ADO to retrieve and update data in an MS Access database.

One of the calls tried to open a stored query but failed with the error ‘Unkown function’. It turns out that the query contained the Nz VBA function to convert null values into some other, zero in my case.

This works fine when run from within MS Access but when called externally would fail due to the JET 4.0 database engines sandbox mode.

I could have changed the registry key to allow the function but that would only work on my computer so as a fix I replaced all the references to Nz with IIf statements, not an elegant solution but it works none the less.

After all the testing with creating bootable USB drives and trying many different distributions I guess the inevitable had to happen. At some point in my partitioning/formatting of the USB drive I’ve typed

/sbin/mkfs.vfat -F 32 /dev/sda1

instead of

/sbin/mkfs.vfat -F 32 /dev/sdb1

Only a small error, replacing sdb1 with sda1, but the result is that I reformatted my main Windows XP Professional NTFS partition to FAT32 and in the process lost Windows. I’m not bothered about data loss as I keep anything important in my Linux partition that’s also backed up to a NAS device. What I’m really bothered about is the prospect of reinstalling Windows. In my experience it goes something like this:

1. Boot using my Acer install disk (pre SP1 from 2003).
2. Wait 30 minutes for install to complete.
3. Reboot, listen to annoyingly loud music while setting my computer preferences for the first time.
4. Reboot again.
5. Spend 2 hours (minimum) customizing and uninstalling the crap put on my computer.
6. Go to Windows update and start the painful process of getting a pre SP1 XP Professional computer to SP2. This usually requires 50 to 100 updates along with maybe 3 reboots on the way. Allow at least 3 hours for this with a fast internet connection.

If all goes well the whole process will take an entire evening. Not forgetting that the fresh Windows install will overwrite my mbr so making my dual boot laptop into a single boot Windows only machine requiring me to boot into a live Linux distribution and then installing GRUB on the mbr again. Phew!

Compared to this exercise in time wasting, installing any new Linux distribution these days seems like a walk in the park. I’ll just have to be extra careful in future when formatting drives.

I was writing some VBA code in MS Access today and discovered some strange behaviour when inserting values into a table field when the data type of the field is set to byte (Byte variables are stored as single, unsigned, 8-bit (1-byte) numbers ranging in value from 0-255).

To recreate the problem simply create a new table with one field of type byte, this can be done by running the following SQL statement:

create table test (id byte)

Next run the following code:

insert into test values (1000)

If you look at the value actually inserted into the table it shows the value 232. There is no warning of invalid data type or out of range warnings, it just goes ahead and inserts the result of the VBA Mod function on 1000 mod 256. The Mod function is defined as ‘Divides two numbers and returns only the remainder’. If you try it with the value 513 it will insert the value 1.

It can be of no coincidence that a VBA byte can have a maximum value of 255. The most worrying thing here is that it converts my integer to a byte result and inserts the value without any warning. If you were trapping errors at this point to trying to catch someone inserting an integer or long value into the field then you’ll never know an error has occurred as no error is thrown.

Even more strangely in VBA code if you open a recordset on the table and add an integer using AddNew method then an error IS thrown, but if you execute the equivalent SQL statement using a command object NO error is thrown. The AddNew behaviour is shown below.

Dim rs As New ADODB.recordset
Dim cn As CurrentProject.Connection
With rs
	.Open "select id from test", cn, adOpenForwardOnly
	.AddNew
	.Fields("id")=1000	'throws an error
	.Update
	.Close
End With

The next code using the command object does not throw an error.

Dim cmd As New ADODB.Command
Dim cn As CurrentProject.Connection
With cmd
	.ActiveConnection = cn
	.CommandText="insert into test values (1000)"
	.Execute      'no error
End With

It seems to me that running an SQL statement from either a query window or through the ADODB command object uses some different method whereas the AddNew method bypasses this thereby raising the correct error.

Of course the lesson here is to check your data before trying to insert it into the database rather than catching errors caused by invalid data.