none
Store Excel 2007 Range RRS feed

  • Question

  • Hello,

     

    I am developing an Excel Add-In using VSTO 3.0, and I was interested if there is a way to save the formattings of an Excel Cell/Range. In Word there was WordOpenXML. Does someone know is in Excel there is something familiar?

     

    The only idea I have is to create a class of my own in which I will add the properties that I need. Anyone with another better idea?

     

    Best regards,

    Silviu.


    http://www.rosoftlab.net/
    Wednesday, July 20, 2011 12:38 PM

Answers

  • Hello Cindy,

     

    Can you give me a short example of how to store the style of a Cell and reuse it on a different Cell?

    I made something link this in the Ribbon of my Add-In, but it does not work

    Microsoft.Office.Interop.Excel.Style style;
    
        private void button2_Click(object sender, RibbonControlEventArgs e)
        {
          if (style == null)
          {
            style = Globals.ThisAddIn.Application.ActiveCell.Style as Microsoft.Office.Interop.Excel.Style;
          }
          else
          {
            Globals.ThisAddIn.Application.ActiveCell.Style = style;
            style = null;
          }
    

     

    The only direction I can think of is to try via Copy and Paste (using the object from the Clipboard). But this could generate strange problems.

     

    Best regards,

    Silviu.

     


    http://www.rosoftlab.net/
    Thursday, July 21, 2011 12:15 PM

All replies

  • Hi Silviu

    there's certainly nothing within VSTO itself that can help you, nor within the object model, that I'm aware of. If anyone would know that, it would be the application specialists in the Excel for Developers forum...

    Excel does have its equivalent of the Open XML file format, but you can only access it from the closed file. Perhaps if you explained why you want to store that information...?


    Cindy Meister, VSTO/Word MVP
    Thursday, July 21, 2011 11:30 AM
    Moderator
  • Hello Cindy,

    I want to be able to save a Range (with different borders on different cells inside the Range, fonts, colors) from the Excel sheet, so a user can reuse it multiple times via my Add-In, in different sheets or documents for example.

    Best regards,
    Silviu.
    http://www.rosoftlab.net/
    Thursday, July 21, 2011 11:36 AM
  • Hi Silviu

    Are you familiar with the concept of a "style"? Word uses it a lot, and most users don't even realize it. Excel uses it too, but there the concept is even less well-known. Anyway, from the Excel end-user Help:

    "To apply several formats in one step, and to make sure that cells have consistent formatting, you can use a cell style. A cell style is a defined set of formatting characteristics, such as fonts (font: A graphic design applied to all numerals, symbols, and alphabetic characters. Also called type or typeface. Arial and Courier New are examples of fonts. Fonts usually come in different sizes, such as 10 point, and various styles, such as bold.) and font sizes, number formats, cell borders, and cell shading. To prevent anyone from making changes to specific cells, you can also use a cell style that locks cells"

    And there are then instructions about how to work with styles in Excel.

    In the object model it appears to be at the Workbook level. That help says, among lots of othe things:

    "For the Workbook object, the Style object is a member of the Styles collection. The Styles collection contains all the defined styles for the workbook."


    Cindy Meister, VSTO/Word MVP
    Thursday, July 21, 2011 11:49 AM
    Moderator
  • Hello Cindy,

     

    Can you give me a short example of how to store the style of a Cell and reuse it on a different Cell?

    I made something link this in the Ribbon of my Add-In, but it does not work

    Microsoft.Office.Interop.Excel.Style style;
    
        private void button2_Click(object sender, RibbonControlEventArgs e)
        {
          if (style == null)
          {
            style = Globals.ThisAddIn.Application.ActiveCell.Style as Microsoft.Office.Interop.Excel.Style;
          }
          else
          {
            Globals.ThisAddIn.Application.ActiveCell.Style = style;
            style = null;
          }
    

     

    The only direction I can think of is to try via Copy and Paste (using the object from the Clipboard). But this could generate strange problems.

     

    Best regards,

    Silviu.

     


    http://www.rosoftlab.net/
    Thursday, July 21, 2011 12:15 PM
  • Hi Silviu

    As always with object-model specific questions, the best source of information is the forum that specializes in that application. In this case, the Excel for Developers forum.

    It appears that the built-in "New Style" tool creates styles based on example. I don't see anything in the object model that will do this, but the specialists may know of something. As far as I can tell, you have to specify each property, specifically:

    Sub PlayWithStyles()
      Dim styl1 As Excel.Style, styl2 As Excel.Style
      
      Set styl1 = ActiveWorkbook.Styles.Add("MyStylePurple")
      styl1.IncludeBorder = True
      styl1.IncludePatterns = True
      styl1.Borders.Weight = ActiveCell.Borders.Weight
      styl1.Borders.LineStyle = ActiveCell.Borders.LineStyle
      styl1.Borders.Item(xlDiagonalUp).LineStyle = ActiveCell.Borders.Item(xlDiagonalUp).LineStyle
      styl1.Borders.Item(xlDiagonalDown).LineStyle = ActiveCell.Borders.Item(xlDiagonalDown).LineStyle
      styl1.Interior.Color = ActiveCell.Interior.Color
      'breakpoint here and move the selection  
      ActiveCell.Style = styl1
    End Sub
    
    
    

     


    Cindy Meister, VSTO/Word MVP
    Thursday, July 21, 2011 12:47 PM
    Moderator
  • Hello Cindy,

     

    To manually set the style I know. My problem was when storing the style of a Cell for later use. Can you please move this topic to the Excel specialized from? Maybe there an aswer will pop-up.

    Thank you.


    http://www.rosoftlab.net/
    Thursday, July 21, 2011 12:59 PM
  • Hi Silviu

    as I mentioned in my last message, I can't move the question because you placed it in the message that's the "Answer" for the original question in this message thread. Doing that is always a questionable practice, for various reasons. It's always best to put a new question in a new message with an appropriate subject line.

    You'll need to post your question again in the Excel for Developer forum.


    Cindy Meister, VSTO/Word MVP
    Thursday, July 21, 2011 1:27 PM
    Moderator