ABL - Excel Import

[Radley1303]

Progress 9.1E Appserver

Hi All,

I have an automated program that takes MS Excel XLS files as input, reads the data from the XLS and populates some db tables. The problem I am having is that some of the XLS files are password protected (they are supplied via an external source) - in this instance I just want to flag up to a log file that they can't be read.

<code snippet>

CREATE "Excel.Application" chExcelApp.
chExcelApp:VISIBLE = NO.
chWorkbook = chExcelApp:Workbooks:OPEN(pi_cImportFile) NO-ERROR.
IF ERROR-STATUS:ERROR THEN
[send error to log file etc]
ELSE chWorkSheet = chExcelApp:Sheets:ITEM(1).

</code snippet>

The NO-ERROR isn't working as Excel pops up prompting for a password. If I don't enter a valid password I get 'Error Occurred while accessing component property/method : Sheets' etc. Is there anyway before I do the OPEN that I can check if the XLS is protected ? Something like :

IF chExcelApp:WorkbooksROTECTED(pi_cImportFile) THEN... ???

As always, thanks in advance.

 

[Scleameth]

You can disable excel alerts before you open the workbook:
chExcelAppisplayAlerts = FALSE

 

[Radley1303]

Thanks for the info Scleameth - have just put that code before the OPEN as suggested but Excel still pops up with an 'Data.xls is Protected' / Password box - grrrr !

 

[Scleameth]

nasty !
and if you try this :
chWorkbook = chExcelApp:Workbooks:OPEN(pi_cImportFile,,FALSE) NO-ERROR.

??
If not that, then - I suppose I don't have the answer, sorry

Edit: I just checked some code, but even if I include all security that Excel allows me, I don't get an error in the line:
chWorkSheet = chExcelApp:Sheets:ITEM(1).
I only get errors when trying to write to it. But what about :
chWorkSheet = chExcelApp:Sheets:ITEM(1) NO-ERROR.
and then handling the error after that statement ?

 

[jdgibson]

You could try passing in an empty string password which is the 5th parameter to the open method.

Instead of checking error status try checking if the workbook handle is a valid handle which it won't be if the workbook hasn't opened

 

[Radley1303]

My sincere thanks go to jdgibson & Scleameth for their help - passing in a blank password string (5th param of OPEN method) and then checking if the handle to the workbook is valid worked a treat. Cheers again you guys !

One happy programmer...

 

[erickroco9611]

HI... could you post your Sample of how can you import from excel with automation! Thanks!!!