K
kunkletown
I believe this is a bug in Excel 2003. I get an error when saving as
CSV with VBA password protection turned on. If I turn off password
protection, the same code runs without error.
To reproduce the problem...
1. Open a new Excel file.
2. Type "Test" in Sheet1 cell A1
3. using Excel's Control Toolbox toolbar, draw a button on the
worksheet
4. right click on the button and select "view code"
5. enter the following line of code in the button click event (be sure
to enter a path that exists on your system -- I used "c:\temp" which
did exist on my system):
ThisWorkbook.SaveAs "c:\temp\test.csv", xlCSVWindows
6. save your Excel file
7. click the button and observe that the VBA code runs without error
8. close the CSV file that you saved in step 7
9. re-open your Excel file that you saved in step 6
10. open the Visual Basic editor and select Tools\VBAProject Properties
from the menu
11. click the Protection tab, check the "Lock project for viewing"
checkbox, enter a password and click OK (this protects your VBA code).
12. close the Excel file
13. re-open the Excel file
14. click on the button you added in step 3 and notice the error.
"Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed
15. open the Visual Basic editor and attempt to view your code
16. when prompted for your password, enter it
17. click on the button you added in step 3 notice now the code runs
without error.
So it's clear that the same code runs fine when VBA password protection
is turned on or the password has been entered, or produces an error
when VBA password protection is turned off or the password has been
entered.
Any ideas how to correct this issue or work around it?
Thanks!
CSV with VBA password protection turned on. If I turn off password
protection, the same code runs without error.
To reproduce the problem...
1. Open a new Excel file.
2. Type "Test" in Sheet1 cell A1
3. using Excel's Control Toolbox toolbar, draw a button on the
worksheet
4. right click on the button and select "view code"
5. enter the following line of code in the button click event (be sure
to enter a path that exists on your system -- I used "c:\temp" which
did exist on my system):
ThisWorkbook.SaveAs "c:\temp\test.csv", xlCSVWindows
6. save your Excel file
7. click the button and observe that the VBA code runs without error
8. close the CSV file that you saved in step 7
9. re-open your Excel file that you saved in step 6
10. open the Visual Basic editor and select Tools\VBAProject Properties
from the menu
11. click the Protection tab, check the "Lock project for viewing"
checkbox, enter a password and click OK (this protects your VBA code).
12. close the Excel file
13. re-open the Excel file
14. click on the button you added in step 3 and notice the error.
"Run-time error '1004':
Method 'SaveAs' of object '_Workbook' failed
15. open the Visual Basic editor and attempt to view your code
16. when prompted for your password, enter it
17. click on the button you added in step 3 notice now the code runs
without error.
So it's clear that the same code runs fine when VBA password protection
is turned on or the password has been entered, or produces an error
when VBA password protection is turned off or the password has been
entered.
Any ideas how to correct this issue or work around it?
Thanks!