Michael Benowitz
Posts: 32
Joined: 2017-02-03
|
Our add-in pulls info from sql. The sql server has multiple databases and I prompt the user for what database they want to read data from. Is there a way to store the database name in the spreadsheet where the user can't see the value.
Thanks
Mike |
|
Marcus Datascout
Guest
|
Hi Mike,
The 'cleanest' way I can think of to save small amounts of data in a spreadsheet is using custom workbook properties.
However, depending on you specific needs, even this approach may not be suitable.
For example, it makes the data you're storing, workbook specific. Also most users won't think to look at the custom workbook properties, but it is still accessible.
Not only is the data now document specific (if you don't have a given workbook open, the data will not be available) it also makes it difficult to manage that data when it is dispersed over (potentially) many workbooks.
For my add-ins I store data in a local relational database. I use MS SQL Server CE although there's many other choices such as SQLite, Access etc.
This centralises your data, provides a level of security (you can password protect & encrypt the database) and let's you store much more data than with document properties.
Cheers - Marcus |
|
Andrei Smolin
Add-in Express team
Posts: 19138
Joined: 2006-05-11
|
Thank you Marcus!
Hello Mike,
I agree with Marcus: although you can save some data to Workbook.CustomDocumentProperties (cast it to Office.DocumentProperties), the property will be accessible by the user. You can also create a worksheet and hide it using Worksheet.Visible=Excel.XlSheetVisibility.xlSheetVeryHidden; see https://msdn.microsoft.com/en-us/library/office/ff821673.aspx. Still all such ways smell.
Michael Benowitz writes:
Is there a way to store the database name
I would suggest storing this info in a .config file.
Andrei Smolin
Add-in Express Team Leader |
|
Niels Ziegler
Guest
|
|
Michael Benowitz
Posts: 32
Joined: 2017-02-03
|
I think Marcus's idea will work best for me. It's actually a benefit if users can see what database the spreadsheet is associated with. It also means if the spreadsheet is shared with someone the database info will follow.
Any examples of setting custom spreadsheet properties.
Thanks,
Mike |
|
Andrei Smolin
Add-in Express team
Posts: 19138
Joined: 2006-05-11
|
|