none
Store Data for Excel Sheet RRS feed

  • Question

  • Hi

    I am now new to these Excel Addin Packs i am developing a addin which gets Files Created,Modified date.

    Username as well. I have called a win form for selection of document type to identify the type of excel document.

    So after user selection the data will be stored in some public variable , but how can i store it permanently for that excel sheet. so that i can retrieve it when the excel file is opened again ?

    Request you to give me some ideas of articles to read for the same.

    Regards

    Avinash


    Thanks Avinash Desai Software Engg (.Net Windows application)

    Wednesday, August 22, 2012 6:31 AM

Answers

  • Hi Avinash

    OK... :-) It does make a difference whether 2003 or 2007 as there were major changes in the file formats starting in 2007.

    Assuming 2007, you'd have the option of storing information in a "Custom XML Part". An Excel 2007 file is actually a "zip package" of xml files. You can add your own XML file to this package with custom content that can be accessed from within the application (via your add-in) or when the file is closed. So your add-in can create a CustomXMLPart (Workbook.CustomXMLParts.Add) and assign it content, read the content, modify it, etc. The XML can be structured any way you like. You'll find working with the CustomXMLPart simpler if you assign it a namespace (xmlns declaration).

    If you need to support an earlier version than 2007, a possibility would be to use Custom Document Properties to store the information. It's important to note that the user can view and modify these, if he bothers to work his way through the menu system.

    Finally, a technique Excel developers used prior to 2007 is to insert a worksheet that's "very hidden" and store all data pertinent to the workbook in that.


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Avinash Desai Saturday, August 25, 2012 8:17 AM
    Friday, August 24, 2012 1:43 PM
    Moderator
  • Hi Avinash

    If you search (google or whatever you prefer) the term set:
    Excel worksheet "very hidden"

    For example:
    http://www.google.ch/search?q=Excel+worksheet+%22very+hidden%22&rls=com.microsoft:en-US&ie=UTF-8&oe=UTF-8&startIndex=&startPage=1&redir_esc=&ei=53A4UIriFdT04QS6pYCICg

    You'll get a lot of information from numerous sources that pretty much says it all :-) If you want to discuss the approach in more detail, I recommend the Excel for Developers forum (http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads) where you'll find application specialists who actually use the technique. (As you can see from my signature line, my specialty is Word, so my knowledge about Excel techniques is mostly theoretical.)


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Avinash Desai Saturday, August 25, 2012 8:17 AM
    Saturday, August 25, 2012 6:33 AM
    Moderator

All replies

  • Hi Avinash

    Which version of Excel are you targeting?


    Cindy Meister, VSTO/Word MVP

    Wednesday, August 22, 2012 1:58 PM
    Moderator
  • I have created a Addin for excel 2003 but when i closed and opend it again it got converted to 2007 ..As of now i think it will be 2007 Excel


    Thanks Avinash Desai Software Engg (.Net Windows application)

    Friday, August 24, 2012 4:45 AM
  • Hi Avinash

    OK... :-) It does make a difference whether 2003 or 2007 as there were major changes in the file formats starting in 2007.

    Assuming 2007, you'd have the option of storing information in a "Custom XML Part". An Excel 2007 file is actually a "zip package" of xml files. You can add your own XML file to this package with custom content that can be accessed from within the application (via your add-in) or when the file is closed. So your add-in can create a CustomXMLPart (Workbook.CustomXMLParts.Add) and assign it content, read the content, modify it, etc. The XML can be structured any way you like. You'll find working with the CustomXMLPart simpler if you assign it a namespace (xmlns declaration).

    If you need to support an earlier version than 2007, a possibility would be to use Custom Document Properties to store the information. It's important to note that the user can view and modify these, if he bothers to work his way through the menu system.

    Finally, a technique Excel developers used prior to 2007 is to insert a worksheet that's "very hidden" and store all data pertinent to the workbook in that.


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Avinash Desai Saturday, August 25, 2012 8:17 AM
    Friday, August 24, 2012 1:43 PM
    Moderator
  • Hi Cindy

    Thanks for the valuable information . I dint knew that its of XML. Thanks again..

    I am doing with the Custom XML Part and storing some data.

    Now If i need any more info on that will come back to thread, meanwhile i want to know this Very hidden worksheet . How can we hide a worksheet which user can not see ?

    Can you just provide me a link to read more about these ? or you can write here .thanks for your help .. :)


    Thanks Avinash Desai Software Engg (.Net Windows application)

    Saturday, August 25, 2012 4:16 AM
  • Hi Avinash

    If you search (google or whatever you prefer) the term set:
    Excel worksheet "very hidden"

    For example:
    http://www.google.ch/search?q=Excel+worksheet+%22very+hidden%22&rls=com.microsoft:en-US&ie=UTF-8&oe=UTF-8&startIndex=&startPage=1&redir_esc=&ei=53A4UIriFdT04QS6pYCICg

    You'll get a lot of information from numerous sources that pretty much says it all :-) If you want to discuss the approach in more detail, I recommend the Excel for Developers forum (http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads) where you'll find application specialists who actually use the technique. (As you can see from my signature line, my specialty is Word, so my knowledge about Excel techniques is mostly theoretical.)


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Avinash Desai Saturday, August 25, 2012 8:17 AM
    Saturday, August 25, 2012 6:33 AM
    Moderator
  • Hi cindy

    Thanks for the help . I am done with your help. I am now be able to store the Value in the form of XML.

    Will post the code soon , so that it mite help someone else :)


    Thanks Avinash Desai Software Engg (.Net Windows application)

    Saturday, August 25, 2012 9:03 AM