Using an Array to set Column Widths when Importing a Fixed Width Text File

Hello everyone,

I having only been using VBA for a few months and am new to arrays. I have a Fixed Width file which I am trying to import and force the width of the columns based on information that comes with the file. ie. start character, end character, and length of characters. I've already created an Array which is pulling in and storing the list of character lengths. Now, while actually opening the text file, I want to use that array to set the widths. Yet, I cannot get it to work as I keep getting an "Invalid procedure call or argument" error.

My code is broken up into 4 different subs but the first one is what creates the array and the last one is what is using the array.

Note: I did originally try Workbooks.OpenText instead of the QueryTables.Add function, but from what I can tell you cannot specify column widths with the former. Is this true?

Any help would really be appreciated!!!

Please Login or Register  to view this content.

Since no one replied, I am updating with my solution in case it can help anyone later down the road.

The issue was with initializing my array. The range that was being used to populate the array was stored vertically, lets say cells C11:C77. I had to wrap this initialization of the array with an Application.Transpose. See below. Upon doing so, everything worked fine. Simple oversight but I was unaware that Excel arrays could be stored in such a manner. I assumed that they were stored horizontally.

Please Login or Register  to view this content.