darcher23
asked on
Writing to Excel Named Ranges using OLEDB and VB.NET 2005
Hi Experts,
I'm trying to write some code in VB.NET 2005 to write data to Excel using OLEDB, and VB.NET 2005. Specifically I need to be able to:
a) Write data to an Excel spreadsheet to a single cell defined named range (i.e. the named range called "CustomerName")
b) Write data to a specific cell within a named range (i.e. Row(0).Column(4) within named range "Orders")
c) To be able to defined a range within Excel (ie. Sheet1 A1:F100 = "Orders")
There is some urgency so I'm generous with the points. Good luck!
Cheers,
David.
I'm trying to write some code in VB.NET 2005 to write data to Excel using OLEDB, and VB.NET 2005. Specifically I need to be able to:
a) Write data to an Excel spreadsheet to a single cell defined named range (i.e. the named range called "CustomerName")
b) Write data to a specific cell within a named range (i.e. Row(0).Column(4) within named range "Orders")
c) To be able to defined a range within Excel (ie. Sheet1 A1:F100 = "Orders")
There is some urgency so I'm generous with the points. Good luck!
Cheers,
David.
ASKER
Hi Patrick,
I need to use OLEDB as I don't want instantiate Excel. I need code that looks a little like the snippet below (only it needs to work!).
Dim m_sConn1 As String
m_sConn1 = "Provider=Microsoft.Jet.OL
"Data Source=" & strPath.ToString & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim conn1 As New System.Data.OleDb.OleDbCon
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCom
cmd.Connection = conn1
cmd.CommandText = "UPDATE [Sheet1$] SET [CompanyName] = '99999'"
cmd.ExecuteNonQuery()
conn1.Close()
I need to use OLEDB as I don't want instantiate Excel. I need code that looks a little like the snippet below (only it needs to work!).
Dim m_sConn1 As String
m_sConn1 = "Provider=Microsoft.Jet.OL
"Data Source=" & strPath.ToString & ";" & _
"Extended Properties=""Excel 8.0;HDR=NO"""
Dim conn1 As New System.Data.OleDb.OleDbCon
conn1.Open()
Dim cmd As New System.Data.OleDb.OleDbCom
cmd.Connection = conn1
cmd.CommandText = "UPDATE [Sheet1$] SET [CompanyName] = '99999'"
cmd.ExecuteNonQuery()
conn1.Close()
darcher23,
That's not my area of knowledge.
Patrick
That's not my area of knowledge.
Patrick
If you use a recordset you can do it (this is VBA code but hopefully the gist is clear:
Sub UpdateNameData()
' Sample demonstrating how to return a recordset from an open workbook
Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Database\export_
"Extended Properties=""Excel 8.0;HDR=NO"""
.Open
End With
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cn
.CursorType = 3 'Static cursor.
.LockType = 2 'Pessimistic Lock.
.Source = "Select * from [CompanyName]"
.Open
.Fields(0).value = "99999"
.Update
.Close
End With
cn.Close
Set cn = Nothing
End Sub
HTH
Rory
Sub UpdateNameData()
' Sample demonstrating how to return a recordset from an open workbook
Dim cn As ADODB.Connection, strQuery As String, rst As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Database\export_
"Extended Properties=""Excel 8.0;HDR=NO"""
.Open
End With
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = cn
.CursorType = 3 'Static cursor.
.LockType = 2 'Pessimistic Lock.
.Source = "Select * from [CompanyName]"
.Open
.Fields(0).value = "99999"
.Update
.Close
End With
cn.Close
Set cn = Nothing
End Sub
HTH
Rory
ASKER
Hi All,
Just to wrap this up, I ended getting a series of strange errors using OLEDB together with large spreadsheets with defined named ranges. I ended up using a 3rd party component called GemBox Professional.
Cheers,
David.
Just to wrap this up, I ended getting a series of strange errors using OLEDB together with large spreadsheets with defined named ranges. I ended up using a 3rd party component called GemBox Professional.
Cheers,
David.
I suggest PAQ and points refunded as the questionner solved his own problem and has now reported the solution.
Patrick
Patrick
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sub macro1()
ActiveWorkbook.Names.Add Name:="CustomerName", RefersToR1C1:="=Sheet1!R1C
[CustomerName] = 12 'Puts 12 into 'CustomerName'
ActiveWorkbook.Names.Add Name:="Orders", RefersToR1C1:="=Sheet1!R1C
[Orders].Cells(1, 4) = 13 'Places 13 in row 1, column 4 of the range named 'Orders'
End Sub
Hope that helps
Patrick