Category Archives: MS-Office

Is Windows getting too net-centric?

Searching Microsoft Office onlineIs Windows (and Office) getting too net-centric?

Case 1: Printer drivers

I hooked up my Lexmark E322 printer to my new computer. Windows XP recognised it, then wanted to go out onto the Internet to get the driver. But the computer’s not online yet. The XP CD apparently doesn’t have the driver. I suppose I could use a separate (online) computer to go to Lexmark’s web site and find a driver, but isn’t that over-complicating things? If XP knows what the printer is, why doesn’t it have the driver on the disc?

(Hey, here’s the web page for Lexmark’s E322 drivers. Someone please tell me it’s some kind of sick joke having three URLs embedded in one like that.)

Case 2: Office 2003 help

To take a theoretical example, search in Word Help for mail merge. It searches Office Online, then presents me with some options. The most useful one turns out to be on their web site.

Obviously having a lot of this content online is beneficial in reducing what is installed on local machines, and even the size of install packages on CDs. It also lets the vendors easily keep software and content up to date.

But… What if my network’s down for the day? What if I’m in a corporate environment and haven’t been granted Net access through the firewall? What if I’m setting up a PC for my mother to use just for word processing, with strictly no Net access?

Today I can re-install and use old versions of Windows, including printer drivers and application help, without network connectivity. Will the same be said for Windows XP in ten years? What if Microsoft drops support for it, including their online driver library? Will Office 2003 still have help available at the end of this decade?

Of course it’s not possible to keep users’ CDs or computers updated with the latest drivers and help files, but shouldn’t at least a basic version of these essential materials be available without network connectivity?

PS. Even after I did get the PC connected to the Innanet, when it tried to go get the driver by itself, it couldn’t find it. So I’ll be downloading it from Lexmark after all.

Creating unique reference IDs in Excel

I often need to create unique IDs in an Excel spreadsheet for importing into our database system. Doing this by hand is time-consuming and error-prone, so here is a method I’ve found which saves a lot of time and effort.

Let’s say I want to create an ID in the format “IMPORTA-1234”, where 1234 is a unique number from 1 to the total number of records in the spreadsheet. However, I would also like them neatly formatted (purist that I am), so that record 5 is shown as IMPORTA-0005 not IMPORTA-5, record 124 is shown as IMPORTA-0124 not IMPORTA-124, and so on.

1. Create a blank column in your spreadsheet, if one doesn’t already exist, for the ID. Call it something like ImportID or UniqueID.

2. Enter the following formula into the first data row (probably row 2 of the spreadsheet):
=CONCATENATE("IMPORTA-",LEFT("0000",4-LEN(ROW(A2)-1)),ROW(A2)-1)
(where A2 is the reference of the cell the formula is in)

3. Now copy and paste this into each row – being a relative formula ensures that each ROW references the correct cell.

What does this do? Let’s look at each part of the formula in turn:

CONCATENATE – This creates one string comprising the three distinct elements needed to form a unique ID.

  • Element 1 is the static “IMPORTA-” string.
  • Element 2 is the appropriate number of ‘0’ characters to pad out shorter numbers.
  • Element 3 is (row number minus one) which gives a unique number based on the record’s position in the spreadsheet (and accounting for a header row)

To ensure that the correct number of zeroes are used as padding, we use the LEFT function to grab a portion of the string “0000”. We use the ROW(cell reference)-1 function to get the unique number, and we use 4-LEN(ROW(cell reference)-1) to work out how many characters long that number is.

Let’s walk through a couple of examples to show how this works:

For row number 25, which is record 24:

  • First part of string is “IMPORTA-“
  • We now use LEFT(“0000”,4-LEN(ROW(A25)-1)).
    The length of ROW(A25)-1 is 2 (24 is two characters long)
    The (4-2) left most characters of “0000” are “00”
  • Final part of the string is the (row number – 1) = 24

Output ID = IMPORTA-0024

