Results 1 to 5 of 5
Thread: Arrays in Excel VBA (97 sr2)

20031024, 10:07 #1
 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

20031024, 10:22 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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

20031024, 12:15 #3
 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 multidimensional arrays the answer??

20031024, 13:46 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Arrays in Excel VBA (97 sr2)
Have you actually tried it? The code I proposed create a twodimensional 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.

20031028, 07:56 #5
 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