How to password protect an excel file through Matlab?

Question

Partha Mitra 2019-11-11

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/490358-how-to-password-protect-an-excel-file-through-matlab

评论： 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 个评论
显示 -1更早的评论隐藏 -1更早的评论

Ehtisham 2021-6-28

https://www.mathworks.com/matlabcentral/fileexchange/94983-xlsprotect-password-protect-excel-files-through-matlab

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.

请先登录，再进行评论。

请先登录，再回答此问题。

Answer 1

Image Analyst 2019-11-22

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/490358-how-to-password-protect-an-excel-file-through-matlab#answer_402827

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 个评论
显示 6更早的评论隐藏 6更早的评论

Partha Mitra 2019-11-23

编辑：Image Analyst 2019-11-23

在 MATLAB Online 中打开

Hi Image Analyst,

When I try using the code I am only able to protect the workbook (Worksheet names).

Because when i open the excel file, it opens and only when I try to change the worksheet names then I can't do as the workbook is protected.

What I said earlier: I want the excel file protected i.e. when i or anyone tries to open then it asks first for the password. On correct password then it will open the file and show the contents.

I will give a brief flow as of now and then I will give the snapshot what is required:

A. Current work:

Step 0: The excel file without any password:

Step 1: I run the following code in matlab:

clc;clear;dFlag_Excel = 0;
xlsfile = 'ExcelFile_V4.xlsx';
Excel = actxserver('Excel.Application'); % open Excel as a COM Automation server
Workbooks = Excel.Workbooks;
try
Workbook = Workbooks.Open([pwd,'\',xlsfile]);
catch
    Workbooks.Close; % close the workbook
    Excel.Quit;      % quit Excel
    % or invoke(Excel,'Quit');
    delete(Excel);
    dFlag_Excel = 1;
end
try
Workbook.Protect('Test@123', true, true)
% Excel.ActiveWorkbook.Protect('Test@123', true, true);
catch
    Workbooks.Close; % close the workbook
    Excel.Quit;      % quit Excel
    delete(Excel);
    dFlag_Excel = 1;
end
if (dFlag_Excel == 0)
    Workbook.Save;
    Workbooks.Close; % close the workbook
    Excel.Quit;      % quit Excel
    delete(Excel);
end

Step 2: I see the excel file as modified for protection.

2.a. I open the excel file: I can open the file but you can see in below snapshot that the name of the worksheet is protected by password. I also will show the snapshot:

Step 3: Also I can see and modify the contents. Please refer below screenshot:

B. But this is not what I want.

What I want is as showed in my earlier comment as well as now: (I am doing it manually in excel)

New excel file: ExcelFile_V5.xlsx

Step i. Manually I encrypt the excel file using a password.

Step ii. After I set the password as per Step i, I try to open the excel file and I see as below:

This is exactly I want my code to do, but unfortunately it is as per Step 2. and Step 3.

So, please help me to achieve the above as per Step ii

Image Analyst 2019-11-23

在 MATLAB Online 中打开

Try this:

dFlag_Excel = 0;
xlsfile = 'ExcelFile_V4.xlsx';
Excel = actxserver('Excel.Application'); % open Excel as a COM Automation server
Workbooks = Excel.Workbooks;
try
	Workbook = Workbooks.Open([pwd,'\',xlsfile]);
catch
	Workbooks.Close; % close the workbook
	Excel.Quit;      % quit Excel
	% or invoke(Excel,'Quit');
	delete(Excel);
	dFlag_Excel = 1;
end
try
	% Define the password for the workbook and the worksheet, BOTH.
	workbookPassword = 'Test@123wb';
	worksheetPassword = 'Test@123'; % Could be different from the workbook password if you want.
	% Set the password property for the Workbook.
	Workbook.Password = workbookPassword;
	% Now protect the sheet inside the workbook, if there are multiple worksheets.
	% Otherwise if there is only one sheet, the workbook password will open the worksheet also.
	Workbook.Protect(worksheetPassword, true, true)
	% Excel.ActiveWorkbook.Protect('Test@123', true, true);
catch
	Workbooks.Close; % close the workbook
	Excel.Quit;      % quit Excel
	delete(Excel);
	dFlag_Excel = 1;
end
if (dFlag_Excel == 0)
	Workbook.Save;
	Workbooks.Close; % close the workbook
	Excel.Quit;      % quit Excel
	delete(Excel);
end

Partha Mitra 2019-11-23

Thanks Image Analyst and thanks Milton!

Roofus Milton 2019-11-23

在 MATLAB Online 中打开

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();

请先登录，再进行评论。

Answer 2

Nicolas B. 2019-11-11

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/490358-how-to-password-protect-an-excel-file-through-matlab#answer_400850

I recommand you to use the xls_protect_sheets() from MATLAB exchange. It will be easier for you.

2 个评论
显示无隐藏无

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 2021-6-28

https://www.mathworks.com/matlabcentral/fileexchange/94983-xlsprotect-password-protect-excel-files-through-matlab

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.

请先登录，再进行评论。

Answer 3

Roofus Milton 2019-11-22

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/490358-how-to-password-protect-an-excel-file-through-matlab#answer_402715

在 MATLAB Online 中打开

You are passing 'True' as a char when it should be logical/boolean.

Workbook.Protect('Test@123', true, true)

4 个评论
显示 2更早的评论隐藏 2更早的评论

Image Analyst 2019-11-22

This code works for me. It does ask for the password upon reopening the file from disk.

Ehtisham 2021-6-28

https://www.mathworks.com/matlabcentral/fileexchange/94983-xlsprotect-password-protect-excel-files-through-matlab

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.

请先登录，再进行评论。

How to password protect an excel file through Matlab?

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

采纳的回答

8 个评论
显示 6更早的评论隐藏 6更早的评论

更多回答（2 个）

2 个评论
显示无隐藏无

4 个评论
显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

How to password protect an excel file through Matlab?

1 个评论 显示 -1更早的评论隐藏 -1更早的评论

采纳的回答

8 个评论 显示 6更早的评论隐藏 6更早的评论

更多回答（2 个）

2 个评论 显示 无隐藏 无

4 个评论 显示 2更早的评论隐藏 2更早的评论

另请参阅

类别

标签

产品

版本

Community Treasure Hunt

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

8 个评论
显示 6更早的评论隐藏 6更早的评论

2 个评论
显示无隐藏无

4 个评论
显示 2更早的评论隐藏 2更早的评论