View Single Post
 
Old 08-15-2015, 05:21 AM
kingesk kingesk is offline
Centurion
 
Join Date: May 2004
Posts: 108
Default VB.Net / Excel using ACE OLEDB

I have another project where I'm trying to work with VB.Net and Excel by using the ACE OLEDB calls. I am able to get an existing worksheet name and read from excel but I cannot get it to create a new worksheet or write new data to an existing worksheet. I was wondering if anyone had any ideas what I'm doing wrong?


This on works

Private Shared Function GetWorkSheetNames(ByVal strPath As String) As String

Dim strSheetName As String = ""
Dim dt As New DataTable
Dim stCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strPath & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";" '& _

Dim cn As New OleDb.OleDbConnection(stCon)
Try
Dim i As Integer

cn.Open()
dt = cn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, Nothing)

strSheetName = CStr(dt.Rows(i)("TABLE_NAME"))


Catch ex As Exception
Throw ex
Finally
cn.Close()
dt.Dispose()
cn.Dispose()
End Try

Return strSheetName

End Function

This on works

Public Shared Function ReadExcel(ByVal strFile As String, ByVal strSheetName As String) As DataSet
Dim lngUpdateCounter As Long = 0

Dim sqlData As String = "SELECT * FROM [" & strSheetName & "]"

Dim xlCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

Dim conn As New System.Data.OleDb.OleDbConnection(xlCon)
Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlData, conn)

Dim dsXLData As New DataSet

da.Fill(dsXLData, "Source")

Return dsXLData
End Function


This one does not work. It does not give an error but the new table (worksheet) is not created in Excel.

Public Shared Function CreateWorkSeetInExcel(ByVal strFile As String, ByVal strSheetName As String) As DataSet
Dim lngUpdateCounter As Long = 0
Dim sqlData As String = "Create table [" & strSheetName & "]" ' ()"

Dim xlCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES""; "

Dim conn As New System.Data.OleDb.OleDbConnection(xlCon)
Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlData, conn)

Dim dsXLData As New DataSet

da.Fill(dsXLData, "Source")

Return dsXLData
End Function


This one does not work. It gives an error on the "da.FIll line" below saying it cannot find the worksheet even if it really is there.

Public Shared Function WriteToWorkSheetInExcel(ByVal strFile As String, ByVal strSheetName As String, ByVal strStore As String, ByVal strLine As String, ByVal strEndDate As String, ByVal lngSumVaule As Long) As DataSet
Dim lngUpdateCounter As Long = 0

Dim sqlData As String = "INSERT INTO [" & strSheetName & "] VALUES('" & strStore & "','" & strLine & "','" & strEndDate & "','" & lngSumVaule & "')"

Dim xlCon As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

Dim conn As New System.Data.OleDb.OleDbConnection(xlCon)
Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlData, conn)

Dim dsXLData As New DataSet
da.Fill(dsXLData, "Source")

Return dsXLData
End Function
Reply With Quote