Home » Developer & Programmer » Forms » How to connect Excel with oracle database via form 10g login screen (Application Server 10g release 2, Database 9.0.6, Application server platform -Window server 2003)
How to connect Excel with oracle database via form 10g login screen [message #466729] Tue, 20 July 2010 15:19 Go to next message
itroome
Messages: 34
Registered: June 2005
Location: DUBAI, UAE
Member

Hi Gurus

In fact I have around 50 excelbooks that are connect with oracle via ODBC/or any other way. My task is to build the login screen on form 10g. This form will validate the user via .dll after successfully validation, Form will connect to specific oracle user. This Login Screen will call the new form that will execute the query for the list of excel sheet [table that will keep the name, description and path of the excelbook]. User will select the one excelbook to open. As user will select Excelbook should be open. Now this excel book should be connected with specific oracle user.

Question 1: How to open the excelbook, that excelbook should be connected via form 10g login screen. After connection excel has its own micros to run he query from oracle database.
Question 2: How I can create the mirco into form 10g to run into excelbook/excelsheet.
Question 3: How I can pass the query or function/procedure to the excelbook/excelsheet.

Please do the needful
itroome@yahoo.com
Re: How to connect Excel with oracle database via form 10g login screen [message #466789 is a reply to message #466729] Wed, 21 July 2010 02:59 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
The following are a good list of threads from which to start:
Working with Excel via OLE2
http://www.orafaq.com/forum/t/72811/67467/
http://www.orafaq.com/forum/m/84230/67467/
http://www.orafaq.com/forum/t/101906/67467/
Merge two cells in Excel using ole2
http://www.orafaq.com/forum/t/77825/67467/
Error closing Excel file generated using OLE2
http://www.orafaq.com/forum/t/77351/67467/
How to wrap text in excel cell using ole2
http://forums.oracle.com/forums/thread.jspa;jsessionid=8d92200830d62336f40f5aad44efb36ee981f4e6c19e.e34QbhuKaxmMai0MaNeMb3eKb390?mess ageID=1242535&#1242535
Excel via DDE
http://www.orafaq.com/forum/m/260061/67467/#msg_260061
Import from Excel
http://www.orafaq.com/forum/mv/msg/32353/86299/67467/#msg_86299
Forms to Excel example with webutil
http://forums.oracle.com/forums/thread.jspa?messageID=1430799
Another Forms to Excel example
http://www.orafaq.com/forum/t/81537/67467/
How to kill EXCEL.exe from Task Manager from Oracle Forms
http://www.orafaq.com/forum/t/88643/67467/
how do i display all excel worksheets name in form
http://www.orafaq.com/forum/t/89775/67467/
More Excel stuff
http://www.orafaq.com/forum/t/90502/67467/
To run an excel macro
http://www.orafaq.com/forum/t/47685/67467/
Worked example for using Excel through ODBC
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4406709207206#18830681837358
Blackswan suggested to me that the following link is better.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:243814703172
create excel chart in form6I/How to SetSourceData into excel.chart in form6i
http://www.orafaq.com/forum/t/99738/67467/
Call Excel file from Forms
http://www.orafaq.com/forum/t/121433/67467/
Sample test form.
http://www.orafaq.com/forum/t/139253/67467/

David
[EDITED by DJM: suggestion from Blackswan]

[Updated on: Mon, 20 September 2010 02:13]

Report message to a moderator

Re: How to connect Excel with oracle database via form 10g login screen [message #467189 is a reply to message #466789] Thu, 22 July 2010 09:49 Go to previous messageGo to next message
itroome
Messages: 34
Registered: June 2005
Location: DUBAI, UAE
Member

Dear David

I have written my code but problem is EXCEL is open in the backgroud [ I checked EXCEL service is on and when i open it from OS file open into readonly mode with alert "file alrady open for the another user"], but it is not visible. I want to open the EXCEL for the user working. How I can open it. please guide me.
Procedure View_File (v_FileName in varchar2) is
  v_ViewFile varchar2(100);
  args ole2.list_type;
  application ole2.obj_type;
  workbooks ole2.obj_type;
  workbook ole2.obj_type;
  worksheets ole2.obj_type;
  worksheet ole2.obj_type;
  temp varchar2(200);
Begin
  change_attrib(v_filename,'1');
  application := ole2.create_obj('Excel.Application');
  ole2.set_property(application,'visible',1);
  workbooks   := ole2.get_obj_property(application,'workbooks');
  args        := ole2.create_arglist;
  ole2.add_arg(args,filelocation||v_filename);
  workbook    := ole2.get_obj_property(workbooks,'Open',args);
  ole2.destroy_arglist(args);
  ole2.release_obj(workbooks);
  ole2.release_obj(workbook);
  ole2.release_obj(application);
End View_File;

[EDITED by DJM: formatted the code and applied [code] tags]

[Updated on: Tue, 27 July 2010 03:25] by Moderator

Report message to a moderator

Re: How to connect Excel with oracle database via form 10g login screen [message #467760 is a reply to message #467189] Tue, 27 July 2010 03:28 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Go back to the first two examples and look at the 'invoke' command.

David
Previous Topic: Fields not writing to database table
Next Topic: Report Engine is not running
Goto Forum:
  


Current Time: Fri Sep 20 03:46:04 CDT 2024