Storing important values: ini file, XML, Registry, where else ?
For small amounts of data, there seems to be several ways to store
important values and parameters used in an Excel application. I've always thought that using the ini file approach is the best, but there are no built-in functions in Excel to read and write this data. XML seems too verbose and again, there are no XML parsers built into Excel. Storing values in the Windows registry seems like one of the best alternatives. Anyone have comments on the above ? Obviously using a database like Access or MySQL is one other alternative, but it seems like it would be overkill for maintaining a small set of data. TIA. |
Storing important values: ini file, XML, Registry, where else ?
How about placing them in a hidden sheet in the workbook itself?
"syswizard" wrote: For small amounts of data, there seems to be several ways to store important values and parameters used in an Excel application. I've always thought that using the ini file approach is the best, but there are no built-in functions in Excel to read and write this data. XML seems too verbose and again, there are no XML parsers built into Excel. Storing values in the Windows registry seems like one of the best alternatives. Anyone have comments on the above ? Obviously using a database like Access or MySQL is one other alternative, but it seems like it would be overkill for maintaining a small set of data. TIA. |
Storing important values: ini file, XML, Registry, where else ?
On Mar 8, 8:12 am, Vergel Adriano
wrote: How about placing them in a hidden sheet in the workbook itself? That's OK, but what if they unhide the sheet ? Also, consider the fact that most of the code will be in an XLA file and that multiple XLS files will be created. WIth your approach, I would need to copy the hidden sheet every time a new XLS file is created. Finally, what happens if the first spreadsheet needs to use any changed values which are in the latest spreadsheet. Your approach is good if I need versioning of the values; terrible if I need a central and consistent source of the values. |
Storing important values: ini file, XML, Registry, where else ?
Since W95 the registry is (was?) the preferred location, and there are
simple built in functions in VB to HKCU\Software\VB and VBA Program Settings In help SaveSetting and 'see also' However for quite a while there has been a retro like move back to using ini by many, including MS (though perhaps they've moved on to xml). There are plenty of ini examples in this ng, search GetPrivateProfileString & WritePrivateProfileString. Would need to consider if you need one ini for all (eg your xla path) or separate ini's for each user in which case you would need another function to determine a suitable user path. A matter of what's simplest and effective for your overall needs. Regards, Peter T "syswizard" wrote in message ups.com... For small amounts of data, there seems to be several ways to store important values and parameters used in an Excel application. I've always thought that using the ini file approach is the best, but there are no built-in functions in Excel to read and write this data. XML seems too verbose and again, there are no XML parsers built into Excel. Storing values in the Windows registry seems like one of the best alternatives. Anyone have comments on the above ? Obviously using a database like Access or MySQL is one other alternative, but it seems like it would be overkill for maintaining a small set of data. TIA. |
Storing important values: ini file, XML, Registry, where else
That the XLA file generates spreadsheets and that the spreadsheets need to
have different sets of values was unknown before (or at least I missed it). But anyway, given your description below, all your points are valid. Storing the values in a hidden sheet is a very simple approach that will not work well in a scenario such as yours. Personally, I would prefer a file-based approach in a scenario like this, either XML or INI. By the way, a sheet has 3 "visibility" values: Visible, Hidden, and Very Hidden. A "Very Hidden" sheet is visible only through the VBA IDE. A user wouldn't be able to unhide it. "syswizard" wrote: On Mar 8, 8:12 am, Vergel Adriano wrote: How about placing them in a hidden sheet in the workbook itself? That's OK, but what if they unhide the sheet ? Also, consider the fact that most of the code will be in an XLA file and that multiple XLS files will be created. WIth your approach, I would need to copy the hidden sheet every time a new XLS file is created. Finally, what happens if the first spreadsheet needs to use any changed values which are in the latest spreadsheet. Your approach is good if I need versioning of the values; terrible if I need a central and consistent source of the values. |
All times are GMT +1. The time now is 03:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com