Category Archives: MS-Office

Getting Excel data into a plain HTML table

I’d been looking for something like this for ages, but must have been Googling the wrong terms.

How to get the contents of an Excel spreadsheet into a simple, no-format, plain HTML table:

No-Cruft Excel to HTML Table Converter

Genius.

Old shapes in Visio

I know I’m using an old (2003) version of Visio, but seriously… paper tape?

Visio shapes

(I suppose these days “cards” could refer to some kind of portable storage, though I bet it really means punch cards.)

Giant embedded slides

Email arrived. Embedded Powerpoint slides. 9Mb. Wow.

Saved the slides out to a temporary directory, loaded them in Powerpoint, saved again as PPTX, edited the message (thank goodness Outlook allows this) to remove the embedded slides and attach the PPTX versions instead. Result: 663 Kb — a 93% saving in space, with no loss of fidelity.

Either we need to send everybody on compulsory email attachments training, or email systems need to get much more efficient at this stuff, and clean up the stupid stuff for them automatically.

By the way, Outlook 2010 made it very difficult, if not impossible to save the slides. Outlook 2007 looking at the same message managed it easily. Hmmm.

Importing into SQL Server

Alas SQL Server Management Studio isn’t as friendly as it could be for pasting in data. You’d think Microsoft would have this humming, but when I tried to paste from Excel, it attempted to paste the entire first row from my spreadsheet into the first column (in one row) of the database.

Using MS Access to open up the database probably would have worked, but I didn’t have it on that machine.

Trying to import using the SQL Server Import And Export Data wizard from a CSV text file worked for a small amount of data, but the 80,000 rows I was trying to import from the world ports code list didn’t. Time and time again it would report an error (unspecified) and give me the option of Abort, Retry, Ignore. No matter option I chose, it crashed.

While the 64-bit version of the wizard on my 64-bit Win7 machine didn’t allow you to import from Excel/Access, the 32-bit version did (presumably because MS Office, at least the version I have installed, is 32-bit).

The next problem was that it only supported Excel 2003 format, which can’t handle more than 64K rows. I ended up having to split the data into two and import the two spreadsheets separately. Then it worked.

Shame the wizard is so flaky, and of course it’s a big shame that Management Studio doesn’t do copy/paste like one would expect. (Maybe that too was a 32-bit/64-bit issue.)

Powerpoint file sizes

Was dealing with a big Powerpoint presentation (PPT) file.

In the older PPT format, 6063 Kb.

When zipped, 4826 Kb. Not a bad saving given the number of pictures in it.

Here’s the interesting thing: in PPTX format: 3293 Kb.

Remembering that PPTX and other Office Open XML formats (DOCX, XLSX etc) do their compression on the file as a whole, not the individual componenets, so this is an interesting result.

Perhaps the old binary format is inherently less efficient/compressible than the new XML format.

Mind you, another big PPT I tried it with didn’t compress down as much; the PPTX was about the same size as the ZIPped PPT, so it obviously depends on the exact content

Outlook’s HTML message bloat

I was cleaning up my work mail, which is in Outlook using Exchange. I was staggered to see a relatively short email taking an inordinate amount of space.

Copied the text including headers to a text editor. It was 6300 bytes. But Outlook claimed it was taking 485 Kb — some 76 times the amount of text.

How can this be?

The message was in HTML format. Ah… Microsoft-generated HTML, a receipe for bloat. It seems particularly bad when the message contains a whole email trail.

So, using Outlook’s very handy Edit Message function (I’m surprised it’s not abused more often), I changed it to Plain Text. It’s not as if anything in there relied on the HTML in order to be legible.

Switcheroo, save, presto! 17 Kb. Not 6, but not 485 either. Much better.

Shame there isn’t an option to clean up MS HTML.

Another thing one can do is zip the attachments.

Tables: MS Word vs CSS

Here’s why I like CSS.

Here’s a table created in Microsoft Word and pasted into a CMS:


