Solved

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

Posted on 2007-03-24
8
1,709 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

713 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