MS Excel handling lazarus / 62-General ---------------------------------------------------------------------- draggon · 2018-02-27 08:11 Hi, is there a possibility to control cell/ worksheet/ workbook protection? I mean MS Excel, not FPSpreadsheet. I work in Windows, using OLE. Thanks ---------------------------------------------------------------------- rvk · 2018-02-27 09:41 I think you should be able to just cal oSheet.Protect('Password', true, true); for the sheet. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet- protect-method-excel ---------------------------------------------------------------------- draggon · 2018-02-27 10:27 thanks, I need control from Pascal, not VBA. ---------------------------------------------------------------------- rvk · 2018-02-27 10:29 Quote from: draggon on February 27, 2018, 11:27:51 amthanks, I need control from Pascal, not VBA. That line is for Pascal in combination with an OLE object (which you requested). Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i oSheet.Protect('Password', true, true); How do you fill a cell of a sheet in Pascal now? If you give that code snippet... you can just replace xxxx.Cell[x,y] with xxxx.Protect('Password', true, true); and it should work. ---------------------------------------------------------------------- draggon · 2018-02-27 10:34 Durind coding I have cells unprotected. When ready I want to unprotect prior handling cell data and protect again then. I suppose the same code is for unprotect, right: oSheet.UnProtect(...) ? ---------------------------------------------------------------------- rvk · 2018-02-27 10:57 Yes, oSheet.Unprotect optionally with a password parameter. The oSheet.Protect and oSheet.Unprotect works on the complete sheet. It does not toggle cell-protection. Normally all cells will be protected when doing oSheet.Protect. Instead of unprotecting the complete sheet, you could try oCell.Locked := false or oRange.Locked := false; ---------------------------------------------------------------------- draggon · 2018-02-27 11:04 I received: Identifier not found "oSheet" ---------------------------------------------------------------------- rvk · 2018-02-27 11:15 oSheet would be your sheet you got via OLE. How do you use your sheet now? Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i var  ooApp: OleVariant;  ooDoc: OleVariant;  oSheet: OleVariant;begin  ooApp := CreateOleObject('Excel.Application');  ooApp.Visible := True;  ooDoc := ooApp.Workbooks.Open('C:\Test\Testing.xls');  oSheet := ooDoc.Worksheets[1];   // etc...end; But this should all be familiar if you know how to use OLE Excel already. ---------------------------------------------------------------------- draggon · 2018-02-27 11:22 I have procedure like this: procedure open_workbook(a, b: ShortString; var c, d: Integer); {Based on input variables assigned to required worksheet and range to be maintained last_row and last_column values are determined fro reading and writing to data table} begin       XLApp:= CreateOleObject('Excel.Application');       XLApp.Visible:= False;       XLApp.DisplayAlerts:= False;       XLApp.Workbooks.Open[path];       {Finds the number of the rows and columns of the range users in Worksheet users.}       c := XLApp.ActiveWorkBook.WorkSheets[a].Range.Rows.Count;       d:= XLApp.ActiveWorkBook.WorkSheets[a].Range.Columns.Count; end; ---------------------------------------------------------------------- rvk · 2018-02-27 11:27 Please, always use the #-button so the code is correctly shown: (see that your post suddenly jumps to bold when you use [b]) Like: QuoteCode: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i begin      XLApp:= CreateOleObject('Excel.Application');      XLApp.Visible:= False;      XLApp.DisplayAlerts:= False;      XLApp.Workbooks.Open[path];       {Finds the number of the rows and columns of the range users in Worksheet users.}      c := XLApp.ActiveWorkBook.WorkSheets[a].Range[b].Rows.Count;      d:= XLApp.ActiveWorkBook.WorkSheets[a].Range[b].Columns.Count; end; Where you have XLApp.ActiveWorkBook.WorkSheets[a] that would be the same as oSheet. (Assigning XLApp.ActiveWorkBook.WorkSheets[a] to oSheet is easier to work with later in the code but it works the same) ---------------------------------------------------------------------- draggon · 2018-02-27 12:24 Correct, I came to the same conclusion and it works. Thanks ---------------------------------------------------------------------- draggon · 2018-02-27 13:49 What is strange is that even if protected from Pascal application, the sheet is editable from MS Excel. ---------------------------------------------------------------------- rvk · 2018-02-27 13:50 Quote from: draggon on February 27, 2018, 02:49:02 pmWhat is strange is that even if protected from Pascal application, the sheet is editable from MS Excel. Did you protect the complete worksheet? Otherwise protecting individual cells don't work. It's documented if you check the protection of an individual cell. ---------------------------------------------------------------------- draggon · 2018-03-09 13:08 I am back again; while when protecting the sheet manually in Excel environment Unprotect works. Unlike Protect. The sheet is not protected against manual manipulation. What mean parameters True ('password', True, true)? I didn't find any link to this command in the net. ---------------------------------------------------------------------- rvk · 2018-03-09 13:24 Quote from: draggon on March 09, 2018, 02:08:40 pmWhat mean parameters True ('password', True, true)? I didn't find any link to this command in the net. It's here: https://msdn.microsoft.com/en-us/vba/excel- vba/articles/worksheet-protect-method-excel The first parameter is password. The third is for content. But because you want to pass the third parameter you also need to pass the second (which is for DrawingObjects). You do need to save the file after doing oSheet.Protect(). If you open the file afterward in Excel, is the content not protected?