<table border="1" cellspacing="0" cellpadding="0" class="MsoNormalTable" style="border-collapse: collapse; border: medium none"><tbody><tr><td width="64" valign="top" style="border-right: #f0f0f0; padding-right: 5.4pt; border-top: windowtext 2.25pt solid; padding-left: 5.4pt; background: #4bacc6 0% 50%; padding-bottom: 0cm; border-left: #f0f0f0; width: 47.65pt; padding-top: 0cm; border-bottom: windowtext 2.25pt solid; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial"><strong><span style="color: white; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">&nbsp;</font></span></strong></td><td width="170" valign="top" style="border-right: #f0f0f0; padding-right: 5.4pt; border-top: windowtext 2.25pt solid; padding-left: 5.4pt; background: #4bacc6 0% 50%; padding-bottom: 0cm; border-left: #f0f0f0; width: 127.85pt; padding-top: 0cm; border-bottom: windowtext 2.25pt solid; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial"><strong><span style="color: white; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Description</font></span></strong><strong><span style="color: white; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">&nbsp;</font></span></strong></td><td width="335" valign="top" style="border-right: #f0f0f0; padding-right: 5.4pt; border-top: windowtext 2.25pt solid; padding-left: 5.4pt; background: #4bacc6 0% 50%; padding-bottom: 0cm; border-left: #f0f0f0; width: 250.95pt; padding-top: 0cm; border-bottom: windowtext 2.25pt solid; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial"><strong><span style="color: white; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">&nbsp;</font></span></strong></td></tr><tr style="height: 36.85pt; page-break-inside: avoid"><td rowspan="7" width="64" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #4bacc6 0% 50%; padding-bottom: 0cm; width: 47.65pt; padding-top: 0cm; height: 36.85pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border: #f0f0f0"><strong><span style="color: white; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Benefits</font></span></strong></td><td width="170" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #d8d8d8 0% 50%; padding-bottom: 0cm; width: 127.85pt; padding-top: 0cm; height: 36.85pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border: #f0f0f0"><span style="font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Low Total Cost of Ownership (TCO)</font></span></td><td width="335" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #d8d8d8 0% 50%; padding-bottom: 0cm; width: 250.95pt; padding-top: 0cm; height: 36.85pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border: #f0f0f0"><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">No up-front hardware or software costs</span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Significantly less work effort to set-up a B2B integration solution since it involves&nbsp;mostly configuration tasks rather than programming</span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Free use of online development interface&nbsp;for developers&nbsp;</span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Data processing rates for usage&nbsp;are world&rsquo;s best</span></p></td></tr><tr><td width="170" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; padding-bottom: 0cm; width: 127.85pt; padding-top: 0cm; background-color: transparent; border: #f0f0f0"><span style="font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Best Return on Investment (ROI)</font></span></td><td width="335" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; padding-bottom: 0cm; width: 250.95pt; padding-top: 0cm; background-color: transparent; border: #f0f0f0"><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">ROI achieved sooner due to low up-front and on-going costs </span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Optimizes work effort since tasks removed or simplified</span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Higher ROI due to removal of costs</span></p></td></tr><tr><td width="170" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #d8d8d8 0% 50%; padding-bottom: 0cm; width: 127.85pt; padding-top: 0cm; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border: #f0f0f0"><span style="font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Speed of Delivery</font></span></td><td width="335" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #d8d8d8 0% 50%; padding-bottom: 0cm; width: 250.95pt; padding-top: 0cm; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border: #f0f0f0"><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Solutions delivered in days and weeks rather than months and years</span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">No requirement to establish and maintain hardware and software</span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Support for standards reduces need for specialists and training</span></p></td></tr><tr><td width="170" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; padding-bottom: 0cm; width: 127.85pt; padding-top: 0cm; background-color: transparent; border: #f0f0f0"><span style="font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Control and Flexibility</font></span></td><td width="335" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; padding-bottom: 0cm; width: 250.95pt; padding-top: 0cm; background-color: transparent; border: #f0f0f0"><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Developers have full control over tenancies, design data and administration</span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Development can be done anywhere at anytime</span></p></td></tr><tr><td width="170" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #d8d8d8 0% 50%; padding-bottom: 0cm; width: 127.85pt; padding-top: 0cm; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border: #f0f0f0"><span style="font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Guaranteed Service </font></span></td><td width="335" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #d8d8d8 0% 50%; padding-bottom: 0cm; width: 250.95pt; padding-top: 0cm; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border: #f0f0f0"><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Secure and reliable infrastructure </span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Guaranteed service level</span></p><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Data-back-up and disaster recovery provided </span></td></tr><tr><td width="170" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; padding-bottom: 0cm; width: 127.85pt; padding-top: 0cm; background-color: transparent; border: #f0f0f0"><span style="font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Market Leading Service</font></span></td><td width="335" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; padding-bottom: 0cm; width: 250.95pt; padding-top: 0cm; background-color: transparent; border: #f0f0f0"><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Most advanced functionality</span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">First remotely configurable Integration, BPM and BI service</span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Wide support for industry standards and customizations</span></p></td></tr><tr><td width="170" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #d8d8d8 0% 50%; padding-bottom: 0cm; width: 127.85pt; padding-top: 0cm; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border: #f0f0f0"><span style="font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Future Proof</font></span></td><td width="335" valign="top" style="padding-right: 5.4pt; padding-left: 5.4pt; background: #d8d8d8 0% 50%; padding-bottom: 0cm; width: 250.95pt; padding-top: 0cm; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; border: #f0f0f0"><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Quarterly releases ensure up-to-date functionality </span></p><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Upgrades are our responsibility</span></p></td></tr><tr><td width="64" valign="top" style="border-right: #f0f0f0; padding-right: 5.4pt; border-top: #f0f0f0; padding-left: 5.4pt; background: #4bacc6 0% 50%; padding-bottom: 0cm; border-left: #f0f0f0; width: 47.65pt; padding-top: 0cm; border-bottom: windowtext 2.25pt solid; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial"><strong><span style="color: white; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">&nbsp;</font></span></strong></td><td width="170" valign="top" style="border-right: #f0f0f0; padding-right: 5.4pt; border-top: #f0f0f0; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #f0f0f0; width: 127.85pt; padding-top: 0cm; border-bottom: windowtext 2.25pt solid; background-color: transparent"><span style="font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;"><font size="2">Expert Assistance</font></span></td><td width="335" valign="top" style="border-right: #f0f0f0; padding-right: 5.4pt; border-top: #f0f0f0; padding-left: 5.4pt; padding-bottom: 0cm; border-left: #f0f0f0; width: 250.95pt; padding-top: 0cm; border-bottom: windowtext 2.25pt solid; background-color: transparent"><p style="margin: 0cm 0cm 4pt" class="MsoBodyText"><span style="font-size: 8pt; font-family: &#39;Calibri&#39;,&#39;sans-serif&#39;">Expertise and knowledge available for support, development, consulting and training</span></p></td></tr></tbody></table>

