none
Reading data from Excel Column out of range exception RRS feed

  • Question

  • Hello, I am trying to build an app to read all the information from an excel column, determine if it contains a set of information, then count the number of times it reoccurs in that column. Specifically, how many items are determined to be 1, 2, 3, or 4 day ship times. The reason for this little app is that (well my spouse asked me to make one lol) but he works with really large excel files and he typically has to figure this out on his own through a long process in excel. Anyways, this is currently what I have and I am getting an exception thrown that I am out of range but I cannot seem to figure out why it is going to out of range. Any help would be greatly appreciated, thank you in advance. 

     Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim range As Excel.Range
            Dim rCnt As Integer
            Dim cCnt As Integer = whichShipColumn
    
            Dim day1 As Integer = 0
            Dim day2 As Integer = 0
            Dim day3 As Integer = 0
            Dim day4 As Integer = 0
    
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Open(selectedFile)
            xlWorkSheet = xlWorkBook.Worksheets(1)
            range = xlWorkSheet.UsedRange
    
    
            Dim xlArray As System.Array = range.Value
    
            For rCnt = 1 To range.Rows.Count
                'This is where the exception is thrown
                Dim xlArVal As String = xlArray(rCnt, cCnt).ToString
    
                Select Case xlArVal
                    Case xlArVal.ToString.Contains("1")
                        day1 += 1
                    Case xlArVal.ToString.Contains("2")
                        day2 += 1
                    Case xlArVal.ToString.Contains("3")
                        day3 += 1
                    Case xlArVal.ToString.Contains("4")
                        day4 += 1
    
                End Select
    
            Next

    • Moved by Neda Zhang Wednesday, October 12, 2016 9:41 AM Excel relevant
    Wednesday, October 12, 2016 2:40 AM

All replies

  • Hi ccr05,

    Since your issue seems more related with the Excel, I will move this thread to the more related forum.

    Reference: https://social.msdn.microsoft.com/Forums/enUS/home?forum=exceldev

    Thanks for your understanding and support.

    Best Regards,

    Neda Zhang


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 12, 2016 7:39 AM
  • Here is a simple example using OleDb as in your case there is nothing to gain from using Excel automation.

    In the code below we are reading one column

    Public Sub Counter()
        Dim dt As New DataTable
        Dim itemList As New List(Of String) From {"1", "2", "3", "4"}
        '
        ' Configured to open our file in the same folder as the app.
        ' HDR tells Excel we have data in the first row of the sheet or sheets to read
        '
        Dim connectionString As String =
            $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SampleData.xlsx")};Extended Properties=""Excel 12.0;IMEX=2;HDR=No;"""
        Using cn As New OleDb.OleDbConnection With {.ConnectionString = connectionString}
            '
            ' Select column A from Sheet1
            ' If the first row was column name we need to configure
            ' the connection string differently and change F1 to the
            ' column name used in the sheet.
            '
            Using cmd As New OleDb.OleDbCommand With
                {
                    .CommandText = "SELECT F1 FROM [Sheet1$]",
                    .Connection = cn
                }
    
                cn.Open()
    
                dt.Load(cmd.ExecuteReader)
    
                Dim result As Integer = 0
                For Each item In itemList
                    ' do the count
                    result = dt.AsEnumerable.Select(Function(row) row).Where(Function(row) row.Field(Of String)("F1").Contains(item)).Count
                    ' show results
                    Console.WriteLine($"{item} - {result}")
                Next
    
            End Using
        End Using
    End Sub

    If you needed to read more than one column e.g. column A and B we change our SELECT statement

    Using cmd As New OleDb.OleDbCommand With
    {
        .CommandText = "SELECT F1, F2 FROM [Sheet3$]",
        .Connection = cn
    }

    For the first code sample here is our data, amount of data does not matter unless there is an obscene amount of data. 

    Results that were displayed to the IDE Output window

    1 - 3
    2 - 2
    3 - 2
    4 - 1

    We can do a MessageBox

    Dim result As Integer = 0
    Dim sb As New Text.StringBuilder
    For Each item In itemList
        ' do the count
        result = dt.AsEnumerable.Select(Function(row) row).Where(Function(row) row.Field(Of String)("F1").Contains(item)).Count
        ' show results
        sb.AppendLine(($"{item} - {result}"))
    Next
    MessageBox.Show(sb.ToString)

    Note any time you see something like this $"{item} - {result}" and get an error means that you are using an earlier version of Visual Studio, replace that with String.Format.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, October 12, 2016 9:26 AM
  • It does not seem to be VBA but one likely cause can be below line.

    Dim xlArray As System.Array = range.Value
    
            For rCnt = 1 To range.Rows.Count
                'This is where the exception is thrown
                Dim xlArVal As String = xlArray(rCnt, cCnt).ToString
    

    You are using an xlArray to hold the range.value. Check once it's dimension. In VBA we use VARIANT for that and get a two dimensional array. Is it same for xlArray here ? It may be one dimensional.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, October 12, 2016 10:39 AM
    Answerer
  • Hi,

    Does line "Dim xlArVal As String = xlArray(rCnt, cCnt).ToString"  cause the exception?

     

    You could use System.Array to store the range.value in VB.NET.

    However, since you are using UsedRange to get the range, it might cause another exception Object reference not set to an instance of an object, because it might have null value in the cells.

    I suggest you check the sheet data, make sure the cell in usedrange would not be null.

     

    In my opinion, your current issue causes from cCnt. What is your whichShipColumn?

    Thursday, October 13, 2016 6:15 AM
    Moderator