Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protected Excel File manipulation
My situation:
I am generating an Excel file from an Access database using VB (Access 2003). Then I send this file to an outside agency. They are supposed to update some information and send it back. I have some formulae in the file (columns as well rows). I would like to lock and hide the formula. I can't do that if I don't protect the worksheet. My problem is that the agency should be able to add rows also. I tried worksheet.protect method and set allowinsertingrows = true This allows me to insert rows but not copy/paste and does not copy the formula from the previous row. I'll Appreciate any suggestions/help. Thanks in advance, bee. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protected Excel File manipulation
On 14 May, 20:11, Bee wrote:
My situation: I am generating an Excel file from an Access database using VB (Access 2003). Then I send this file to an outside agency. They are supposed to update some information and send it back. I have some formulae in the file (columns as well rows). I would like to lock and hide the formula. I can't do that if I don't protect the worksheet. My problem is that the agency should be able to add rows also. I tried worksheet.protect method and set allowinsertingrows = true This allows me to insert rows but not copy/paste and does not copy the formula from the previous row. I'll Appreciate any suggestions/help. Thanks in advance, bee. Bee, Have you thought about block filling enough formulas to cover If the agency are simply enterring data but need to see the results of the formulas this should work OK. Example, Access produces 15 records, but you populate formulae to 100 rows, allowing the Agency up to 85 rows to enter source data for new records? My only other thought is to turn the workbook into a small application and use macro's userforms etc to allow the agency to update. Anthony |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protected Excel File manipulation
Anthony,
Thank you for this response. I thought about allowing some extra blank rows at the end. One agency we send this to has 150 records at this time but could insert 200 or so rows easily. So far my solution is: 1. Unlock the entire file. 2. Lock the header rows (first 15 or so). 3. Lock & Hide the summary column formula (column 35 or so - varies by month). (I am using row() and column() in the formula in stead of a row/column counter) 4. LOck the summary column. 5. Lock & hide the totals row formula. (Varies) 6. Lock the totals row. 7. Protect the file with a password but have allowinsertingrows = true. This allows the insertion of rows. But the total column value stays blank - The grand total is calculated correctly however. This is not the cleanest probably but I'll live with this. I don't have too much time as I have already spent more time than I was planning. Thank you, Bee. "excel-ant" wrote: On 14 May, 20:11, Bee wrote: My situation: I am generating an Excel file from an Access database using VB (Access 2003). Then I send this file to an outside agency. They are supposed to update some information and send it back. I have some formulae in the file (columns as well rows). I would like to lock and hide the formula. I can't do that if I don't protect the worksheet. My problem is that the agency should be able to add rows also. I tried worksheet.protect method and set allowinsertingrows = true This allows me to insert rows but not copy/paste and does not copy the formula from the previous row. I'll Appreciate any suggestions/help. Thanks in advance, bee. Bee, Have you thought about block filling enough formulas to cover If the agency are simply enterring data but need to see the results of the formulas this should work OK. Example, Access produces 15 records, but you populate formulae to 100 rows, allowing the Agency up to 85 rows to enter source data for new records? My only other thought is to turn the workbook into a small application and use macro's userforms etc to allow the agency to update. Anthony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
file manipulation | Excel Programming | |||
For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation | Excel Programming | |||
Pasword protected Excel file encrypted, how do I read this file? | Excel Discussion (Misc queries) | |||
File Manipulation | Excel Programming | |||
Code for Access file manipulation from Excel | Excel Programming |