Can't import password protected excel file in NAV 2015 using XlWrkBkReader

(0) Share

Report

Posted on by Community Member

I'm getting an error in NAV 2015 when trying to import an password protected NAV 2015 file, does anyone have any ideas on how to counter this?

*This post is locked for comments

All responses (7)

Answers (1)

Community Member on at

Like (0)

Report

RE: Can't import password protected excel file in NAV 2015 using XlWrkBkReader

That's great, thanks for taking the time to give me a thorough response, appreciate it!
Verified answer

HILBE 300 on at

Like (0)

Report

RE: Can't import password protected excel file in NAV 2015 using XlWrkBkReader

Yes, you have to open the file with the standard interop of excel and unprotect the sheet.

ExcelApplicationClass_l := ExcelApplicationClass_l.ApplicationClass;

ExcelWorkbook_l := ExcelApplicationClass_l.Workbooks.Open(FileName_v,0,TRUE,5,'','',TRUE,ExcelXLPlatform_l.xlWindows,'\t',FALSE,FALSE,0,TRUE,1,0);

ExcelWorkbook_l.Unprotect('password');

ExcelWorkbook_l.SaveAs(FileName_v,ExcelXLFileFormat_l.xlOpenXMLWorkbook,'','',FALSE,FALSE,ExcelXLSaveAsAccessMode_l.xlNoChange,ExcelXLSaveConflictResoultion_l.xlUserResolution,FALSE,'','',TRUE);

ExcelWorkbook_l.Close(FALSE,FileName_v,FALSE);

ExcelApplicationClass_l.Quit;

After that you can read the file :)

Parameter:

Var Name DataType Subtype Length

Ja FileName_v Text 1024

local variables:

Name DataType Subtype Length

ExcelApplicationClass_l DotNet Microsoft.Office.Interop.Excel.ApplicationClass.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

ExcelWorkbook_l DotNet Microsoft.Office.Interop.Excel.Workbook.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

ExcelXLFileFormat_l DotNet Microsoft.Office.Interop.Excel.XlFileFormat.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

ExcelXLPlatform_l DotNet Microsoft.Office.Interop.Excel.XlPlatform.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

ExcelXLSaveAsAccessMode_l DotNet Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

ExcelXLSaveConflictResoultion_l DotNet Microsoft.Office.Interop.Excel.XlSaveConflictResolution.'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

Hope it helps
Suggested answer

Alexander Ermakov 28,094 on at

Like (0)

Report

RE: Can't import password protected excel file in NAV 2015 using XlWrkBkReader

If you know the password, you can try to unprotect the file using xlWorkbook.Unprotect : msdn.microsoft.com/.../microsoft.office.tools.excel.workbook.unprotect.aspx

If you do not know the password, you need to remove protection, e.g. by external software which you can run in the background before opening, and use the result of that process.
Community Member on at

Like (0)

Report

RE: Can't import password protected excel file in NAV 2015 using XlWrkBkReader

It's falling over on this bit of code:

XlWrkBkReader := XlWrkBkReader.Open(FileName);
Community Member on at

Like (0)

Report

RE: Can't import password protected excel file in NAV 2015 using XlWrkBkReader

http://imgur.com/VFhvMD3
Community Member on at

Like (0)

Report

RE: Can't import password protected excel file in NAV 2015 using XlWrkBkReader
ManishS 80 on at

Like (0)

Report

RE: Can't import password protected excel file in NAV 2015 using XlWrkBkReader

What is the error message ?