0 Replies - 337 Views - Last Post: 23 April 2012 - 02:25 PM Rate Topic: -----

#1 ichthuso1  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 18-April 12

Loading Excel Data, cutoff at empty cell

Posted 23 April 2012 - 02:25 PM

the following is a working button to load excel data into a listbox. When I first wrote it I just used a loop to pull items 1-10 to do testing. Then sufficiently forgot about it and remembered just now. So in short here's the button:

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        ListBox1.Items.Clear()
        With OpenFileDialog1
            .ShowReadOnly = False
            .Filter = "All Files|*.*|Excel Files (*)|*;*.xls;*.xlsx"
            .FilterIndex = 2
            If .ShowDialog = DialogResult.OK Then
                APP1 = New Excel.Application
                workbook1 = APP1.Workbooks.Open(.FileName)
                worksheet1 = workbook1.Worksheets("sheet1")
            End If
        End With

        Dim r As Single
        Dim l As Object
        For r = 1 To 10
            l = worksheet1.Cells(r, 1).Value
            ListBox1.Items.Add(l)
        Next
        ListBox3.Items.Clear()
        ListBox3.Items.AddRange(ListBox1.Items.Cast(Of String).Except(ListBox2.Items.Cast(Of String)).ToArray)
    End Sub



My simple For loop is what generates the data, and need that to just pull in everything in column A that has data (can't blindly extend the number 'cause errors arise).

Was looking into a Boolean loop so changed that part to read:

        Dim r, n As Single
        Dim l As Object
        Dim non_empty As Boolean = True
        Do While non_empty = True
            For r = 1 To n
                l = worksheet1.Cells(r, 1).Value
                If worksheet1.Cells(r, 1).value <> "" Then
                    ListBox1.Items.Add(l)
                    n = r + 1
                Else
                    non_empty = False
                End If
            Next
        Loop



however this loop doesn't end... thoughts/comments?

Is This A Good Question/Topic? 0
  • +

Page 1 of 1