Home » Developer & Programmer » Forms » need help
need help [message #120312] Wed, 18 May 2005 23:56 Go to next message
toufiq_raja
Messages: 39
Registered: May 2005
Location: Islamabad
Member


i used the following code but there are 2 errors in it, can any one help me to remove the errors

1. when button pressed
populate_date.load_emp_records;
(identifier must be declared)

2.

Procedure Worksheet_PurchaseRqst2 IS
C number := 1;
r number := 3;
BEGIN
OPEN_WORKSHEET('PurchaseRqst2');
FOR I IN 1..POPULATE_DATA.EMP_RECORDS.LAST LOOP
c := 1;
WRITE_TO_WORKSHEET(r,c,populate_data.emp_records(i).empno);
c := 3;
WRITE_TO_WORKSHEET(r,c,populate_data.emp_records(i).ename);
c := 4;
WRITE_TO_WORKSHEET(r,c,populate_data.emp_records(i).hiredate);
c := 7;
WRITE_TO_WORKSHEET(r,c,populate_data.emp_records(i).dname);
c := 1;
r := r + 1;
END LOOP;
END;

error identifier populate_date must be declared

create a textitem called file_name (save as file name)
create a button excel_button. under the when button-pressed trigger paste the follwing code.
Let me know if you still get any problems.

if :excel.file_name is not null then
-- To check the save as file exists.
IF olewrap.IS_FILE_EXISTS (:excel.file_name) THEN
MESSAGE('Save as file Already Exists....');
Raise Form_Trigger_Failure;
ELSE
populate_data.load_emp_records;
olewrap.Start_Writing_Into_Excel;
END IF;
else
Message('Please enter Save As file name ');Message('');
Raise form_trigger_failure;
end if;



PACKAGE olewrap IS
template_file varchar2(200) := 'c:\temp\your_excel_file.XLT';
PROCEDURE Start_Writing_Into_Excel;
FUNCTION IS_FILE_EXISTS (P_FILE_NAME VARCHAR2) RETURN BOOLEAN;
PROCEDURE RENAME_SHEET;
PROCEDURE DELETE_SHEET (P_SHEET_NAME VARCHAR2);

END;


PACKAGE BODY olewrap IS
-- Declare the OLE objects
application OLE2.OBJ_TYPE;
workbooks OLE2.OBJ_TYPE;
workbook OLE2.OBJ_TYPE;
worksheets OLE2.OBJ_TYPE;
worksheet OLE2.OBJ_TYPE;
cell OLE2.OBJ_TYPE;
args OLE2.LIST_TYPE;

PROCEDURE Start_Excel IS
BEGIN
-- Start Excel and make it visible
application := OLE2.CREATE_OBJ('Excel.Application');
OLE2.SET_PROPERTY(application,'Visible', 'True');

workbooks := OLE2.GET_OBJ_PROPERTY(application, 'Workbooks');

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, template_file);
workbook := OLE2.GET_OBJ_PROPERTY(workbooks, 'Open', args);
OLE2.DESTROY_ARGLIST(args);

worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets');
--worksheet := OLE2.GET_OBJ_PROPERTY(worksheets, 'Add');

END;

FUNCTION IS_FILE_EXISTS (P_FILE_NAME VARCHAR2) RETURN BOOLEAN IS
check_file text_io.file_type;
no_file exception;
PRAGMA EXCEPTION_INIT (no_file, -302000);
BEGIN
-- Check the file can be found, if not exception no_file will be raised
check_file := TEXT_IO.FOPEN(p_file_name,'R');
TEXT_IO.FCLOSE(check_file);
RETURN TRUE;
EXCEPTION
WHEN no_file THEN
RETURN FALSE;
END;

PROCEDURE OPEN_WORKSHEET (P_Worksheet VARCHAR2) IS
BEGIN
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args,P_WORKSHEET);
worksheet := OLE2.GET_OBJ_PROPERTY(worksheets,'Item',args);
ole2.invoke(worksheet,'Select');
OLE2.DESTROY_ARGLIST(args);
END;

PROCEDURE WRITE_TO_WORKSHEET (P_ROW NUMBER,P_COLUMN NUMBER,P_VALUE VARCHAR2) IS
BEGIN
-- Return object handle to cell A1 on the new Worksheet
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, P_ROW);
OLE2.ADD_ARG(args, P_COLUMN);
cell := OLE2.GET_OBJ_PROPERTY(worksheet, 'Cells', args);
OLE2.DESTROY_ARGLIST(args);

-- Set the contents of the cell to 'Hello Excel!'
OLE2.SET_PROPERTY(cell, 'Value', P_VALUE);
END;

PROCEDURE Stop_Excel IS
BEGIN
-- Release the OLE objects
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args, :excel.file_name);
OLE2.INVOKE(worksheet, 'SaveAs', args);
OLE2.DESTROY_ARGLIST(args);
-- OLE2.INVOKE(application, 'Quit');
OLE2.RELEASE_OBJ(cell);
OLE2.RELEASE_OBJ(worksheet);
OLE2.RELEASE_OBJ(worksheets);
OLE2.RELEASE_OBJ(workbook);
OLE2.RELEASE_OBJ(workbooks);
OLE2.RELEASE_OBJ(application);
END;


