Loop through rows in an Excel Spreadsheet?

Visual Basic .NET » Database Programming

Loop through rows in an Excel Spreadsheet? -- LaurenQuantrell --

I am very new to VB.NET so I apologize for my near utter lack of knowledge. I've been developing in VBA for a very long time so I'm hoping this transition won't be too difficult.

In an Access mdb database I have created a link to an Excel spreadsheet and then referred to the linked name as if it was a database table,
looping through rows in VBA to extract column data. Simple.

Is there anyway in a VB.NET project to reference the Excel spreadsheet and do the same? For this particular application I cannot import the Excel data into any database first.
What I need to do specifically is to extract every row from a specific column in this spreadsheet.

I appreciate any help on this.
lq

-- LaurenQuantrell --

In case anyone is looking for the same solution, I found one and am posting it here:
lq

Public Sub GetExcelData(ByVal strPath As String, ByVal intMaxQty As Long)

Dim conn As New OleDbConnection
Dim salesReader As OleDbDataReader
Dim connString As String
Dim cmd As New OleDbCommand

Dim intCount As Long
Dim strE As String
Dim strColText As String

Try

' If the user has set zero or a negative value, default the setting to 30:
If intMaxQty < 1 Then intMaxQty = 30

' Set the connection string.
connString = "Data Source=" & _
strPath & ";" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;"

' Open the connection.
conn.ConnectionString = connString
conn.Open()

' Set the command properties.
cmd.Connection = conn
cmd.CommandText = "SELECT <ColumnName> from

"

' Get the OleDbDataReader and do some processing with it.
salesReader =
cmd.ExecuteReader(CommandBehavior.CloseConnection)

Try
' Loop through the records.
While (salesReader.Read)

' Get the email value.
strColText = CStr(salesReader.GetValue(0))

' If the current count is less than the maximum qualtity allowed add it.
If intCount < intMaxQty Then
strE = strE & strColText & ";"
intCount = intCount + 1
Else ' Maximum quantity exceeded so add three carriage returns.
strE = Left(strE, Len(strE) - 1) & vbCrLf &
vbCrLf & vbCrLf
intCount = 0
End If

End While

' Clean the training ";".
If Right(strE, 1) = ";" Then
strE = Left(strE, Len(strE) - 1)
End If

' Copy the string to the Windows clipboard.
Clipboard.SetDataObject(strE, True)

' Open the Windows notepad.
Shell("Notepad.exe", vbNormalFocus)

' Use SendKeys to paste the contents of the Windows clipboard to Notepad.
SendKeys.Send("^V")

Finally
salesReader.Close()
End Try

Catch ex As Exception
Dim strError As String
strError = ex.Message
Select Case strError
Case "External table is not in the expected format."
MessageBox.Show("You must resave the Excel spreadsheet in an Excel format.", _
"Excel Format Error")
Case Else
MessageBox.Show("An error has occurred: " &
ex.Message)
End Select

End Try

End Sub

Home