Solved

Writing to Excel Named Ranges using OLEDB and VB.NET 2005

Posted on 2007-03-24
8
1,693 Views
Last Modified: 2013-11-26
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.
0
Comment
Question by:darcher23
8 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 18786921
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
 

Author Comment

by:darcher23
ID: 18789842
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
 
LVL 45

Expert Comment

by:patrickab
ID: 18791439
darcher23,

That's not my area of knowledge.

Patrick

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18798916
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
 

Author Comment

by:darcher23
ID: 19166089
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
 
LVL 45

Expert Comment

by:patrickab
ID: 19166588
I suggest PAQ and points refunded as the questionner solved his own problem and has now reported the solution.

Patrick
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 19202771
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now