Arrays in Excel VBA (97 sr2)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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 . For example, since your code has Array(18, 4), you should add arrColumns(18, 2) = 4.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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 . For example, since your code has Array(18, 4), you should add arrColumns(18, 2) = 4.

Reply | Quote

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

Reply | Quote

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

Reply | Quote