Imports System
Imports System.IO
Imports System.Collections
Module Module1

    Sub Main()
    Dim ColArray As New ArrayList()

    Dim File, Files() As FileInfo
    Dim Dir As New DirectoryInfo("C:\Users\User\Desktop\Nezavisimai\Papka2")
    Files = Dir.GetFiles(".xls*")
    For Each File In Files

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet

        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlApp.Visible = False
        xlBook = CType(xlApp.Workbooks.Open(File), Excel.Workbook)
        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

        Dim myRange As Excel.Range
        myRange = xlApp.Range("A1:C3")
        Dim myArray As Object(,)
        myArray = myRange.Value

        ColArray.AddRange(myArray)
        xlApp.Quit()

    Next

    Dim numRows As Long, numCols As Long, r As Long, c As Long, rT As Long
    numRows = ColArray.Item(1).GetUpperBound(0)
    numCols = ColArray.Item(1).GetUpperBound(1)

    Dim fArray(,) As Object, arr(,) As Object
    ReDim fArray(0 To (numRows * ColArray.Count), 0 To numCols)

    rT = 0
    'loop over collection and add each item to the final array
    For Each arr In ColArray
        For r = arr.GetLowerBound(0) To numRows
            rT = rT   1
            For c = arr.GetLowerBound(1) To numCols
                fArray(rT, c) = arr(r, c)
            Next c
        Next r
    Next arr

    For i As Integer = 0 To fArray.GetUpperBound(0)
        For j As Integer = 0 To fArray.GetUpperBound(1)
            Console.Write("{0} ", fArray(i, j))
        Next
        Console.WriteLine()
    Next
End Sub
End Module

It does not work. Error. Problem with AddRange 2D arrays into Arraylist. I am getting an error saying: Different size of 2D arrays and ArrayList. Dim ColArray As New ArrayList((,)) - error. How does it work? How to combine 2D arrays in ArrayList?

CodePudding user response：

I guess I got your code working. Explanation of changes in comments in the code.

Dim ColArray As New List(Of Object(,))() ' changed to List

Dim di As New DirectoryInfo("C:\Users\danverdolino\Desktop\Nezavisimai\Papka2")
' changed name of File to fi, File is a class name in System.IO
For Each fi In di.GetFiles("*.xls*") ' wildcard was missing leading *
    ' object declarations and assignments in one line, implicit declarations
    Dim xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
    Dim xlBook = xlApp.Workbooks.Open(fi.FullName)
    Dim xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
    xlApp.Visible = False
    ' again no need to declare and assign in two lines
    Dim myRange = xlApp.Range("A1:C3")
    Dim myArray = myRange.Value
    ' just adding the array, not adding a range of them
    ColArray.Add(myArray)
    xlApp.Quit()
Next

' removed all those control variable declarations. they are unnecessary in vb.net
Dim numRows = ColArray.Item(1).GetUpperBound(0)
Dim numCols = ColArray.Item(1).GetUpperBound(1)
' no need for Dim, ReDim, just Dim
Dim fArray(numRows * ColArray.Count - 1, numCols) As Object ' first dimension was fixed

Dim rT = 0
'loop over collection and add each item to the final array
For Each arr In ColArray
    For r = arr.GetLowerBound(0) To numRows
        For c = arr.GetLowerBound(1) To numCols
            fArray(rT, c) = arr(r, c)
        Next c
        rT  = 1
    Next r
Next arr

For i As Integer = 0 To fArray.GetUpperBound(0)
    For j As Integer = 0 To fArray.GetUpperBound(1)
        Console.Write("{0} ", fArray(i, j))
    Next
    Console.WriteLine()
Next