Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-03-24
8
Medium Priority
?
1,730 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

670 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