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.
darcher23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patrickabCommented:
darcher23,

Sub macro1()
    ActiveWorkbook.Names.Add Name:="CustomerName", RefersToR1C1:="=Sheet1!R1C1:R1C1" 'Names cell A1 'CustomerName'
    [CustomerName] = 12 'Puts 12 into 'CustomerName'
    ActiveWorkbook.Names.Add Name:="Orders", RefersToR1C1:="=Sheet1!R1C1:R4C6" 'Names range A1:F4 'Orders'
    [Orders].Cells(1, 4) = 13 'Places 13 in row 1, column 4 of the range named 'Orders'
End Sub

Hope that helps

Patrick
0
darcher23Author Commented:
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.OLEDB.4.0;" & _
                              "Data Source=" & strPath.ToString & ";" & _
                              "Extended Properties=""Excel 8.0;HDR=NO"""

            Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
            conn1.Open()

            Dim cmd As New System.Data.OleDb.OleDbCommand
            cmd.Connection = conn1
            cmd.CommandText = "UPDATE [Sheet1$] SET [CompanyName] = '99999'"
            cmd.ExecuteNonQuery()
           
            conn1.Close()

0
patrickabCommented:
darcher23,

That's not my area of knowledge.

Patrick

0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Rory ArchibaldCommented:
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_test.xls;" & _
            "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
0
darcher23Author Commented:
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.
0
patrickabCommented:
I suggest PAQ and points refunded as the questionner solved his own problem and has now reported the solution.

Patrick
0
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.