Converting "range" type to "integer" in Visual Basic .NET

Question

I wrote this codes in vb.net. I want to use this function to plot a simple mathematics operation but this error appears during the compilation.
Please help me to correct that..

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Function ExcelReader(ByVal Address As String, ByVal SheetName As String, ByVal StartRow As Integer, ByVal EndRow As Integer, ByVal StartColumn As Integer, ByVal EndColumn As Integer) As String(,)
        Dim Contents(EndRow - StartRow + 1, EndColumn - StartColumn + 1) As String
        Dim ExcelInterface As New Excel.Application
        Dim Workbook As Excel.Workbook
        Dim Sheet As Excel.Worksheet

        Workbook = ExcelInterface.Workbooks.Open("address")
        Sheet = Workbook.Sheets(SheetName)

        For Row As Integer = StartRow To EndRow
            For Column As Integer = StartColumn To EndColumn
                Contents(Row - StartRow, Column - StartColumn) = Sheet.Cells(Row, Column)
            Next
        Next

        Return Contents
    End Function
End Class

Reza Aghaei · Accepted Answer · 2015-12-17 18:51:21Z

1

You can read the value of the range this way:

DirectCast(Sheet.Cells(Row, Column), Excel.Range).Value

Or simply this way:

Sheet.Cells(Row, Column).Value

answered Dec 17, 2015 at 18:51

Reza Aghaei

126k18 gold badges239 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Karen Payne · Accepted Answer · 2015-12-17 22:32:07Z

See if this alternate would be acceptable. Note that all objects used are disposed of properly which leads to more code in the long run.

Example call

Dim demo As New ExcelIterate With
    {
        .FileName = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Countries.xlsx"),
        .SheetName = "countrylist"
    }
Dim results As Object(,) = demo.OpenExcelIterate("A9", "B15")

Class

Option Strict On
Option Infer Off

Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Public Class ExcelIterate
    Public Property FileName As String
    Public Property SheetName As String

    Public Function OpenExcelIterate(ByVal StartCell As String, ByVal LastCell As String) As Object(,)

        Dim Results(,) As Object = Nothing

        If IO.File.Exists(FileName) Then

            Dim Proceed As Boolean = False
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next
            If Proceed Then

                Dim xlUsedRange As Excel.Range = xlWorkSheet.Range(StartCell, LastCell)
                Dim DecimalTest As Decimal = 0

                Try
                    Results = CType(xlUsedRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))
                Finally
                    ReleaseComObject(xlUsedRange)
                End Try

            Else
                MessageBox.Show(SheetName & " not found.")
            End If

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlCells)
            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)
        Else
            MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
        End If

        Return Results

    End Function

    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            If obj IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            End If
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Class

Collectives™ on Stack Overflow

Converting "range" type to "integer" in Visual Basic .NET

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related