OpenROAD & Excel

[Young, Matthew (CRD)]

Hi,

I'm writing a frame that allows the user to open an Excel file and read
the information from the file into OpenROAD. I have used an external
class (Microsoft Excel 12.0 Object Library) and have had success using
code similar to:

ON CLICK =
DECLARE
iStatus = integer NOT NULL DEFAULT ER_FAIL;
vMessage = varchar(100) NOT NULL;
strFile = stringobject;

ExcelAppExl = ExcelApp_Exl!Application;
ExcelWorkBook = ExcelApp_Exl!_Workbook;
ExcelWorkSheet = ExcelApp_Exl!_Worksheet;
ENDDECLARE
{
strFile.Value = 'C\*.xls';
iStatus = CurFrame.FilePopup(MessageText = 'Select the Excel
file...',
reply = strFile);
IF iStatus = PU_CANCEL THEN RESUME; ENDIF;

ExcelWorkBook = ExcelAppExl.workbooks.Open(strFile.Value);

ExcelWorkSheet = ExcelWorkBook.ActiveSheet;

vMessage = ExcelWorkSheet.cells(1, 2).value;

message vMessage;
}

This displays the value of cell B in row 1 of the excel file. However, I
want the user to be able to specify the worksheet in the file to read
data from. I have tried adding code such as:

vMessage = ExcelWorkBook.WorkSheets(1).cells(1, 2).value;

AND

ExcelWorkBook.ActiveSheet = ExcelWorkBook.WorkSheets(1);

AND

ExcelWorkBook.WorkSheets(1).Activate()
ExcelWorkSheet = ExcelWorkBook.ActiveSheet;

All to no avail :(

I'm sure I'm missing something obvious but I just can't get it to work.

Can anyone tell me how to set the ActiveSheet or read from the nth
worksheet?

Thanks in advance,
Matt

************************************************************
The Chemicals Regulation Directorate (CRD) was created on the 1st April 2009.
All our email addresses will follow the following format
firstnam...@hse.gsi.gov.uk.
Email sent to the previous PSD addresses will continue to be delivered but please update your mailing lists or contacts as soon as you can to reflect the change.

This email may contain sensitive information. Please ensure that you refer to our Statement of Service Standards for guidance on secure handling of information

***********************************************************************************
Please note: Incoming and outgoing e-mail messages are routinely monitored for compliance with our policy on the use of electronic communications and may also be automatically logged, monitored and/or recorded for lawful purposes by the GSi service provider.

Interested in Occupational health and safety information?
Please visit the HSE website at the following address to
keep yourself up to date.

www.hse.gov.uk

or Contact HSE Infoline on 0845 345 0055 or Email on hse.in...@natbrit.com

***********************************************************************************

The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.

[Bodo Bergmann]

Have you tried:

ExcelWorkSheet = ExcelWorkBook.Sheets.Item(1);
ExcelWorkSheet.Activate();

Bodo.

[Young, Matthew (CRD)]

Awesome! I knew someone clever would know. Worked like a charm :)

Cheers,
Matt

This email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In
case of problems, please call your organisation's IT Helpdesk.

Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.

************************************************************
The Chemicals Regulation Directorate (CRD) was created on the 1st April 2009.
All our email addresses will follow the following format
firstnam...@hse.gsi.gov.uk.
Email sent to the previous PSD addresses will continue to be delivered but please update your mailing lists or contacts as soon as you can to reflect the change.

This email may contain sensitive information. Please ensure that you refer to our Statement of Service Standards for guidance on secure handling of information