VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
Go Back  Xtreme Visual Basic Talk > > > VB.Net / Excel using ACE OLEDB


Reply
 
Thread Tools Display Modes
  #1  
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
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
 
VB.Net / Excel using ACE OLEDB
VB.Net / Excel using ACE OLEDB
 
-->