none
Please help me : System.MissingMemberException: 'Public member 'Sheet1' on type 'Workbook' not found.' RRS feed

  • Question

  • I want to import from excel with combobox

    It's error line 5:    Dim RetailerWorksheet = RetailerWorkbook.Sheet1 

    My code :

    Imports Microsoft.Office.Interop.Excel
    Public Class Form1
        Dim ExcelApp = New Microsoft.Office.Interop.Excel.Application
        Dim RetailerWorkbook = ExcelApp.Workbooks.open("I:\Retailer.xlsx")
        Dim RetailerWorksheet = RetailerWorkbook.Sheet1

        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            Dim iStaredRow As Integer
            Dim iTotalrows As Integer
            iTotalrows = ExcelApp.ActiveWorkbook.Sheet(1).Rang("a1").Currentregion.rows.count
            For iStaredRow = 2 To iTotalrows
                If Me.ComboBox1.Text = RetailerWorksheet.Cell(iStaredRow, 1).text Then
                    Me.ComboBox1.Text = RetailerWorksheet.Cells(iStaredRow, 2).text
                End If
            Next
        End Sub

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim StartedRow As Integer
            Dim TotalRows As Integer
            Me.ComboBox1.Items.Clear()

            TotalRows = ExcelApp.ActiveWorkbook.sheet(1).Range("a1").Curremtregion.Row.Count

            For StartedRow = 2 To TotalRows
                Me.ComboBox1.Items.Add(RetailerWorksheet.cell(StartedRow, 1).Text)
            Next

            MessageBox.Show(" Reatiler name has been successfully added into the combobox.", " combobox status")
        End Sub
    End Class

    Tks all!

    Tuesday, November 19, 2019 6:29 AM

All replies

  • Hi,

    You can try to change your code to mine as follows and you can focus on marked places:

    Imports Microsoft.Office.Interop.Excel
    Public Class Form1
        Dim ExcelApp = New Microsoft.Office.Interop.Excel.Application
        Dim RetailerWorkbook = ExcelApp.Workbooks.open("D:\student.xlsx")
        Dim RetailerWorksheet = RetailerWorkbook.Worksheets(1)
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            Dim iStaredRow As Integer
            Dim iTotalrows As Integer
            iTotalrows = ExcelApp.ActiveWorkbook.Worksheets(1).UsedRange.CurrentRegion.Rows.Count
            For iStaredRow = 2 To iTotalrows
                Dim value As String = Me.ComboBox1.SelectedItem
                If value = (RetailerWorksheet.Cells(iStaredRow, 1).Value) Then
                    ComboBox2.Text = RetailerWorksheet.Cells(iStaredRow, 2).Value
                End If
            Next
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim StartedRow As Integer
            Dim TotalRows As Integer
            Me.ComboBox1.Items.Clear()
    
            TotalRows = ExcelApp.ActiveWorkbook.Worksheets(1).UsedRange.CurrentRegion.Rows.Count
            For StartedRow = 2 To TotalRows
                ComboBox1.Items.Add(RetailerWorksheet.Cells(StartedRow, 1).Value)
            Next
            Me.Invalidate()
            MessageBox.Show(" Reatiler name has been successfully added into the combobox.", " combobox status")
        End Sub
    End Class

    Hope it be helpful.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Alex Li-MSFT Wednesday, November 20, 2019 5:19 AM
    Tuesday, November 19, 2019 10:17 AM
    Moderator
  • Yes, It's Working. Thank you very much!
    Wednesday, November 20, 2019 4:39 AM
  • Hi,

    Do you resolve the issue? If you resolve the issue, please mark the helpful as answer. It will be beneficial to other community.

    Best Regards,

    Julie


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 20, 2019 5:21 AM
    Moderator