CODEWITHSUNDEEP
vb.netexcelnamed-ranges
joeb
joeb

Reputation: 877

Setting value of named range in Excel

I apologize if this is a total noob question. I'm in the process of writing an addin for Excel. I have a sub inside class1 that opens an excel file, in this sub I have a reference to sub2 which is below. All I am looking to do is hook into the active instance of Excel, change a named range value and exit. But I keep getting errors no matter which way that I try. Here is what I have. Tell me where I have gone wrong. Forgot to mention, this is in VB.NET.

   Private Sub SetRangeValue(ByVal RangeName As String, ByVal RangeValue As String)
    Dim ExcelApp As Excel.Application
    Dim TheRange As Excel.Range
    Dim TheRangeName As String = ""

    'Hook into running excel instance
    ExcelApp = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)

    'First Attempt Here
    TheRange = ExcelApp.ActiveWorkbook.Names.Item(RangeName)
    TheRange.Value = RangeValue

    'Second Attempt
    TheRange = ExcelApp.Range(RangeName)
    TheRange.Value = RangeValue
End Sub

I can't get either one to work. Any help is appreciated.

Upvotes: 1

Views: 5237

Answers (3)

Borrell Fankam
Borrell Fankam

Reputation: 11

I just made a sub that would make it easier me to find & replace using my named ranges:

Private Sub XlFindReplace(ByRef xSheet As Excel.Worksheet, ByVal cellName As String, ByVal NewText As String)
        xSheet.Range(cellName).Value = NewText
    End Sub

then T'd call it like this in order to replace stuff:

XlFindReplace(xlC1Sheet, "client1Co1Tax", client1Co1Tax)
  • where the xlC1Sheet is the sheet I'm currently
  • the "client1Co1Tax"is the name of the range in excel
  • and client1Co1Tax is the string variable I'm replacing it

Thanks to everyone for their input.

Upvotes: 0

joeb
joeb

Reputation: 877

Finally I got this to work properly. This is how it needed to work. Thanks for all the help.

Private Sub SetRangeValue(ByVal RangeName As String, ByVal RangeValue As String)
    Dim ExcelApp As Excel.Application
    'Dim TheRangeObj As Excel.Range
    Dim TheRange As Microsoft.Office.Interop.Excel.Name
    Dim TheRangeName As String = ""


    'Hook into running excel instance
    ExcelApp = CType(Marshal.GetActiveObject("Excel.Application"), Excel.Application)

    TheRange = ExcelApp.ActiveWorkbook.Names.Item(RangeName)
    TheRange.RefersToRange.Value = RangeValue

End Sub

Upvotes: 2

volody
volody

Reputation: 7189

'First Attempt Here
TheRange = ExcelApp.ActiveWorkbook.Names.Item(RangeName)
TheRange.Value = RangeValue

According to Names.Item Method (Excel), this function returns a single Name object from a Names collection. In this case TheRange is not valid name for this variable, it should be TheName. Then

TheName.Value = RangeValue

is not right assignment; According to Name.Value Property (Excel) this property - Returns or sets a String value that represents the formula that the name is defined to refer to.

Error 0x800A03EC, there are a number of reasons this error is returned from Excel - the most common is when attempts to write data larger than Excel can handle. For example, you try to write a string longer than 1024 characters to a cell in Excel

Upvotes: 1

Related Questions