Daily Dose of Excel

Importing Fixed Width Text Files with ActiveX Data Objects

2013-05-10T15:13:20Z

A few weeks ago I was struggling with fixed width text files. Remember 1995? Me neither, but I’m living it every day. I happened upon a stackoverflow.com post that dealt with using ADO for this purpose. I don’t remember the post, so no link. It did, however, lead me to this MSDN article, which was [...] Read More......(read more)

Importing Fixed Width Text Files

2013-05-09T01:34:12Z

I’ve been working with fixed width text files quite a bit lately. The built-in method for importing these files is terrible. First, it’s yet another wizard and I think it would be better as a single form (but then I think everything is better as a single form). The worst part, by far, is the [...] Read More......(read more)

Queen’s day sale

2013-04-29T18:58:16Z

As some of you may know, I’m Dutch and live in “The Kingdom Of The Netherlands”. Well, tomorrow is an important day for the Dutch people: Queen’s Day. More so, tomorrow is an extra special day. Not because our Queen abdicates to make room for King Willem-Alexander, but because my 24 hour “Queen’s Day sale” [...] Read More......(read more)

Draw a circle in an Excel chart

2013-04-28T06:24:23Z

By default, Excel has a limited number of charts. That does not mean that those are the only charts one can create. It turns out that with a little imagination and creativity, we can format and configure the default charts so that the effect is like many other kinds of charts. One of the more [...] Read More......(read more)

Analyze ‘free’ money

2013-04-25T06:40:17Z

Recently, I received yet another promotion from yet another company offering me money at zero percent interest with the predictable asterisk next to the zero percent. Instead of just shredding the offer I decided to create a downloadable Excel template to analyze the offer, which was an interest-free 18 month loan for a 4% transaction [...] Read More......(read more)

Date Picker Add-in for Excel 2011 for the Mac

2013-04-21T22:19:47Z

Hi all I upload a Date Picker add-in based on John Walkenbach’s Date Picker on my site for the Mac, this Date Picker will also be a part of to the PUP add-in for the Mac soon. This Date Picker for Mac Excel have local Day letters in the header and also local Date formats in the format [...] Read More......(read more)

Stop Replying to Yourself

2013-04-17T13:01:28Z

In this edition of How to Make Outlook More Like GMail, I address the problem of replying to a message that you have sent. Inexplicably, Outlook creates a message addressed to you. That make sense from the standpoint that a reply to should be addressed to the sender. But then there’s the whole common sense [...] Read More......(read more)

Excel corrupts certain workbooks in migrating from 2003 to 2007

2013-04-14T07:04:06Z

I got a email from a client asking for help because Excel was “destroying,” to use his terminology, his 2003 workbook after conversion to the 2007 format. And, after analyzing the kind of change Excel made, I had to agree. The following in 2003 Figure 1 becomes, in 2013 (and in 2010), Figure 2 The [...] Read More......(read more)

Almost There

2013-04-12T21:22:39Z

Look how close I am. It’s like the odometer rolling over on my car. Read More......(read more)

Sample Fixed Width Text File

2013-04-12T11:49:27Z

I have a post or two in my head that deals with fixed width text files, something I’ve been dealing with quite a bit. Like me, you probably don’t run into fixed width text files in your personal life. It’s usually some crappy computer program at your job that forces you to deal with them. [...] Read More......(read more)

Com Add-ins Install problem on a machine with Office 2013

2013-04-10T16:38:50Z

Hi all VBE Bookmarks/Copy buffer and Tools>References Enlarger Com Add-ins http://www.rondebruin.nl/win/addins/vbeaddins.htm When I create this new page on my site about two very nice com add-ins from Jim Rech I found out that if you want to install them on a machine with Office 2013 you are missing a dll file that you need if [...] Read More......(read more)

RIP Live Mesh, hello Cubby

2013-04-09T00:54:29Z

Recently, I have had to make changes to my “computing infrastructure.” This is one of them. For the longest time I synchronized files between my laptop and desktop with the help of a flash drive. Then, I discovered Groove but it didn’t really fit the bill and I don’t remember why. It was on [...] Read More......(read more)

Worksheet as a chart – multiple conditional formats

2013-04-07T06:44:38Z

Several years back, I wrote an article on how to use multiple cells to simulate conditional formats that involved more than 3 conditions. Three versions of Excel later, I still receive requests related to this post. So, I updated it to include more screenshots and a downloadable file. In Excel 2003 and earlier, conditional formatting [...] Read More......(read more)

Friday Thoughts

2013-04-05T23:23:28Z

I need this procedure automatically called from every entry point procedure I run: Public Function PreventHavingToKillExcelAndLoseWork() As Boolean Dim lCountEOF As Long Dim lCountMoveNext As Long lCountEOF = CountInstanceOfStringInAllModules(".EOF") lCountMoveNext = CountInstanceOfStringInAllModule(".MoveNext") PreventHavingToKillExcelAndLoseWork = lCountEOF <= lCountMoveNext...(read more)

Opening Outlook Attachments Redux

2013-04-04T12:00:08Z

In Opening Outlook Attachments, I posted some code to open Outlook attachments using the keyboard. Now that I’m back using Outlook, I had to dig out the old code and put it to use. Of course I won’t be leaving well enough alone, but thanks for asking. The last change I made was to refactor [...] Read More......(read more)