With a little CSS coding (held in an external file), it has become this:


<table class="featuretable">
  <tbody>
    <tr>
      <td class="ftop"></td>
      <td class="ftop">Description</td>
      <td class="ftop"></td>
    </tr>
    <tr>
      <td class="fside">Benefits</td>
      <td class="fd0">Low Total Cost of Ownership (TCO)</td>
      <td class="fd0">No up-front hardware or software
costs<br>
Significantly less work effort to set-up a B2B integration solution
since it involves mostly configuration tasks rather than programming<br>
Free use of online development interface for developers <br>
Data processing rates for usage are world’s best</td>
    </tr>
    <tr>
      <td class="fside"></td>
      <td class="fd1">Best Return on Investment (ROI)</td>
      <td class="fd1">ROI achieved sooner due to low
up-front and on-going costs<br>
Optimizes work effort since tasks removed or simplified<br>
Higher ROI due to removal of costs</td>
    </tr>
    <tr>
      <td class="fside"></td>
      <td class="fd0">Speed of Delivery</td>
      <td class="fd0">Solutions delivered in days and
weeks rather than months and years<br>
No requirement to establish and maintain hardware and software<br>
Support for standards reduces need for specialists and training</td>
    </tr>
    <tr>
      <td class="fside"></td>
      <td class="fd1">Control and Flexibility</td>
      <td class="fd1">Developers have full control over
tenancies, design data and administration<br>
Development can be done anywhere at anytime</td>
    </tr>
    <tr>
      <td class="fside"></td>
      <td class="fd0">Guaranteed Service</td>
      <td class="fd0">Secure and reliable infrastructure<br>
