RibTime
Member
Still trying to complete my first VB/ADO.Net piece of code. The final piece of help I need is how to write out my DataSet which I have updated back to the original Excel spreadsheet or to a new spreadsheet?
Below is my code..
Many thanks RibTime
Below is my code..
Many thanks RibTime
PHP:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim conn As System.Data.OleDb.OleDbConnection
Dim comm As System.Data.OleDb.OleDbDataAdapter
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=v:\From Outlook.xls;"
strConn = strConn & "Extended Properties=""Excel 8.0;HDR=No;IMEX=1"""
conn = New System.Data.OleDb.OleDbConnection(strConn)
comm = New System.Data.OleDb.OleDbDataAdapter("Select * From [Sheet1$]", conn)
Dim Excel As New DataSet("Data")
Dim strName As String
Dim strArray() As String
Dim intRead As Integer
Try
conn.Open()
comm.Fill(Excel)
Me.DataGrid.DataSource = Excel.DefaultViewManager
For intRead = 6 To (Excel.Tables(0).Rows.Count - 1)
strName = Excel.Tables(0).Rows(intRead).Item(5)
Excel.Tables(0).Rows(intRead).Item(10) = WordCount(strName)
Next
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
conn.Close()
conn.Dispose()
comm.Dispose()
End Try
Clean_Up:
conn = Nothing
comm = Nothing
End Sub
Private Function WordCount(ByVal input As String) As String
Dim separators As Char() = {" "c}
Dim words As String() = input.Split(separators)
Dim strEmail As String
Dim intCheck As Integer
'***********************************************************************************************
' First check to see if the name has a "()" in the string in which case we will ignore it
'***********************************************************************************************
intCheck = input.IndexOfAny("()", 1)
If intCheck > 0 Then
strEmail = "invalid"
Return strEmail
End If
'***********************************************************************************************
' Take first and last name and build2 xx Email Address.
' Ignore any name that is longer than 3 wrods
'***********************************************************************************************
Select Case (words.Length)
Case (1)
strEmail = "Invalid Address"
Case (2)
strEmail = words(1).ToLower & "_" & words(0).ToLower & "@xx.com"
Case (3)
strEmail = words(2).ToLower & "_" & words(0).ToLower & "@xx.com"
Case Else
strEmail = "No Email Found"
End Select
Return strEmail
End Function
End Class