PROCEDURE RUN_MACRO (P_MACRO VARCHAR2) IS
BEGIN
-- Run macro MyMacro
args:=ole2.create_arglist;
ole2.add_arg(args, P_MACRO);
ole2.invoke(application, 'Run', args);
ole2.destroy_arglist(args);
END;

Procedure Worksheet_TEO_RteSheet2 IS
BEGIN
OPEN_WORKSHEET('TEO RteSheet2');

WRITE_TO_WORKSHEET(11,5,'456778');
WRITE_TO_WORKSHEET(15,6,'Software Engineer');
WRITE_TO_WORKSHEET(30,2,'4');
WRITE_TO_WORKSHEET(32,2,'6');
WRITE_TO_WORKSHEET(34,2,'8');
WRITE_TO_WORKSHEET(36,2,'10');
WRITE_TO_WORKSHEET(42,7,'Writing data from the forms to excel in a specific worksheet of the cell');

-- To rename a worksheet.
ole2.set_property(Worksheet,'Name','Konda');

END;

Procedure Worksheet_PurchaseRqst2 IS
C number := 1;
r number := 3;
BEGIN
OPEN_WORKSHEET('PurchaseRqst2');
FOR I IN 1..POPULATE_DATA.EMP_RECORDS.LAST LOOP
c := 1;
WRITE_TO_WORKSHEET(r,c,populate_data.emp_records(i).empno);
c := 3;
WRITE_TO_WORKSHEET(r,c,populate_data.emp_records(i).ename);
c := 4;
WRITE_TO_WORKSHEET(r,c,populate_data.emp_records(i).hiredate);
c := 7;
WRITE_TO_WORKSHEET(r,c,populate_data.emp_records(i).dname);
c := 1;
r := r + 1;
END LOOP;
END;


Procedure Worksheet_PUC_Report IS
BEGIN
OPEN_WORKSHEET('PUC Report');

WRITE_TO_WORKSHEET(14,3,'11111111');
WRITE_TO_WORKSHEET(29,2,'Writing data from the forms to excel in a specific worksheet of the cell');
WRITE_TO_WORKSHEET(7,11,TO_CHAR(TRUNC(SYSDATE),'DD-MON-YYYY'));
WRITE_TO_WORKSHEET(18,3,'99999999');
END;


PROCEDURE Worksheet_TEO_Face_Sheet IS
BEGIN
OPEN_WORKSHEET('TEO Face Sheet');
RUN_MACRO('CheckBox1_Click');
--RUN_MACRO('CheckBox16_Click');
--RUN_MACRO('CheckBox17_Click');
--RUN_MACRO('CheckBox18_Click');
--RUN_MACRO('CheckBox19_Click');
--RUN_MACRO('CheckBox20_Click');
END;


PROCEDURE Start_Writing_Into_Excel IS
BEGIN
-- To check the template file exists.
IF olewrap.IS_FILE_EXISTS (olewrap.template_file) THEN

--To Start Excel and make it visible.
Start_Excel;
-- Open the worksheet TEO RteSheet2 and Write.
Worksheet_TEO_RteSheet2;
--Worksheet_TEO_Face_Sheet;
--INDEX
--GEN JOB SUMMARY
--Gen Notes
--MATERIAL LIST
--PurchaseRqst2
--Quote Rqst
--Com Sys Reqmts Rqst
--MAP - Misc Trans Rqst
--MAP Trans Rqst2
--Install Agreement
--Completion Accept
--EIU 5E1
--EIU 5E2
--EIU DMS
--Cntrl Office Equip Rqst
--Open the worksheet PUC Report and Write.
Worksheet_PurchaseRqst2;
Worksheet_PUC_Report;
DELETE_SHEET('Konda');
-- Release the Opened OLE objects.
Stop_Excel;
ELSE
MESSAGE('Excel Template File not found.');
Raise Form_Trigger_Failure;
END IF;
END;



PROCEDURE RENAME_SHEET IS
BEGIN
--*** Open and rename the sheet
args:=ole2.create_arglist;
ole2.add_arg(args,'ABC');
worksheet:=ole2.get_obj_property(workbook,'Worksheets',args);
ole2.destroy_arglist(args);
ole2.set_property(Worksheet,'Name','XYZ');
END;

PROCEDURE DELETE_SHEET (P_SHEET_NAME VARCHAR2) IS
BEGIN
args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(args,P_SHEET_NAME);
OLE2.INVOKE(worksheet,'Delete',args);
OLE2.DESTROY_ARGLIST(args);
END;
end;


help me to use the code
regards
Re: need help [message #290089 is a reply to message #120312] Thu, 27 December 2007 06:28 Go to previous message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
is there any package called populate_data present in the form?
is POPULATE_DATA.EMP_RECORDS a PL/SQL table?

the package olewrap does not seem to have any problem.
Previous Topic: FRM-47313: Invalid query for hierarchial tree.
Next Topic: How to administor the forms with role to user
Goto Forum:
  


Current Time: Thu Dec 08 14:18:13 CST 2016

Total time taken to generate the page: 0.18583 seconds