dynamic array with n rows and columns

Question

I have to read an excel which can have 'n' rows and 'n' columns and store the data in an array for processing.

For example the below:

Author    Book    No    Value
ABC       A       1     100
DEF       D       2     20

I want to take the headers in a separate array and the Information rows in another.

Here is what I have so far:

Assuming the data starts from Row 13 in the excel worksheet-

var i, j, k ,l,m ,n;
i=13; j=1, k=0; m=0; n=0;  // i represents the row and j represents excel column(A)
while(EApp.ActiveSheet.Cells(12,j).Value!="")
{
    j=j+1; // Counting the Number of Columns (Header)
}
j=j-1; //decrementing the counter j by 1
k=j;
while(j!=0)
{
    ArrField[j]=EApp.ActiveSheet.Cells(12,j).Value;
    j=j-1;
}

This is what I tried, output is as expected, but is there a better way to code this? preferably with lesser variables.

while(EApp.ActiveSheet.Cells(i,1).Value!="undefined") 
{
    m=k;
    j=0;
    ArrData[i]=new Array(m);
    while(m!=0)
    {                                       
        ArrData[n][j]=EApp.ActiveSheet.Cells(i,m).Value;
        m=m-1;
        j=j+1;
    }
    n=n+1;
    i=+1;
}

Also I need to read the arrays and store them into corresponding fields of another system. I'm a bit lost over here.

Sample Code: (Something like this)

SetFieldValue(ArrField[0],ArrData[0][0]);

Array Output:

Value,No,Book,Author (Header got in reverse order)

100,1,A,ABC,20,2,D,DEF (2 rows of Data also got in reverse order)

Any suggestions experts?

Answer 1

you can use csv's as dynamic arrays in a quite nice way. Here is my example code. I suggest to rewrite it to your needs and add some errorhandling.

Here is the table data:

ID  AUTHOR  BOOK    NO  VALUE
1   ABC     A       1   100
2   DEFG    D       2   20
3   XYZ     Q       3   55

Here is the code:

Sub test1()

    Dim arrayname As String
    Dim mytestW As Variant
    Dim mytestR As Variant '-->your array you can work with
    'give your array a arrayname = authorbook (for example)
    arrayname = "authorbook"

    mytestW = dataintoarray(arrayname)
    mytestR = AsArray(arrayname, 2) '-->get the second row
End Sub

And here are the functions:

  Function dataintoarray(myarray As String) As Variant

  Dim res As Variant
  Dim wb As Workbook, ws As Worksheet
  Dim LastRow As Long, LastCol As Long
  Dim iRow As Integer, jCol As Integer
  Dim OutputFileNum As Integer
  Dim PathName As String
  Dim Line As String
      Line = ""

  Set wb = ThisWorkbook
  Set ws = wb.Worksheets("Sheet1")

  PathName = Application.ActiveWorkbook.Path
  OutputFileNum = FreeFile

  Open PathName & "\" & myarray & ".csv" For Output Lock Write As #OutputFileNum

  LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  LastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

  For iRow = 1 To LastRow
      For jCol = 1 To LastCol
         Line = Line & ws.Cells(iRow, jCol).Value & ","
      Next jCol
      Print #OutputFileNum, Line
      Line = ""
  Next iRow

  Close OutputFileNum

  End Function


Function AsArray(myarray As String, index As Integer) As Variant

    Dim res As Variant
    Dim wb As Workbook, ws As Worksheet
    Dim objFSO As Object, objFile As Object
    Dim sLine As String

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.OpenTextFile(Application.ActiveWorkbook.Path & "\" & myarray & ".csv", 1)
    Do Until objFile.AtEndOfStream
          sLine = objFile.ReadLine
          'the col of the ID = 0
          If Split(sLine, ",")(0) = index Then
              AsArray = Split(sLine, ",")
          End If
    Loop
    objFile.Close

 End Function

Now you have your dynamic array saved in mytestR. You can play with it like so:

 ?mytestR(0)
 2
 ?mytestR(1)
 DEFG
 ?mytestR(2)
 D
 ?mytestR(3)
 2
 ?mytestR(4)
 20