Recent

Author Topic: MS Excel handling  (Read 5430 times)

draggon

  • New Member
  • *
  • Posts: 41
MS Excel handling
« on: February 27, 2018, 09:11:22 am »
Hi, is there a possibility to control cell/ worksheet/ workbook protection? I mean MS Excel, not FPSpreadsheet. I work in Windows, using OLE.

Thanks
« Last Edit: February 27, 2018, 09:51:23 am by draggon »

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: MS Excel handling
« Reply #1 on: February 27, 2018, 10:41:46 am »
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

  • New Member
  • *
  • Posts: 41
Re: MS Excel handling
« Reply #2 on: February 27, 2018, 11:27:51 am »
thanks, I need control from Pascal, not VBA.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: MS Excel handling
« Reply #3 on: February 27, 2018, 11:29:46 am »
thanks, I need control from Pascal, not VBA.
That line is for Pascal in combination with an OLE object (which you requested).
Code: Pascal  [Select][+][-]
  1. 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

  • New Member
  • *
  • Posts: 41
Re: MS Excel handling
« Reply #4 on: February 27, 2018, 11:34:22 am »
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

  • Hero Member
  • *****
  • Posts: 6111
Re: MS Excel handling
« Reply #5 on: February 27, 2018, 11:57:37 am »
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

  • New Member
  • *
  • Posts: 41
Re: MS Excel handling
« Reply #6 on: February 27, 2018, 12:04:33 pm »
I received: Identifier not found "oSheet"

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: MS Excel handling
« Reply #7 on: February 27, 2018, 12:15:17 pm »
oSheet would be your sheet you got via OLE.

How do you use your sheet now?

Code: Pascal  [Select][+][-]
  1. var
  2.   ooApp: OleVariant;
  3.   ooDoc: OleVariant;
  4.   oSheet: OleVariant;
  5. begin
  6.   ooApp := CreateOleObject('Excel.Application');
  7.   ooApp.Visible := True;
  8.   ooDoc := ooApp.Workbooks.Open('C:\Test\Testing.xls');
  9.   oSheet := ooDoc.Worksheets[1];
  10.   // etc...
  11. end;

But this should all be familiar if you know how to use OLE Excel already.

draggon

  • New Member
  • *
  • Posts: 41
Re: MS Excel handling
« Reply #8 on: February 27, 2018, 12:22:03 pm »
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

  • Hero Member
  • *****
  • Posts: 6111
Re: MS Excel handling
« Reply #9 on: February 27, 2018, 12:27:07 pm »
Please, always use the #-button so the code is correctly shown:
(see that your post suddenly jumps to bold when you use [b])

Like:
Quote
Code: Pascal  [Select][+][-]
  1. begin
  2.       XLApp:= CreateOleObject('Excel.Application');
  3.       XLApp.Visible:= False;
  4.       XLApp.DisplayAlerts:= False;
  5.       XLApp.Workbooks.Open[path];
  6.  
  7.       {Finds the number of the rows and columns of the range users in Worksheet users.}
  8.       c := XLApp.ActiveWorkBook.WorkSheets[a].Range[b].Rows.Count;
  9.       d:= XLApp.ActiveWorkBook.WorkSheets[a].Range[b].Columns.Count;
  10.  
  11. 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

  • New Member
  • *
  • Posts: 41
Re: MS Excel handling
« Reply #10 on: February 27, 2018, 01:24:37 pm »
Correct, I came to the same conclusion and it works. Thanks

draggon

  • New Member
  • *
  • Posts: 41
Re: MS Excel handling
« Reply #11 on: February 27, 2018, 02:49:02 pm »
What is strange is that even if protected from Pascal application, the sheet is editable from MS Excel.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: MS Excel handling
« Reply #12 on: February 27, 2018, 02:50:52 pm »
What 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

  • New Member
  • *
  • Posts: 41
Re: MS Excel handling
« Reply #13 on: March 09, 2018, 02:08:40 pm »
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

  • Hero Member
  • *****
  • Posts: 6111
Re: MS Excel handling
« Reply #14 on: March 09, 2018, 02:24:16 pm »
What 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?

 

TinyPortal © 2005-2018