フィルターのクリア

How to password protect an excel file through Matlab?

12 ビュー (過去 30 日間)
Partha Mitra
Partha Mitra 2019 年 11 月 11 日
コメント済み: Ehtisham 2021 年 6 月 28 日
I am trying to password protect an Excel file which is created using Matlab..
clc;clear;dFlag_Excel = 0;
xlsfile = 'Try_V1.xlsx';
password = 'Test';
Excel = actxserver('Excel.Application'); % open Excel as a COM Automation server
Workbooks = Excel.Workbooks;
Workbook = Workbooks.Open([pwd,'\',xlsfile]);
Workbook.Protect(password, 'True', 'True');
But I am getting an error in this last line above in the script:
??? Invoke Error, Dispatch Exception: The parameter is incorrect.
Error in ==> Untitled at 7
Workbook.Protect('Test@123', 'True', 'True')
  1 件のコメント
Ehtisham
Ehtisham 2021 年 6 月 28 日
Go to the above link and download the simple code. It is easy to use and I have created it myself because I was facing the same problem as you and the code provided by anyone else did not serve my purpose. So I created the code myself.

サインインしてコメントする。

採用された回答

Image Analyst
Image Analyst 2019 年 11 月 22 日
Have you checked out the Excel API for workbooks and worksheets: https://docs.microsoft.com/en-us/office/vba/api/Excel.Worksheet.Protect
From what I can see from the left panel, the worksheet object has a Protect() method but a workbook object does not. However, the text is ambiguous. It says
A string that specifies a case-sensitive password for the worksheet or workbook. If this argument is omitted, you can unprotect the worksheet or workbook without using a password. Otherwise, you must specify the password to unprotect the worksheet or workbook. If you forget the password, you cannot unprotect the worksheet or workbook.
Yet up at the top, and in the left panel it seems to say only worksheets, not workbooks. Perhaps it means that if a workbook has only one worksheet then protecting the worksheet will also protect the workbook, however it doesn't say that explicitly. If I get time, I'll play around with it later.
  8 件のコメント
Partha Mitra
Partha Mitra 2019 年 11 月 23 日
Thanks Image Analyst and thanks Milton!
Roofus Milton
Roofus Milton 2019 年 11 月 23 日
Here are the steps to cycle through protected states.
Local Variables
existingFilePath = "C:\*********.xlsx";
filePath = "C:\*********.xlsx";
openPassword = "OpenPassword";
wbProtectPassword = "Workbook";
wsProtectPassword = "Worksheet";
Create Excel Objects
% attach to open Excel instance
excel = actxGetRunningServer('Excel.Application');
% ensure Excel is visible
excel.Visible = true;
% check to see if we create a new workbook or open existing
if(strcmp(existingFilePath, ""))
% add a new workbook to the workbooks collection
wb = excel.Workbooks.Add();
else
% open existing file, note [] facilitates optional parameters
wb = excel.Workbooks.Open(existingFilePath, [], [], [], openPassword);
end
% set the active worksheet
ws = wb.ActiveSheet();
Workbook
Protect the Workbook
% protect the sheet
wb.Protect(wbProtectPassword, true, true);
% set the password property
wb.Password = openPassword;
Test Workbook Protection
If ProtectStructure returns a logical 1, the code below will produce an error: "Add method of Sheets class failed".
% check if the workbook structure is protected
wb.ProtectStructure
% add a worksheet to the workbook
ws2 = wb.Sheets.Add();
% rename the worksheet
ws2.Name = "TestSheet";
Unprotect the Workbook
% set the password to an empty string removes password requirement
wb.Password = "";
% unprotects the workbook structure
wb.Unprotect(wbProtectPassword);
Worksheet
Protect the Worksheet
% check to see if we created a new workbook, only write data if we create a
% new workbook
if(strcmp(existingFilePath, ""))
% write test data
ws.Range("A1").Value2 = "Roofus";
ws.Range("B1").Value2 = "Milton";
ws.Range("C1").Value2 = "Bear";
end
% set worksheet level protection
ws.Protect(wsProtectPassword, true, true);
Test Worksheet protection
If the ProtectContents property returns a logical 1 then the code will produce an error.
% get the protected status
ws.ProtectContents
% write test data
ws.Range("A2").Value2 = "Test1";
Unprotect the Worksheet
% call the unprotect method
ws.Unprotect(wsProtectPassword);
% write test data
ws.Range("B2").Value2 = "Test2";
Cleanup
% save the file
wb.SaveAs(filePath);
% close the file
wb.Close();

サインインしてコメントする。

その他の回答 (2 件)

Nicolas B.
Nicolas B. 2019 年 11 月 11 日
I recommand you to use the xls_protect_sheets() from MATLAB exchange. It will be easier for you.
  2 件のコメント
Partha Mitra
Partha Mitra 2019 年 11 月 11 日
This works but it only protects the sheet.
What I want is to protect the file completely.
I don't want others to open the file without a password.
Ehtisham
Ehtisham 2021 年 6 月 28 日
Go to the above link and download the simple code. It is easy to use and I have created it myself because I was facing the same problem as you and the code provided by anyone else did not serve my purpose. So I created the code myself.

サインインしてコメントする。


Roofus Milton
Roofus Milton 2019 年 11 月 22 日
You are passing 'True' as a char when it should be logical/boolean.
Workbook.Protect('Test@123', true, true)
  4 件のコメント
Image Analyst
Image Analyst 2019 年 11 月 22 日
This code works for me. It does ask for the password upon reopening the file from disk.
Ehtisham
Ehtisham 2021 年 6 月 28 日
Go to the above link and download the simple code. It is easy to use and I have created it myself because I was facing the same problem as you and the code provided by anyone else did not serve my purpose. So I created the code myself.

サインインしてコメントする。

カテゴリ

Help Center および File ExchangeSpreadsheets についてさらに検索

製品


リリース

R2010b

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by