How do I find out a text in Microsoft Excel using DDE?

Posted by LegacyUser on 26-May-2003 05:10

I want to find out a text in a Microsoft Excel file. How do I? Help me please

..

Now,I can use DDE technique of progress to open an Excel file.And I try to

use find syntax command like this-> "chFound = chExcelApplication:Range("A1:E10"):FIND("hello").

/chFound and chExcelApplication are com handle/ BUT!! It does not work.:(.

Help me and give me an example please. Thank you very much.

Best Regards,

Ms. Woranan Konthieng.

All Replies

Posted by Admin on 04-Sep-2003 08:39

This might help you out.

excel_read_template.p

/*************************************************************************

Author: David Corral

Purpose: This is a template for reading in an excel spreadsheet. To output

to an excel spreadsheet, use excel_output_template.p.

*************************************************************************/

DEF VAR oExcel_Application AS COM-HANDLE NO-UNDO.

DEF VAR oExcel_Workbook AS COM-HANDLE NO-UNDO.

DEF VAR oExcel_Worksheet AS COM-HANDLE NO-UNDO.

DEF VAR v_range AS CHAR.

DEF VAR i AS INT INITIAL 1.

DEF VAR m AS CHAR INITIAL ".".

DEF VAR v_max_records AS INT INITIAL 1.

DEF VAR v_input_file AS CHAR FORMAT "x(40)" INITIAL "c:\ntn data file august 2002.xls".

/*************************************************************************

Definitions for the excel file. These are the column headings.

Change these for any excel input file.

*************************************************************************/

DEF TEMP-TABLE cdr

FIELD v_cust_name AS CHAR FORMAT "x(40)"

FIELD v_host_name AS CHAR FORMAT "x(40)"

FIELD v_call_date AS DATE

FIELD v_order_num AS DEC FORMAT ">>>>>>>>9"

FIELD v_order_# AS CHAR

FIELD v_par_name AS CHAR

FIELD v_start_time AS CHAR FORMAT "x(45)"

FIELD v_end_time AS CHAR FORMAT "x(25)"

FIELD v_par_item AS CHAR

FIELD v_minutes AS INT

FIELD v_setup_rate AS DEC

FIELD v_per_min_rate AS DEC

FIELD v_par_total AS DEC

FIELD v_fed_tax AS DEC

FIELD v_state_tax AS DEC

FIELD v_county_tax AS DEC

FIELD v_city_tax AS DEC

FIELD v_invoice_num AS INT

FIELD v_order_ref AS CHAR FORMAT "x(25)"

INDEX pri AS PRIMARY

v_cust_name ASCENDING

v_order_num.

UPDATE "Enter input file " v_input_file WITH NO-LABELS.

RUN Open_Object.

/*************************************************************************

If you don't use this technique, then you could be importing blanks.

You can also do a check similar to this in the next section.

*************************************************************************/

DO WHILE asc(substring(m,1,1)) > 32:

ASSIGN

v_max_records = v_max_records + 1

v_range = "A" + string(v_max_records)

m = oExcel_Worksheet:Range(v_range):value().

IF asc(substring(m,1,1)) = ? THEN LEAVE.

END.

MESSAGE "There are " v_max_records " rows to read " VIEW-AS ALERT-BOX.

/*************************************************************************

Set the date and time columns to a numeric

*************************************************************************/

v_range = "F1:F" + string(v_max_records).

oExcel_Worksheet:Range(v_range):Style = "Normal".

/*************************************************************************

Import the data into a temp table for reading later.

*************************************************************************/

REPEAT i = 2 TO v_max_records:

IF i = 500 THEN LEAVE.

DISP i WITH 1 DOWN NO-LABELS.

PAUSE 0.

/*********************************************************************

Get the Excel data

*********************************************************************/

CREATE cdr.

v_range = "A" + string(i).

v_cust_name = oExcel_Worksheet:Range(v_range):value().

v_range = "B" + string(i).

v_host_name = oExcel_Worksheet:Range(v_range):value().

v_range = "C" + STRING(i).

v_call_date = oExcel_Worksheet:Range(v_range):value().

v_range = "D" + STRING(i).

v_order_num = oExcel_Worksheet:Range(v_range):value().

v_range = "E" + string(i).

v_par_name = oExcel_Worksheet:Range(v_range):value().

v_range = "F" + string(i).

v_start_time = oExcel_Worksheet:Range(v_range):value().

v_range = "G" + string(i).

v_end_time = oExcel_Worksheet:Range(v_range):value().

v_range = "H" + string(i).

v_par_item = oExcel_Worksheet:Range(v_range):value().

v_range = "I" + string(i).

v_minutes = oExcel_Worksheet:Range(v_range):value().

v_range = "J" + string(i).

v_setup_rate = oExcel_Worksheet:Range(v_range):value().

v_range = "K" + string(i).

v_per_min_rate = oExcel_Worksheet:Range(v_range):value().

v_range = "L" + string(i).

v_par_total = oExcel_Worksheet:Range(v_range):value().

v_range = "M" + string(i).

v_fed_tax = oExcel_Worksheet:Range(v_range):value().

v_range = "N" + string(i).

v_state_tax = oExcel_Worksheet:Range(v_range):value().

v_range = "O" + string(i).

v_county_tax = oExcel_Worksheet:Range(v_range):value().

v_range = "P" + string(i).

v_city_tax = oExcel_Worksheet:Range(v_range):value().

v_range = "Q" + string(i).

v_invoice_num = oExcel_Worksheet:Range(v_range):value().

v_range = "R" + string(i).

v_order_ref = oExcel_Worksheet:Range(v_range):value().

END.

/*************************************************************************

Set the date and time columns back to Custom

*************************************************************************/

v_range = "F1:F" + string(v_max_records).

oExcel_Worksheet:Range(v_range):NumberFormat = "m/d/yyyy h:mm AM/PM".

RUN Close_Object.

FOR EACH cdr.

DISP cdr WITH 1 COL USE-TEXT.

END.

PROCEDURE Open_Object:

/*********************************************************************

Create the Com-Handle for the application

*********************************************************************/

CREATE "Excel.Application" oExcel_Application.

/*********************************************************************

Create the Com-Handle for the application

*********************************************************************/

oExcel_Workbook = oExcel_Application:workbooks:Open(v_input_file).

/*********************************************************************

Open the sheet for work

*********************************************************************/

oExcel_Worksheet = oExcel_Application:Sheets:Item(1).

END PROCEDURE.

PROCEDURE Close_Object:

/***********************************************************************

To View the spreedsheet at the end, uncomment the following line.

***********************************************************************/

/oExcel_Application:Visible = true./

oExcel_Application:SaveWorkSpace("c:\workspace.xlw").

OS-COMMAND SILENT ("del c:\workspace.xlw").

oExcel_Application:QUIT().

/***********************************************************************

These objects need to always be released. Each one is

created in the procedure Open_Object

***********************************************************************/

RELEASE OBJECT oExcel_Application.

RELEASE OBJECT oExcel_Workbook.

RELEASE OBJECT oExcel_Worksheet.

END PROCEDURE.

This thread is closed