Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/)
-   .NET Office Automation (http://www.xtremevbtalk.com/-net-office-automation/)
-   -   VB.Net / Excel using ACE OLEDB (http://www.xtremevbtalk.com/-net-office-automation/327732-vb-net-excel-using-ace-oledb.html)

kingesk 08-15-2015 05:21 AM

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


All times are GMT -6. The time now is 04:31 PM.

Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Search Engine Optimisation provided by DragonByte SEO v2.0.15 (Lite) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.