CODEWITHSUNDEEP
c#excelvstoexcel-addins
Steffen H
Steffen H

Reputation: 45

Get Range of a previously marked Excel cell in VSTO

I'm writing a VSTO Add-In for Excel 2013/2016 in C#.

The add-in needs to mark a specific cell in an Excel worksheet somehow. (This mark needs to survive the closing of the workbook.) Then the Add-In needs to insert data at this specific cell. Therefore I need to get the position/range of this cell.

What is the best way to mark a cell and how do i get the position of that marked cell?

Is there a better solution than searching every cell for a keyword like "&?%insertHere" ?

Upvotes: 2

Views: 346

Answers (2)

Steffen H
Steffen H

Reputation: 45

One way to mark a cell is naming it:

// Name a cell (here: the active cell in the active Excel sheet)
Excel.Range cell = Globals.ThisAddIn.Application.ActiveCell;
cell.Name = "MyMarkedCell";

// Get a cell by its name
Worksheet sheet = Globals.ThisAddIn.Application.ActiveSheet;
Excel.Range cell = sheet.get_Range("MyMarkedCell");
// Careful: get_Range throws an Exception, when there is no cell with the given name.

Pros:

  • Has no effect on the value/content of the cell, and is therefore not seen in the (printed) document
  • can be done in Excel application and in code
  • easy adding or editing of named cells
  • is saved in the workbook file and survives closing
  • accessing a named cell is fast, because the application doesn't iterate over all cells of the sheet

Cons:

  • Only one cell can be named with a specific name. If this name is assigned to another cell, the old one is simply unnamed. So a user could easily destroy the whole procedure.

Upvotes: 2

aduguid
aduguid

Reputation: 3195

You could insert a comment into the cell. Then loop the used range per sheet looking for the comment.

Excel.Range cell; 
cell.AddComment("Insert Here");

Upvotes: 2

Related Questions