Hey, Scripting Guy! How can I import a fixed-width data file into Microsoft Excel?
— RS

Hey, RS. This column is being written on a Friday morning, and so far it’s been a very weird morning at that. For one thing, it’s not raining and – if you look closely – you can even see the sun. (It’s been awhile, but we’d recognize the sun anywhere.) Even weirder, the temperature is supposed to climb up into the 60s by this afternoon, and could even hit 70 degrees Fahrenheit on Saturday. 60-degree weather in April? In Seattle? We must be dreaming.

We’d give you more details on the current weather conditions if it weren’t for the fact that the Scripting Guy who writes this column doesn’t have a window office; that means that he can only keep tabs on the weather by getting up to go to the bathroom and then glancing out someone else’s window. Admittedly, this would usually be the point where the Scripting Guy who writes this column would make some smart remark about window offices. He’s not going to do that, however; needless to say, window offices are nothing to joke about at Microsoft. People will kill– and for all we know people have killed – just so they could get an office with a window.

Three interesting notes about window offices:

On the bright side, not having a window to stare out of does give the Scripting Guy who writes this column plenty of time to write this column. With that in mind, let’s take a look at the kind of fixed-width text file that RS would like to open in Excel:

Region                                             Subnet                                             Country
-------------------------------------------------- -------------------------------------------------- --------------
AMIS                                               192.168.0.0                                         American Samoa
AMIS                                               192.168.1.0                                         American Samoa
AMIS                                               192.168.10.0                                        American Samoa
AMIS                                               192.168.11.0                                        American Samoa
AMIS                                               192.168.12.0                                        American Samoa
AMIS                                               192.168.13.0                                        American Samoa
AMIS                                               192.168.14.0                                        American Samoa
AMIS                                               192.168.15.0                                        American Samoa
AMIS                                               192.168.2.0                                         American Samoa
AMIS                                               192.168.3.0                                         American Samoa

How can we open a file like that in Excel? Well, here’s one way:

Const xlFixedWidth = 2
Const xlTextFormat = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",,3,xlFixedWidth,,,,,,,,, _
    Array(Array(0, xlTextFormat),Array(51, xlTextFormat),Array(102, xlTextFormat))

Yes, we know: it is hard to believe that this script will correctly open a fixed-width file, isn’t it? But maybe things will clear up when we explain how it works.

No, check that: things will definitely clear up when we explain how it works.

Maybe.

As you can see, we start out by defining a pair of constants, setting the value of each of these constants to 2. The constant xlFixedWidth tells the script that we’re going to open a fixed-width text file; alternatively, we could create a constant named xlDelimited (with a value of 1) and use that to open a delimited text file (e.g., a comma- or tab-separated values file). The constant xlTextFormat tells the script the data type for each of the fields in our text file. Because all three of our fields are text fields we need to define – and use – only this one constant. However, there are other data types and there are other constants you might need to use. For more information, look for the xlColumnDataType enumeration in the Microsoft Excel VBA Language Reference.

After we define the constants we create an instance of the Excel.Application object and then set the Visible property to True; that gives us a running instance of Microsoft Excel that we can see on screen. And that – cue the ominous music – brings us to the following line of code:

objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",,3,xlFixedWidth,,,,,,,,, _
    Array(Array(0, xlTextFormat),Array(51, xlTextFormat),Array(102, xlTextFormat))

So is that the craziest line of code that the Scripting Guy who writes this column has ever written? No, but it’s probably in the top 10. As you can see, we’re using the OpenText method (a method that belongs to the Workbooks collection) to open the file C:\Scripts\Test.txt. As for all the other craziness crammed into that one line of code, well, any time you see a blank parameter (that is, a comma followed by absolutely nothing) that means that this is a parameter we don’t need in order to open our text file. For example, you might note that one of those blank parameters comes immediately after the file path:

"C:\Scripts\Test.txt",,

As it turns out, this second, optional parameter lets you specify the “origin” of the text file; that is, whether this file came from a Windows computer, an MS-DOS computer, or a Macintosh. (Oh, OK: a Macintosh computer.) Because the default is to assume that the file came from a Windows machine, we can simply leave this parameter blank. Just remember, though, that the parameter must be included even though it has no value. You might think that you could take a shortcut by eliminating all the blank parameters:

objExcel.Workbooks.OpenText "C:\Scripts\Test.txt",3,xlFixedWidth, _
    Array(Array(0, xlTextFormat),Array(51, xlTextFormat),Array(102, xlTextFormat))

That’s actually a pretty handy little shortcut … unless you want your script to work. If you want the script to work then leave in all the blank parameters. When you call the OpenText method the numerical position of the parameters is of paramount importance. The constant xlFixedWidth must be the fourth parameter in the method call. If xlFixedWidth appears anywhere else your script is likely to blow up.

And yes, we mean that literally: last year, in the state of Washington alone, nearly 300 people were horribly disfigured when their Excel scripts blew up on them.

As for the other parameters, the 3 tells the script that we want to start with line 3 in the text file; that enables us to skip over the two header lines. If you don’t want to skip over the two headers lines then leave this parameter blank.

You already know what the constant xlFixedWidth is for; that tells the script that we’re working with a fixed-width file. That leaves us with this:

Array(Array(0, xlTextFormat),Array(51, xlTextFormat),Array(102, xlTextFormat))

Believe it or not, this is where we define each field in the text file. To begin with, because we can (and usually will) have multiple fields in a text file all this information must be passed as an array; that’s why we end up with a bunch of little arrays embedded inside one big, giant array. As for those little arrays, let’s take a look at the code that defines the first field in the text file:

Array(0, xlTextFormat)

As you can see, we’re creating an array that contains two elements. The first item, the 0, represents the starting character position for this field. (The first character position in a file is position 0; that’s why we have a 0 here rather than a 1.) The second item is the constant xlTextFormat, which defines the data type for this field.

Now, what about field 2? Well, the second field begins at character 51; hence we use 51 for the first item in the array, and the constant xlTextFormat as the second item in the array:

Array(51, xlTextFormat)

Etc., etc.

So will that actually import a fixed-width text file into Excel? Well, it did for us; give it a try and see what happens for you.

In the meantime, we’re sure that many of you are thinking, “You know, the Scripting Guy who writes that column is always complaining about how bad the weather is. I wonder when the last time was that they had a nice day in Seattle?” Funny you should ask that: we looked it up, and the last time we actually had a nice day in Seattle was on June 19, 1937. Which was nice; that meant the Scripting Editor had nice weather for her 16^th birthday party. (Editor’s Note: Well, that’s pretty close, with the exception of the month, day, and year. It is true, however, that I had a 16^th birthday.)