Load File from excel if it has an existing cell

[ichthuso1]

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:

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

14
Dim r As Single
15
Dim l As Object
16
For r = 1 To 10
17
l = worksheet1.Cells(r, 1).Value
18
ListBox1.Items.Add(l)
19
Next
20
ListBox3.Items.Clear()
21
ListBox3.Items.AddRange(ListBox1.Items.Cast(Of String).Except(ListBox2.Items.Cast(Of String)).ToArray)
22
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:

01
Dim r, n As Single
02
Dim l As Object
03
Dim non_empty As Boolean = True
04
Do While non_empty = True
05
For r = 1 To n
06
l = worksheet1.Cells(r, 1).Value
07
If worksheet1.Cells(r, 1).value <> "" Then
08
ListBox1.Items.Add(l)
09
n = r + 1
10
Else
11
non_empty = False
12
End If
13
Next
14
Loop


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

[kareninstructor]

It would be easier to use OleDb as shown below. For this example set an OpenDialog up with a filter "Excel|*.xls|Excel 2007|*.xlsx"

Public Class frmExcel1
    Private dtSheet As New DataTable
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If OpenFileDialog1.ShowDialog = DialogResult.OK Then

            dtSheet = New DataTable

            Using cn As New OleDbConnection With
                {
                    .ConnectionString = TheConnectionString(OpenFileDialog1.FileName)
                }

                Using cmd As New OleDbCommand With
                    {
                        .Connection = cn,
                        .CommandText = "SELECT F1 As Column1 FROM [Sheet1$] ORDER BY F1 ASC"
                    }

                    cn.Open()

                    Try
                        dtSheet.Load(cmd.ExecuteReader)
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                    End Try
                End Using
            End Using

            ListBox1.DataSource = Nothing
            ListBox1.DisplayMember = "Column1"
            ListBox1.DataSource = dtSheet
        End If
    End Sub
    Private Function TheConnectionString(ByVal FileName As String) As String
        Dim Builder As New OleDbConnectionStringBuilder With {.DataSource = FileName}

        If FileName.ToLower.EndsWith(".xlsx") Then
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", "Excel 12.0;HDR=NO;IMEX=1;")
        Else
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", "Excel 8.0;IMEX=0;HDR=No;")
        End If
        Return Builder.ConnectionString
    End Function
End Class