Solved

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

Posted on 2007-03-24
8
1,684 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

758 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

18 Experts available now in Live!

Get 1:1 Help Now