<\/div>\n
You already know what the constant xlFixedWidth is for; that tells the script that we\u2019re working with a fixed-width file. That leaves us with this:<\/p>\n
Array(Array(0, xlTextFormat),Array(51, xlTextFormat),Array(102, xlTextFormat))\n<\/pre>\nBelieve 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\u2019s why we end up with a bunch of little arrays embedded inside one big, giant array. As for those little arrays, let\u2019s take a look at the code that defines the first field in the text file:<\/p>\n
Array(0, xlTextFormat)\n<\/pre>\nAs you can see, we\u2019re 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\u2019s 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.<\/p>\n
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:<\/p>\n
Array(51, xlTextFormat)\n<\/pre>\nEtc., etc.<\/p>\n
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.<\/p>\n
In the meantime, we\u2019re sure that many of you are thinking, \u201cYou 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?\u201d 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 16th<\/sup> birthday party. (<\/i>Ed<\/i>itor\u2019s Note: Well, that\u2019s pretty close, with the exception of the month, day, and year.<\/i> It is true, however, that I had a 16<\/i>th<\/sup> birthday.<\/i>)<\/i><\/p>\n","protected":false},"excerpt":{"rendered":"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\u2019s been a very weird morning at that. For one thing, it\u2019s not raining and \u2013 if you look closely \u2013 you can even see the […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[18,711,48,49,3,4,14,5],"class_list":["post-55783","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-arrays-hash-tables-and-dictionary-objects","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-scripting-techniques","tag-text-files","tag-vbscript"],"acf":[],"blog_post_summary":"
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\u2019s been a very weird morning at that. For one thing, it\u2019s not raining and \u2013 if you look closely \u2013 you can even see the […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55783","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=55783"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/55783\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=55783"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=55783"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=55783"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
<\/p>\n![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/a> <\/p>\n