none
Open Text File with Variable (String) as FieldInfo Argument not working RRS feed

  • 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: "Run-time 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:=True

    This 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 "Run-time 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?

    Wednesday, November 25, 2015 3:40 PM

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 "Run-time 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:=True

    Also tried it with "FieldInfo:=myArray" with the same result.

    • Marked as answer by Nezi1 Thursday, November 26, 2015 12:34 PM
    Thursday, November 26, 2015 7:32 AM
  • Thanks for the help on that, it works now!

    I re-ran 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 Sub-Array 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:=True

    Again, thanks a lot for your help on this! Very much appreciated!!!

    • Marked as answer by Nezi1 Thursday, November 26, 2015 12:32 PM
    • Unmarked as answer by Nezi1 Thursday, November 26, 2015 12:33 PM
    • Marked as answer by Nezi1 Thursday, November 26, 2015 12:34 PM
    Thursday, November 26, 2015 10:15 AM

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
    

    Wednesday, November 25, 2015 6:54 PM
  • 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 "Run-time 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:=True

    Also tried it with "FieldInfo:=myArray" with the same result.

    • Marked as answer by Nezi1 Thursday, November 26, 2015 12:34 PM
    Thursday, November 26, 2015 7:32 AM
  • 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
    

    Thursday, November 26, 2015 8:10 AM
  • Thanks for the help on that, it works now!

    I re-ran 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 Sub-Array 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:=True

    Again, thanks a lot for your help on this! Very much appreciated!!!

    • Marked as answer by Nezi1 Thursday, November 26, 2015 12:32 PM
    • Unmarked as answer by Nezi1 Thursday, November 26, 2015 12:33 PM
    • Marked as answer by Nezi1 Thursday, November 26, 2015 12:34 PM
    Thursday, November 26, 2015 10:15 AM

  • 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.


    Thursday, November 26, 2015 11:04 AM