Thursday, June 18, 2009

That pesky security alert message for external workbook references

Microsoft gets flak when there are macro virus alerts. Each iteration of Microsoft Office has toughened up default security. Excel 2007 now displays a security alert when you open a workbook which has external workbook references. MSKB 826921 explains the issue and workaround.

Update:

No need to work so hard. There is a deep menu nested way of switching off the alert – thanks to Lilith for telling us.

Office Button > Excel Options > Trust Center > Trust Center Settings > External Content > Enable automatic update for all Workbook Links (not recommended).

Yeah, not recommended but do it anyway – of course you should have a good, up to date, anti-virus system in place and running real time.

Don’t confuse this with:

Data > (Connections Block) Edit Links > Startup Prompt > Don’t display the alert and update links

Thursday, June 11, 2009

Pardon the Range Name

Range Names are a lot of fun. Lots of “A1” cell reference people don’t know about the benefit of using Range Names – but with Excel 2007’s new in-your-face Formula Tab with Name Manager, we hope more people will use this feature.

One wrinkle in an otherwise simple explanation of Range Names, is the scope of the Name. You see, there is a global-to-workbook scope and a local-to-worksheet scope. Here is Simon Hurst’s attempt at explaining it:

Names must be unique within their scope. So you cannot have two identical names in one sheet, but you can set the same name up in different sheets. This could be very

useful – a similar formula on several different sheets could refer to the range name defined for that sheet

You are also meant to be able use the scope setting to define the same name at local worksheet level and global workbook level. If you refer to the name on a sheet where it has been defined for that specific sheet, then the local reference will be used. If you refer to the name on a sheet that doesn't have that name defined locally then the global workbook reference will be used. If you want to refer to the global name from a sheet that has the same name used locally, then you have to prefix the name with the workbook name, for example: ='2007 range names.xlsx'!profit.

Training Matters has an interesting article on effective use of Range Names – and explains how to replace cell references in pre-existing formulae that didn’t use a Range Name previously

The Define Name option includes the Define Name and Apply Name options. Apply Name is unchanged from previous versions of Excel and allows names to be applied to existing formulae where they will replace any direct references to the named cells or ranges.

It is also possible to create dynamic range names. You can use a dynamic formula to define a range. As new items are added, the range will automatically expand.

Templates and the default document

There are good knowledgebase articles for templates used by Microsoft Office 2007 and 2003.

Did you know that instead of a single template location, there are now four? As described in MS KB 924460, they are:

  • The user templates file location
  • The workgroup templates file location
  • The advertised and installed templates file location
  • The non-file-based templates file location

The article is a bit too brief though. It does not explain for example that Excel responds to two global templates – a default template for the new workbook – book.xlt and a default template for the new worksheet – sheet.xlt. This is explained in HP052292861033.

It’s also interesting how the default font and font size in Excel is set. You can directly set it in Office Button > Excel Options in the When Creating New Workbooks section for new workbooks. Or you could go the book.xlt route. Or for the current workbook only, you can modify the Cell Style called Normal.

Numbers as Text in Excel 2007

It’s an old issue. Goes back as far as Lotus 1-2-3. Yes, the classic DOS edition. Maybe even to Visicalc but I’m not sure. These spreadsheet programs of course accept numbers and calculate numbers. Text can be secondary. But what happens when you have to enter leading zeroes in product codes or mobile phone numbers? The leading zero is swallowed up by the numeric expectation.

Excel 2007 has a nice solution. Thing is, it’s not phrased in help in such a way that easily comes to surface. Search Excel Help for Format As Text.

For pro-active work, select a range of cells and apply numeric formatting. Yes, Yes. Numeric formatting. But you nominate the numeric format called Text. Guess what? Not only is the selection formatted as text (e.g. with leading apostrophe) but future data entry into that range will retain the text entry behaviour. So you don’t have precede your intended number with an apostrophe – and your leading zeroes are preserved, of course.

For post processing, your zeroes have already been sucked into the null bucket. So using this technique will not restore them. Use the TEXT function to manufacture the leading zeroes in a new cell.

Tuesday, June 9, 2009

Graphics and Pictures in Access 2007

We sometimes discuss how it’s not a great idea to store BLOBs / digital pictures / graphics within the Access database file itself. The only advantage is that you keep all the (hopefully small) graphic files in one database file and it’s easy to transport the whole thing. The big disadvantage is that Access does not do anything or use anything with those graphic objects – the database engine does not index, search within or recognise faces (for example) within the digital pictures. In previous versions of Access, the situation was even worse, Access would manufacture several bitmap formats as per the OLE specification so a small JPEG picture would bloat up considerably once it was stored as an OLE picture. There are third party solutions but they expect you to licence and install .dlls on the participating machines. Here is an article describing the problems you may face and a comparison of the different techniques.

The redoubtable Larry Linson, and Stephen Lebans have various examples. See Stephen’s Picturebox , LoadJPEGGif webpages. Tony Toews’s famous website seems has an Image Handling webpage with resource links.

Northwind 2000 has an example with the Employees Form. Get it and take it apart.

Access 2007 has a new field type, the Attachment field type, which avoids this bloating, but you still fatten the Access database file with an “dead” matter.  Here’s an Office knowledgebase article "Attach files and graphics to records in your database