rael_lucid
Board Regular
- Joined
- Aug 7, 2003
- Messages
- 58
Hi,
To make a really long story a little shorter, this is my situation:
I have an excel (2003) workbook with approximately 20 sheets. Each is fairly heavily formatted and is used as a data entry form. Each sheet is protected so the user can only enter values in appropriate cells.
Users require the ability to be able to SAVE the data into a "data only" workbook, and LOAD preexisting data from a "data only" workbook.
I originally used the worksheet.copy method, but due to the following MS Excel bug - http://support.microsoft.com/kb/210684 - I am unable to rely on it.
Because there are approximately 10000 unprotected cells in the 20 sheets, looping through them one at a time simply takes too long (especially when it comes to LOADing an existing backend - approx 60 seconds).
So, I have had to find another method of doing this.
I have set up named ranges for all the unprotected cells which I use for clearing the sheets (when required), and my thinking is to perhaps somehow use these named ranges to speed up the process. However, because the ranges are made up of data in non-adjacent cells, I am unable to use Copy and Paste methods.
At this point, the only way I can see to do this is to basically do the following:
To Save Data:
Create a new workbook with the same sheetname structure
For each sheet, unprotect, then select and copy all cells.
Paste contents into appropriate sheet on new workbook.
Reprotect sheet in "frontend"
To Load Data:
Open presaved workbook
Unprotect and unhide all sheets in "frontend"
For each sheet in presaved data workbook, select and copy all cells
Paste contents into appropriate sheet in frontend.
Reprotect and hide sheets in frontend.
I believe that this will work, but I have some concerns:
1) it seems like a messy way, would prefer something a little more professional
2) when loading data, is this going to affect my named ranges in the frontend?
Any suggestions welcomed.
Rael
To make a really long story a little shorter, this is my situation:
I have an excel (2003) workbook with approximately 20 sheets. Each is fairly heavily formatted and is used as a data entry form. Each sheet is protected so the user can only enter values in appropriate cells.
Users require the ability to be able to SAVE the data into a "data only" workbook, and LOAD preexisting data from a "data only" workbook.
I originally used the worksheet.copy method, but due to the following MS Excel bug - http://support.microsoft.com/kb/210684 - I am unable to rely on it.
Because there are approximately 10000 unprotected cells in the 20 sheets, looping through them one at a time simply takes too long (especially when it comes to LOADing an existing backend - approx 60 seconds).
So, I have had to find another method of doing this.
I have set up named ranges for all the unprotected cells which I use for clearing the sheets (when required), and my thinking is to perhaps somehow use these named ranges to speed up the process. However, because the ranges are made up of data in non-adjacent cells, I am unable to use Copy and Paste methods.
At this point, the only way I can see to do this is to basically do the following:
To Save Data:
Create a new workbook with the same sheetname structure
For each sheet, unprotect, then select and copy all cells.
Paste contents into appropriate sheet on new workbook.
Reprotect sheet in "frontend"
To Load Data:
Open presaved workbook
Unprotect and unhide all sheets in "frontend"
For each sheet in presaved data workbook, select and copy all cells
Paste contents into appropriate sheet in frontend.
Reprotect and hide sheets in frontend.
I believe that this will work, but I have some concerns:
1) it seems like a messy way, would prefer something a little more professional
2) when loading data, is this going to affect my named ranges in the frontend?
Any suggestions welcomed.
Rael