Guaranteed service level<br>
Data-back-up and disaster recovery provided</td>
    </tr>
    <tr>
      <td class="fside"></td>
      <td class="fd1">Market Leading Service</td>
      <td class="fd1">Most advanced functionality<br>
First remotely configurable Integration, BPM and BI service<br>
Wide support for industry standards and customizations</td>
    </tr>
    <tr>
      <td class="fside"></td>
      <td class="fd0">Future Proof</td>
      <td class="fd0">Quarterly releases ensure up-to-date
functionality<br>
Upgrades are our responsibility</td>
    </tr>
    <tr>
      <td class="fside"></td>
      <td class="fd0">Expert Assistance</td>
      <td class="fd0">Expertise and knowledge available
for support, development, consulting and training</td>
    </tr>
  </tbody>
</table>

Old version: 12250 bytes.

New version: 2490 bytes + 605 bytes of CSS. And much more maintainable, and it’ll be easier to change the table styles later.

OK, the new looks slightly different to the old (this was on purpose to enlarge the fonts a bit), but jeez.

Office 2007: Menus and SP2

Try as you might, you can’t get used to the Office 2007 ribbons, and you want the menus back?

There have been a few paid solutions, but here’s a free one (for non-commercial use): UBitMenu (Corporate licence applies for business usage for €10 + €0.65 per user).

It adds a Menu ribbon which has the traditional menus, with extra items added for new Office 2007 functionality. Neato; I’ll be giving this a try pronto.

(via Office Watch)

By the way, Office 2007 SP2 is due out on April 28th… this MS blog article previews some of the new features included. (via Malcolm)

Quicktime and a decompressor are needed to see this picture

Of all the useless error messages, this one would have to take the cake. I found it in a Word document tonight.

Quicktime and a decompressor are needed to see this picture

It appears to be caused by the author using Mac Word, and having pasted a picture into the document in some weird and wonderful way.

The error is useless, because I already have Quicktime installed on this machine. (I didn’t particularly want it; it came with iTunes.) And if it wants a particular decompressor, it would be very helpful if it gave me a hint as to which one, and where I should get it from.

I couldn’t even see a way of extracting the picture so I could try and throw it at another viewer program.

In this age of standards, when the vast majority of pictures flying about the place are either GIF, JPEG or PNG, and even proprietary standards like MS Word are almost universal, why on earth should I be getting an error message like this?

Evidently the only fix is to go back to the source (on the Mac) and change the picture to something more universal. Thankfully the document’s author was around, so I could do that. But who knows why Mac Word lets people insert pictures in this way in the first place. (Powerpoint is susceptible too.)

Conclusion? Blame Microsoft!

How to compress attachments in Outlook

I use Outlook at work, and sometimes people send big attachments without zipping them up. Often they’re documents which are in the document management system anyway. Sigh.

Anyway, it’s easy enough to compress them within Outlook.

1. Open mail item.

2. Save attachment(s) to a temporary directory.

3. Zip (or whatever) the file. (See? 2Mb XLS down to 300Kb. Why didn’t the sender do that?)

4. In the email, click Edit / Edit Message on the menu. (This feature is a boon for fraudulent modification of emails, but also for compressing attachments.)

5. Right click on the attachment and Remove.

6. Drag the zip file into the email. (For some reason you can’t use the menu to insert an attachment like you can when composing. And the drag-drop has to land in the body section of the email.)

7. Save and Close the email.

Voila, a bunch of space saved.

I haven’t explored to see if the same method can be used in other mail clients.

Outlook 2007 irritations

Why do I keep getting these? They rarely happened in Outlook 2003.

A data file did not close properly
I promise you, I did close Outlook properly. Just like I do every time. It’s good that this version can do the integrity check in the background, but I don’t see why it’s needed at all.

A connection to the server cannot be established
IMAP performance in this version is appalling. It rarely seems to stay connected to the IMAP server for more than a few seconds at a time. Just about all the time I need to keep reconnecting and/or do the Mark For Download / Process Marked Headers thing, which gets old really fast.

I like a lot of the other stuff in the new Outlook, but these are very irritating.