Shaft0rz

Hello all!
I'm working on a small Excel add-in to add the ability to pull data into a spreadsheet. I've got my custom task pane working, my button working, and the data pulling correctly. I'm consistently hitting one stumbling block though: How do I get at an arbitrary Cell/Row/Column from the button click event of the add-in

Here's a description of the data I'm working with, to make my question more clear. I have a series of rows, with 3 columns. The first is an ID that I can query against the database. The second two are data fields. I want to scroll through all the rows, compare the data in the database against the data in the 2 columns. If it's different, format the cell and update the data. My problem is scrolling through the rows, and pulling individual cells in those rows. The only way I've been able to get at ANY data in the sheet is by using

Globals.ThisAddIn.Application.ActiveCell.Value2.ToString();

I've seen examples of Excel document projects that do something as simple as:
Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
this.Controls.AddNamedRange(this.Range["A1", missing], "NamedRange1");

... but AddNamedRange doesn't seem to be visible to add-ins (and I'd have to use System.Reflection.Missing.Value). I've tried things like:

Excel.Range myRange = (Excel.Range)thisSheet.Rows["1", "2"];

...but that produces a COM error. I have a feeling this is going to be much more simple than I'm making it, but I've found examples, articles and intellisense to all be rather "sparse" when dealing with add-ins. This is in C#, btw.

Thanks in advance for ANY pointers!

Peter


Re: Visual Studio Tools for Office How to get a NamedRange in a VSTO 2005 SE Excel Add-in?

Dennis Wallentin

Peter,

I'm a VB-person so You need to convert the following to C#:

Friend Sub Find_Value()

Dim wsSheet As Excel.Worksheet = CType(Globals.ThisWorkbook.Application.ActiveSheet, Excel.Worksheet)

Dim rnData As Excel.Range = wsSheet.Range("E10:E200")

Dim rnFind As Excel.Range

Dim stAddress As String

Globals.ThisWorkbook.Application.ScreenUpdating = False

With rnData

rnFind = .Find(What:="Test")

If Not rnFind Is Nothing Then

stAddress = rnFind.Address

Do

rnFind.EntireRow.Hidden = True

rnFind = .FindNext(rnFind)

Loop While Not rnFind Is Nothing And rnFind.Address <> stAddress

End If

End With

Globals.ThisWorkbook.Application.ScreenUpdating = True

End Sub






Re: Visual Studio Tools for Office How to get a NamedRange in a VSTO 2005 SE Excel Add-in?

Shaft0rz

Hi Dennis. Thanks for the response. That's indeed what I was looking for. The thing that was causing me issues was that the Range method is called get_Range in the C# wrapper (or is that a version difference not sure). But anyway, I'm merrily grabbing cells and ranges now. Thanks!