For row number 164, which is record 163:

  • First part of string is “IMPORTA-“
  • We now use LEFT(“0000”,4-LEN(ROW(A164)-1)).
    The length of ROW(A164)-1 is 3 (163 is three characters long)
    The (4-3) left most character of “0000” is “0”
  • Final part of the string is the (row number – 1) = 163

Output ID = IMPORTA-0163

and so on.

Once the spreadsheet is complete (no more records are to be inserted or deleted) it may be a good idea to ‘lock down’ the IDs by highlighing all the ID cells, copying them into the clipboard, and then doing a ‘Paste Special’ specifying to paste the actual values. This removes the formulae and makes the IDs static, but still unique of course.

To adapt for longer or shorter numbers, change the string “0000” and the number that the row length is subtracted from (in this case 4). For records from 001 to 999, use "000" and 3-LEN(ROW(reference); for records from 00001 to 99999, use "00000" and 5-LEN(ROW(reference), and so on.

Inaccuracies reading Excel via ADO

Yesterday’s discovery with reading Excel via ADO and the ODBC Text Driver: numeric values may be wrong if it takes a guess that the column contains date fields.

The geeky detail (found in VB6SP5, Excel 2003, ADO 2.7, Jet Provider 4.0):

The text driver looks at the first few lines (configurable via the MaxScanRows setting or the Rows To Scan field in the DSN configuration box) to figure out what sort of data it is dealing with in each column. If there are numbers, it assumes the columns are numbers.

But if it sees dates, and sees no other data in the next few rows, then it assumes all the data in those columns are dates. If it gets further down to rows containing actual numbers, it still thinks it’s reading dates, and when it tries to convert the number to a date and back again, it causes a rounding error and ends up adding 1 to the final number.

In my case, the solution was to get the spreadsheets to contain zeroes (quite valid for the data being loaded) in the first few rows, instead of blanks. A little tricky, but it remains a good method for getting data out of Excel.

How to get rid of the damn change tracking in MS Word

MS Word's change trackingI don’t like Word’s change tracking. Never have. I suppose it’s useful in some circumstances, but almost every document I’ve come across that had it turned on proved it to be a symptom of self-importance on the part of the author.

Maybe my dislike of it is partially fuelled by the fact that I don’t know how to work it properly. It’s irritating to open a document and have to continually turn off the View Markup just to hide all the colourful lines and balloons that otherwise display. And it bloody turns itself back on every time you open the document again.

The way to permanently hide it all is to approve all the changes, something that can apparently only be done by showing the Reviewer toolbar. And my problem is that on the occasions I encounter markup all over the place and I want to get rid of it, I can never remember how it’s done.

This article goes into plenty of detail. But in summary:

  • View / Toolbars / Reviewing: turn it on
  • On the toolbar look for tick icon (Accept change)
  • On its dropdown, choose Accept All Changes In Document

There, finally got rid of the bastards.

Outlook won’t undo

One of my long-term Outlook gripes: Undo can catch a lot of things, but one of the things I use it most for is when I’ve changed my mind about deleting an email, so I want it back out of the Deleted Items. But it only works if you deleted from the folder, not from within the email itself, which then returns you to the folder. Would it be that hard for the logic to say “hey, he pressed undo, he must mean undo the last action” (well duh) “so I’ll undo the mail delete he just did.”

Office Object Library problems

One of the machines a program of mine runs on is still NT4 and Office 97. It seemed to keep working okay when I was on Win2K/Office 2000, but now I’m on WinXP/Office 2003 it’s crashing when calling the Excel object library to open an XLS.

MS have documented that this can happen after re-compiling with the Office 2000 libraries or later (though I’m sure it didn’t happen with Excel 2000).

“This behavior is by design.” Application errors by design. Yeah. SURE.

Excel application error

Apparently the solution is to use that normally-considered-evil late-binding; DIMming as an Object and using CreateObject.

But it didn’t seem to work for me. So I dug out an old copy of Office 97, installed it into a separate directory to Office 2003. Removed the reference to Excel 11, added Excel 8 instead (EXCEL8.OLB), recompiled and all is well.

Excel to VB bug?

From my limited testing (I had to find a workaround pronto) seems to be some kind of bug when using VB6, ADO 2.7, ODBC text driver to read from an Excel spreadsheet. Recordsets with date fields come in with the dates encoded as integers. This is normally no problem, as you can use IsDate to check if it’s valid, then CDate to convert it to a date.

But what I’ve found is that IsDate has stopped working… that is, it’s stopped returning True for field values that can be converted to dates, eg CDate works. Whether this is something to do with the new year, or a new version of something on my machine, I haven’t yet figured out, but I ended up writing a wrapper IsDate function that just tries the CDate regardless, and returns a False (eg not a date) if it errors.

An initial dig in the MSKB found nothing about it, but I’ll do some more exhaustive digging and try to get a definite answer later. The answer being, I suspect, “Switch to VB.Net, you luddite!”

Two Office snippets

I reckon it’s about time Word (and other Office products) figured out that those last few paragraph breaks at the ends of documents don’t need to be printed, especially if they cause an extra blank page to be wasted.

Yesterday I was trying to work out how to tell if an Excel cell has a value or a formula in it, so some conditional formatting could indicate if a value was calculated or entered by a human. Seems to be no built-in function to do it. But this seems to work, though I’m still trying to figure out how, precisely. The explanation at the bottom more-or-less explains it, but damn Excel 2003’s help, which has no index, doesn’t integrate its VBA help into VB6, seems to download everything (maybe it’s just the way I have it configured) and makes it near impossible to jump straight to the definition of GET and CELL.

Empty recordset from MDB queries via ADO

Every so often I’ll find the solution to an obscure issue, and wonder “why isn’t this in the MSKB? Why has nobody written an explanation I could find in Google?” Well through this site, now I can do something about that.

If you call an Access (Jet 4.0) query via ADO/MDAC, you may get empty recordsets back if the query uses parameters. There’s a bug that Microsoft reckons applies to RDO calls, but also appears to affect ADO/MDAC 2.7 in the same way.

To fix it you need to define the parameter size to an arbitrary amount before you set the parameter. Here’s some hopefully useful (to somebody, somewhere, some day) sample code…

(This is VB6. Dim everything beforehand of course, ‘cos everybody loves early-binding. What, you want to play with fire by using late-binding and no Option Explicit? Silly you.)

Set oCommand = New ADODB.Command
oCommand.ActiveConnection = gcDatabase.Connection
oCommand.CommandText = "select * from MyQuery"

Set oParam = New ADODB.Parameter
oParam.Size = 255 'Take this out and you get an empty recordset back, but no error. Wacky.
oParam.value = "your value"
oParam.Name = "your parameter name"
oParam.Type = adYourFieldType
oCommand.Parameters.Append oParam

Set oRS = New ADODB.Recordset
oRS.Open oCommand, adOpenForwardOnly, adLockReadOnly

(and close and destroy everything when you’re done, natch. You know what VB6’s garbage collection is like.)

Service packs

Here’s Microsoft on why service packs are better than patches (as well as explaining their meanings for: Product family, Product, Version, Service pack, Patch).

They don’t really clarify Service Pack vs Service Release, claiming it’s the same thing, though at one stage it seemed that an SP is cumulative, whereas SRs often require you to install them consecutively to be up-to-date, eg Office 97 SR1, then SR2b.

This theory is broken with Visio 2000 SP2, which requires SR1 before you install it. Helpfully, SP2 is available for download, but SR1 isn’t! Brilliant! I have a vague feeling that vanilla Visio 2000 was never available for retail sale, but it’s certainly found its way into a few enterprises (such as where I work), so some people are bound to need SR1. But no. Obviously it was taking up too much valuable disk space on the Microsoft servers.