Uploading protected sheets to navision

maxpaine

Good Afternon,

Im new here and i liked to say hello to every body first.

But mey qyestion is, is it possible to open an excel file with protected cells in navision???

best regards

faizguy84

Yes it is possible. It can be done through Automation server Micorsoft Excel Object Library. You will have to create an automation variable. Like
xlwrkbk Automation server Micorsoft Excel Object Library.Workbook

by using XLwrkbk-->"method"-->"protect" u can protect the workbook.




______________________
Regards,
Faisal Bukhari.

maxpaine

It not proteting i want for now, but thanks ;
The problem is reading from a protect book. by a normal means e says error.
Can any body help.

Cumprimentos
Carlos Sequeira

ddanica

Yes, in standard Excel Buffer, the Read function cannot read protected excel sheets. To solve this problem (and to create protected Excel files also), I have created a new function in Excel Buffer table:

PROCEDURE ProtectSheet@1103051003(vPassword@1103051000 : Text[30];ToDo@1103051004 : 'Protect,Unprotect');
    VAR
      DrawingObjects@1103051001 : Boolean;
      Contents@1103051002 : Boolean;
      Scenarios@1103051003 : Boolean;
    BEGIN

      IF ToDo = ToDo::Protect THEN BEGIN
        DrawingObjects := TRUE;
        Contents := TRUE;
        Scenarios := TRUE;

        XlWrkSht.Protect(vPassword,DrawingObjects,Contents,Scenarios);
      END ELSE
        XlWrkSht.Unprotect(vPassword);
    END;

To use proper this function to PROTECT files, you must create and call before a function to lock/unlock some cells from the futures Excel file. If you don't use a function to unlock some cells, all cells will be locked.
If you need just to read a protected file, is enough to call this function with correct password and option "Unprotect".

cssgyula

Hi All,

I have also created for this a custom function in Excel Buffer table, but I have come to an Excel file, which has two different Workbook Protection Passwords, one for opening the Workbook for to view it and one for protecting sheets from being able to Hide/Unhide them for reading.

Previously, there were only one password used to protect the function of Hide/Unhide Sheets in Excel, but now one particular Excel file has two different passwords, but the xlWorkbook.Unprotect has no parameters of which password I want to enter. I call xlApp.Workbooks._Open by-the-book, but when I run onto the two-password protected Excel-file, the password prompt shows up after Excel starts up, but because the Excel application starts up, my importing function shows up an OLE error, because it cannot read the Excel of course, because it is already opened by Excel application itself and Workbook.Unprotect accepts only the second password to Unhide the sheet for reading.

I tried to look up xlApp.Worksheets to see any underlying functions to unprotect an Excel before opening, but I couldn't find neither in Navision C/AL Symbol Menu, nor on MSDN, only for using Workbook Method Unprotect, but it cannot be used to Unprotect an Excel for reading, before opening:

https://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.unprotect.aspx

Any suggestions, ideas, documentations, anything?

Thank you in advance for your help and support!

Jules

cssgyula

Any new suggestions anyone?

Please?

Jules

c.kestas

I know this is a very old post, but here's a solution anyway:

The "Open" method has more parameters than just the filename. The fifth is the password.
Example:
XlApp.Workbooks.Open('c:\MyProtectedFile.xls',0,FALSE,'5','ThePassword');

More details in the link below so you can adjust the rest of the parameters according to your case:

https://www.codeproject.com/articles/5123/opening-and-navigating-excel-with-c