Answered by:
Open Text File with Variable (String) as FieldInfo Argument not working
Question

Hi,
I have an issue with trying to open a text file using Workbooks.OpenText in VBA.
What I am trying to do is open a text file in which the number of columns of data included in the text file can vary. Before opening the text file, the format used for importing each column will be set. i.e. #9 for skip, #1 for general, #2 for text, etc. This worked fine when hardcoding the "FieldInfo" argument.
In order to incorporate the fact that the number of arrays can vary, I decided to create the "FieldInfo" argument as string via a variable. And here lies the problem. When I use a string variable for the FieldInfo Argument "FieldInfo:=myArray", I get the following error: "Runtime error '1004': Method 'OpenText' of object 'Workbooks' failed"
However, I had the macro write the resulting string of the variable into a cell in order to replace the variable within the "FieldInfo" Argument with exactly that resulting string. When doing so the Macro runs through without Error message.
The Code looks as follows:
Dim myArray as String
...(creation of String)
Workbooks.OpenText Filename:=(Report_Path & Report_Name) _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=myArray, TrailingMinusNumbers:=TrueThis is the string I create and which "myArray" equals. Looks good and like I said above it works as well, as long as its
not handed to the FieldInfo Argument via a variable.
Array(Array(1, 9), Array(2, 2), Array(3, 2), Array(4, 9), Array(5, 9), Array(6, 2), Array(7, 9), Array(8, 9), Array(9, 9), Array(10, 9), Array(11, 9), Array(12, 2), Array(13, 9), Array(14, 9), Array(15, 9), Array(16, 9), Array(17, 5), Array(18, 2), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 9), Array(23, 9), Array(24, 9), Array(25, 9), Array(26, 9), Array(27, 9), Array(28, 9), Array(29, 9), Array(30, 9), Array(31, 9), Array(32, 9), Array(33, 9), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 9), Array(41, 9), Array(42, 9), Array(43, 9), Array(44, 9), Array(45, 9), Array(46, 9), Array(47, 9), Array(48, 9), Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 9), Array(53, 9), Array(54, 9))
I also tried "FieldInfo:=Array(myArray)" with the variable returning an amended string. Then the Error comes back as "Runtime error '13': Type mismatch". Again, if I replace the variable "myArray" with the resulting string it runs through.
I would therefore like to think that its not the string I create but something else that doesn't add up.
Any ideas on what I am doing wrong? Am I missing something? Or even other ideas on how to approach the problem of varying arrays a different way?
Answers

Hi,
thanks for the answer. Realised the declaration error just after posting the question yesterday, as I was typing away the "FieldInfo" argument the "Array(ParamArray ArgList() as Variant)" popped up.
Your code works.
However, as my Array needs to adjust in length in order to accomodate the fact that the number of imported columns can vary, I tried the following. With the result that now I get a "Runtime error '13': Type mismatch" for the Worksbooks.OpenText.
Dim Array_Value (1 to 100) as Double
Dim Next_Array_Value as Double
Dim myArray(1 to 100) as Variant
Dim i as Double
For i = 1 to 100
Array_Value(i) = Worksheets("xy").Cells(i, 13).Value
myArray(i) = Array(i, Array_Value(i))
Next_Array_Value = Worksheets("xy").Cells(i + 1, 13).Value
If Next_Array_Value = Empty Then
GoTo Exit_Array_Loop:
End If
Next i
Exit_Array_Loop:
Workbooks.OpenText Filename:=(Report_Path & Report_Name) _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(myArray), TrailingMinusNumbers:=TrueAlso tried it with "FieldInfo:=myArray" with the same result.
 Marked as answer by Nezi1 Thursday, November 26, 2015 12:34 PM

Thanks for the help on that, it works now!
I reran my old code with a watch on the variable myArray and found that myArray had 3 dimensions, which obviously doesn't work with the FieldInfo argument, where each SubArray has only 2. Looking at my old code... still not sure where I gave it that 3rd dimension though.
Anyway, after looking at your code, I changed my code as follows (and it works now):
Dim Array_Value () As Double
Dim myArray() As Variant
Dim subArray (0 to 1) As Variant
Dim Number_Of_Arrays Ar Double
Dim i as Double
Number_Of_Arrays = Worksheets("yx").Cells(47, 15).value 'Cell that always holds the number of Arrays in import
ReDim myArray(1 To Number_Of_Arrays)
ReDim Array_Value(1 To Number_Of_Arrays)
For i = LBound(myArray) To UBound(myArray)
Array_Value(i) = Worksheets("xy").Cells(i, 13).Value
myArray(i) = Array(i, Array_Value(i))
Next i
Workbooks.OpenText Filename:=(Report_Path & Report_Name) _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=myArray, TrailingMinusNumbers:=TrueAgain, thanks a lot for your help on this! Very much appreciated!!!
All replies

