Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Arrays in Excel VBA (97 sr2)

    I'm trying to import data from an external file using VBA so I don't need to be here 24/7.
    No problem with the smaller files but I'm hitting a compile error: Out of memory when I try to specify more than say 36 array elements.
    Here's a section of the code:

    Workbooks.OpenText FileName:= _
    MyfileName _
    , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
    :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
    False, Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
    FieldInfo:=Array(Array(1, 1), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array _
    (6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 2), _
    Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 1), Array(17, 4), Array(18, 4), Array( _
    19, 4), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array(25, 1), _
    Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31, 1), Array( _
    32, 1), Array(33, 1), Array(34, 1), Array(35, 2), Array(36, 1), Array(37, 2), Array(38, 2), _
    Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 2), Array(44, 1), Array( _
    45, 1), Array(46, 2), Array(47, 2), Array(48, 2), Array(49, 2), Array(50, 2), Array(51, 2), _
    Array(52, 2), Array(53, 2), Array(54, 2), Array(55, 1))

    According to other info I have, the number of elements in the array shouldn't be giving a problem, but it does.
    I have no constants and 3 public dims.
    The filename to open comes from GetOpenFilename sub in same module.

    Any suggestions on how to open a file containing this number (or more when I have 72 columns to import) of csv fields??

    TIA
    Alan
    Cheshire
    UK

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Arrays in Excel VBA (97 sr2)

    Although VBA can handle very large arrays, there is a limit on the number of Array(Array(...), Array(..), ...). You can get around it by defining the array in a different way:

    Dim arrColumns(1 To 55, 1 To 2)
    Dim i As Long

    For i = 1 To 55
    arrColumns(i, 1) = i
    Next i

    arrColumns(1, 2) = 1
    arrColumns(2, 2) = 2
    ...
    arrColumns(54, 2) = 2
    arrColumns(55, 2) = 1

    Workbooks.OpenText FileName:= MyfileName, Origin:=xlWindows, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=True, OtherChar:="|", _
    FieldInfo:=arrColumns

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Arrays in Excel VBA (97 sr2)

    Sorry to be a pain but I can't see the application of that code in my scenario. The array which I am trying to bring in is in typically a csv type file but with pipe separators. The defined array as in the original sample coding can be explained as:
    Array(1,2) - this is first column and is to be imported as "General" format
    Array(2,2) - this is second column and is to be imported as "Text" format
    ....
    Array(17,4) - this is the seventeenth column and is to be imported as "Date" format
    ....
    Array(54,2) - this is the fifty fifth column and is to be imported as "Text" format

    etcetera.
    First number is column number and second number is:
    1 = General format
    2 = Text format
    4 = Date format

    The formats of the columns are already specified from the original transfer.
    Hence my problem is that I need the separate array components to be of 2 values, the second of which needs to be defined and not incremental or to any pattern.

    Hope this sounds clear.

    Is multi-dimensional arrays the answer??

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Arrays in Excel VBA (97 sr2)

    Have you actually tried it? The code I proposed create a two-dimensional array that is the equivalent of the array of arrays in your code. The first column of this array is simply filled with the numbers 1 to 55. You were meant to complete the list of values for the second column, where I put ... since I'm a lazy <img src=/w3timages/censored.gif alt=censored border=0>. For example, since your code has Array(18, 4), you should add arrColumns(18, 2) = 4.

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Arrays in Excel VBA (97 sr2)

    Apologies and thanks Hans,
    My foot is now lodged in my mouth. I was distracted a little by there being no number 4's and compared them to my original coding - kind of like 2+2=5.
    Just redone the code in two subs and it works fine.
    Many thanks and I appreciate your patience.
    Alan
    Cheshire UK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •