Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Building a user-friendly Excel Oracle front-end (Read 9082 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Building a user-friendly Excel Oracle front-end
18.01.09 at 11:18:31
 
Building a user-friendly Excel Oracle front-end
 
Quote:
I have some familiarity with Oracle and SQL, but only little familiarity with Excel and none with VB/VBA. I have downloaded your software to trial with a view to building a user-friendly Excel front-end to a bulk update/insert in Oracle.

What I am looking for is the ability to return a result set into a combo box, allow a selection from that list to be used as input parameter in the next SQL to populate another combo box, select data from that result set to be used in validation (via more SQL to populate work tables) of a separate list of items entered on the form by the user. Finally, display the results of the validation in a scrollable list and allow the user to invoke a PL/SQL script to perform the bulk process (using the work tables).

Is this the kind of thing that SQL*XL can be used for? The application will be built from scratch - there is no existing Excel spreadsheet.
I would appreciate your advice.

 
You can show data in dropdown lists. Go to View-Toolbars and enable the Forms toolbar. You can draw a dropdown list onto your spreadsheet. Right click the control and select "Format control". You can specify a range of cells where the input should be taken. E.g. set that to X2:X100. You now need to put the reference values there. You can run a query to populate the range: select CategoryId from categories to X1; You can automate this using the sqlqueryvalue function (use the Array version to be able to display more than one row). You can also automate it by running a macro running the query.
 
A combobox can be programmed to assigning a macro that fires when a value in the combo changes. You can execute your detail query from there.
 
Further reading:
Excel DB front-end with listboxes and comboboxes  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1232962909
Back to top
 
« Last Edit: 26.01.09 at 11:03:53 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Building a user-friendly Excel Oracle front-en
Reply #1 - 19.01.09 at 09:46:25
 
I have created an example workbook where I have solved the problem both with queries and using cell formulas. Personally I find cell formulas a neater solution. Note that you will need to first establish a connection. You can connect automatically on startup if required.
Back to top
« Last Edit: 19.01.09 at 09:47:42 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Building a user-friendly Excel Oracle front-en
Reply #2 - 24.01.09 at 09:53:52
 
In the past SQL*XL could return data into comboboxes for example. I tested this again after your comments and found out it did not work correctly anymore. I have fixed the problem and I will post soon some instructuctions how to use it.  
 
In short you can create controls on the worksheet with the Control Toolbox toolbar. Create for example a combobox and name it combo1. Then you can run the query select mycol from mytable to combo1. SQL*XL will clear the combobox and fill it with the data in column mycol.
 
Further reading:
Excel DB front-end with listboxes and comboboxes  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1232962909
Back to top
 
« Last Edit: 26.01.09 at 11:04:06 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1