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.
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.