4 Replies - 478 Views - Last Post: 10 September 2019 - 07:43 AM Rate Topic: -----

#1 pj18limited   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 09-September 19

Importing xcel data from a worksheet

Posted 09 September 2019 - 02:13 PM

Hi all, I'm trying to figure out how to import data to a datagridview from Excel worksheet. My code does not through an error, however the data is not the data in the worksheet. I get System._ComObject in my datagridview.

    Private Sub btImport_Click(sender As Object, e As EventArgs) Handles btImport.Click

        Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()

        If xlApp Is Nothing Then
            MessageBox.Show("Excel is not properly installed!!")
            Return
        End If

        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlWorkBook = xlApp.Workbooks.Open(txtMPath.Text & "\Material Price Sheet.xlsx")
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        
        Dim i As Integer
        Dim k As Integer = 0

        With dgvMaterial
            For i = 2 To 3
                For k = 1 To 3
                    .Rows(i - 2).Cells(k - 1).Value = xlWorkSheet.Cells(i, k)
                    .Rows(i - 2).Cells(k - 1).Value = xlWorkSheet.Cells(i, k)
                Next
            Next
        End With

        xlWorkBook.Close(True, misValue, misValue)

        xlApp.Quit()

        releaseObject(xlWorkSheet)
        releaseObject(xlWorkBook)
        releaseObject(xlApp)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)

        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try

    End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Importing xcel data from a worksheet

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6818
  • View blog
  • Posts: 28,228
  • Joined: 12-December 12

Re: Importing xcel data from a worksheet

Posted 10 September 2019 - 05:27 AM

.Cells returns a range, a ComObject, you probably want the Value (or Value2) property.
Was This Post Helpful? 0
  • +
  • -

#3 pj18limited   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 09-September 19

Re: Importing xcel data from a worksheet

Posted 10 September 2019 - 06:20 AM

Thanks, I'll try that.
Was This Post Helpful? 0
  • +
  • -

#4 pj18limited   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 09-September 19

Re: Importing xcel data from a worksheet

Posted 10 September 2019 - 06:58 AM

Ok, I changed to read
With dgvMaterial
            For i = 1 To 2
                For k = 1 To 3
                    .Rows(i - 1).Cells(k - 1).Value = xlWorkSheet.Value(i, k)
                Next
            Next
        End With

and I get an error "Public member 'Value' on type Worksheet not found" Not sure why.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6818
  • View blog
  • Posts: 28,228
  • Joined: 12-December 12

Re: Importing xcel data from a worksheet

Posted 10 September 2019 - 07:43 AM

That looks very much like a guess, and the error confirms that Value is not a member of a Worksheet.

 = xlWorkSheet.Cells(i, k).Value

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1