Any ideas on what I am doing wrong?
myArray must be a Variant and a real array, not a String.
Andreas.
Dim myArray As Variant myArray = Array( _ Array(1, 9), Array(2, 2), Array(3, 2), Array(4, 9), Array(5, 9), Array(6, 2), _ Array(7, 9), Array(8, 9), Array(9, 9), Array(10, 9), Array(11, 9), Array(12, 2), _ Array(13, 9), Array(14, 9), Array(15, 9), Array(16, 9), Array(17, 5), Array(18, 2), _ Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 9), Array(23, 9), Array(24, 9), _ Array(25, 9), Array(26, 9), Array(27, 9), Array(28, 9), Array(29, 9), Array(30, 9), _ Array(31, 9), Array(32, 9), Array(33, 9), Array(34, 1), Array(35, 1), Array(36, 1), _ Array(37, 1), Array(38, 1), Array(39, 1), Array(40, 9), Array(41, 9), Array(42, 9), _ Array(43, 9), Array(44, 9), Array(45, 9), Array(46, 9), Array(47, 9), Array(48, 9), _ Array(49, 1), Array(50, 1), Array(51, 1), Array(52, 9), Array(53, 9), Array(54, 9)) Workbooks.OpenText Filename:=(Report_Path & Report_Name) _ , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=myArray, _ TrailingMinusNumbers:=True

Hi,
thanks for the answer. Realised the declaration error just after posting the question yesterday, as I was typing away the "FieldInfo" argument the "Array(ParamArray ArgList() as Variant)" popped up.
Your code works.
However, as my Array needs to adjust in length in order to accomodate the fact that the number of imported columns can vary, I tried the following. With the result that now I get a "Runtime error '13': Type mismatch" for the Worksbooks.OpenText.
Dim Array_Value (1 to 100) as Double
Dim Next_Array_Value as Double
Dim myArray(1 to 100) as Variant
Dim i as Double
For i = 1 to 100
Array_Value(i) = Worksheets("xy").Cells(i, 13).Value
myArray(i) = Array(i, Array_Value(i))
Next_Array_Value = Worksheets("xy").Cells(i + 1, 13).Value
If Next_Array_Value = Empty Then
GoTo Exit_Array_Loop:
End If
Next i
Exit_Array_Loop:
Workbooks.OpenText Filename:=(Report_Path & Report_Name) _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(myArray), TrailingMinusNumbers:=TrueAlso tried it with "FieldInfo:=myArray" with the same result.
 Marked as answer by Nezi1 Thursday, November 26, 2015 12:34 PM

Also tried it with "FieldInfo:=myArray" with the same result.
Each item of the main must be an array, and all arrays must be Variant. Below is an example.
Andreas.
Dim myArray() As Variant Dim subArray(0 To 1) As Variant Dim i As Long 'Create an array with 54 items ReDim myArray(0 To 53) For i = LBound(myArray) To UBound(myArray) 'Fill the subArray subArray(0) = i + 1 subArray(1) = Worksheets("xy").Cells(i + 1, 13).Value 'Copy into the main array myArray(i) = subArray Next Workbooks.OpenText Filename:=(Report_Path & Report_Name) _ , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _ Comma:=False, Space:=False, Other:=False, FieldInfo:=myArray, TrailingMinusNumbers:=True

Thanks for the help on that, it works now!
I reran my old code with a watch on the variable myArray and found that myArray had 3 dimensions, which obviously doesn't work with the FieldInfo argument, where each SubArray has only 2. Looking at my old code... still not sure where I gave it that 3rd dimension though.
Anyway, after looking at your code, I changed my code as follows (and it works now):
Dim Array_Value () As Double
Dim myArray() As Variant
Dim subArray (0 to 1) As Variant
Dim Number_Of_Arrays Ar Double
Dim i as Double
Number_Of_Arrays = Worksheets("yx").Cells(47, 15).value 'Cell that always holds the number of Arrays in import
ReDim myArray(1 To Number_Of_Arrays)
ReDim Array_Value(1 To Number_Of_Arrays)
For i = LBound(myArray) To UBound(myArray)
Array_Value(i) = Worksheets("xy").Cells(i, 13).Value
myArray(i) = Array(i, Array_Value(i))
Next i
Workbooks.OpenText Filename:=(Report_Path & Report_Name) _
, Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=myArray, TrailingMinusNumbers:=TrueAgain, thanks a lot for your help on this! Very much appreciated!!!

ReDim myArray(1 To Number_Of_Arrays)
ReDim Array_Value(1 To Number_Of_Arrays)
For i = LBound(myArray) To UBound(myArray)
Array_Value(i) = Worksheets("xy").Cells(i, 13).Value
myArray(i) = Array(i, Array_Value(i))
Next i
Array_Value is superfluous, have a look:
ReDim myArray(1 To Number_Of_Arrays) For i = LBound(myArray) To UBound(myArray) myArray(i) = Array(i, Worksheets("xy").Cells(i, 13).Value) Next i
I'm pleased to hear that it works.
Would you please so kind and mark the post with the code as answer? So it would be easier to find the right answer for followers. Thank you.
Andreas.
 Edited by Andreas Killer Thursday, November 26, 2015 11:04 AM typo