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!!!

Global sDataPath As String
Global bUserCancelled As Boolean
Global avColLength() As Variant

Option Explicit

Sub ImportFixedWidth()

'Delcarations

Dim wksInfo As Worksheet

Dim rFindStart As Range

Dim iRowCount As Integer

'Setting initial values

bUserCancelled = False

Set wksInfo = Worksheets("Info")
wksInfo.Activate

Set rFindStart = wksInfo.Cells.Find("LENGTH")

iRowCount = wksInfo.Cells(Rows.Count, rFindStart.Column).End(xlUp).Row - rFindStart.Row

avColLength = wksInfo.Range(Cells(rFindStart.Row + 1, rFindStart.Column), Cells(rFindStart.Row + iRowCount, rFindStart.Column))

Call WorkbookReset

Call UserSelectFile

If bUserCancelled = True Then Exit Sub

Call MoveNewFile

End Sub

Sub WorkbookReset()

Dim wksData As Worksheet

Set wksData = Worksheets("Data")

wksData.Activate

If wksData.Range("A1").Value = "" Then

    Exit Sub

Else

    wksData.Cells.Select
    Selection.QueryTable.Delete
    Selection.ClearContents

End If

End Sub

Sub UserSelectFile()

sDataPath = Application.GetOpenFilename("Text Files (*.txt), *.txt", 1, "Select Experian Quest File to Open")

If sDataPath = "False" Then

    MsgBox ("No file was select. The procedure has been cancelled.")
    bUserCancelled = True
    
    Exit Sub

End If

End Sub

Sub MoveNewFile()

Dim wksData As Worksheet

Set wksData = Worksheets("Data")

wksData.Activate
wksData.Range("A1").Select

With wksData.QueryTables.Add(Connection:="TEXT;" & sDataPath, Destination:=Range("$A$1"))
    .Name = "201515230A_Main"
    .AdjustColumnWidth = True
    .TextFileStartRow = 2
    .TextFileParseType = xlFixedWidth
    .TextFileFixedColumnWidths = Array(avColLength)
    .TextFileTrailingMinusNumbers = True
End With

End Sub

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.

avColLength = Application.Transpose(wksInfo.Range(Cells(rFindStart.Row + 1, rFindStart.Column), Cells(rFindStart.Row + iRowCount, rFindStart.Column)))