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
LOVs, buttons and package calls (Read 5247 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
LOVs, buttons and package calls
20.05.07 at 10:54:27
 
LOVs, buttons and package calls
 
Question:
I need to create an Excel sheet in which users may:
 1) display LOVs that are populated dynamically from Oracle tables.
 2) supply a button or macro that invokes an Oracle plsql packaged procedure.
 3) supply a button or macro that invokes an Oracle plsql packaged function and return the result to an Excel cell.
 4) a button or macro that downloads data from Oracle to the sheet.
 5) a button or macro that uploads from the sheet to an Oracle table.
 
Answers:
 1) display LOVs that are populated dynamically from Oracle tables.
    Please read:
     LOV - List Of Values
     http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1179212318
 2) supply a button or macro that invokes an Oracle plsql packaged procedure.
    Please read:
     How to use stored procedures and functions
     http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1129819645
     Your own interface to SQL*XL functionality (read the section about the button)
     http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133092522
 3) supply a button or macro that invokes an Oracle plsql packaged function and return the result to an Excel cell.
    Please read:
     How to use stored procedures and functions
     http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1129819645
 
    The method described in this topic is to run the function from an anonymous PL/SQL block as: begin :A1 := my_package.my_function(1,2,3,4); end;
 
    There also is a worksheet function in SQL*XL called SQLFunction. It takes the name of the function and the function parameters as input. You can use this to bind a cell to the function output.
 
 4) a button or macro that downloads data from Oracle to the sheet.
     Record a macro where you download data from Oracle to the sheet. Then read the following topic how to create the button.
     Your own interface to SQL*XL functionality (read the section about the button)
     http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133092522
 
 5) a button or macro that uploads from the sheet to an Oracle table.
     Please read:
      Macro coding to automate batch insert
      http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1133159596
Back to top
 
« Last Edit: 20.05.07 at 11:03:55 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: LOVs, buttons and package calls
Reply #1 - 21.05.07 at 07:36:44
 
>>
>Using the macro code I found on the forums to connect and query the database I hit a problem.
>Running this gives me the error "Object Expected" and then points
>to the "Database" word in the above Connect() subroutine.
>>
 
If you created the macros by hand the reference to the SQLXL object is missing. When you would have recorded this code using the macro recorder you would have not had this problem. Macro recording needs to be switched on in the program preferences in the general section.
You can also manually add the reference. In the VBA environment of Excel go to Extra and add a reference to sqlxl.xla. That will do the trick.
See: http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130851761
 
>>
>And, will calling the packaged procedure as I am trying to do in the above macro the best way
>to retrieve a result set back to a specific area on my Excel sheet?  Or, is there a better way
>to do this *automatically*, that is, without the user having to use the SQLXL SQL dialogue?
>>
When you run the macro it will not show the SQL dialog.
 
One way I like to do this is by using a worksheet function and by providing a button to refresh the formulas. Please have a look at the SQLQueryValue function in SQL*XL. You find info about it in the forums and you can add the function from the insert function menu. Enter the formula as an array formula and you will be able to view the results of a query as the result of a worksheet formula. The only thing your users need to do is to connect to the database and force the formulas to recalculate. The first can be provided as a macro they can run by clicking a button. The second can be done by recording a macro where you search and replace the = sign. All formulas start with an = sign an therefore all formulas will be refreshed. You can again start the macro from a button.
See:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1162290970
 
Hope this helps
Back to top
 
 

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




Posts: 75
Re: LOVs, buttons and package calls
Reply #2 - 21.05.07 at 07:46:54
 
>>
>I am trying to do this.  I've created a List (I selected range A2:A15) in my worksheet using the List option under the Validation sub-menu of the Data menu.  This created an LOV drop-down button in EACH of the cells A2:A15.  I'm not sure I'm doing this right.  All I want is an LOV in cell A2.
>I then want to populate this LOV with SELECT ENAME, EMPNO FROM EMP.  The user should then be able to use the dropdown.  See a a display of all
>ENAME values.  Select, say, KING, and then have a cell somewhere in the sheet be populated with the EMPNO associated with KING.  If this is not
>possible, then populating the LOV with SELECT ENAME FROM EMP would be ok, too.
>
>In any case, I then use the SQL*XL "SQL ..." option.  Here is where I am doing something wrong.  The dialog lets me type-in SELECT ENAME FROM EMP.
>A dialogue then appears in which I enter the range A1:A15.  I click OK.  This then populate this range with the ENAME values from EMP.  My LOV
>that I had created in this range is overwritten.
>>
Enter your query select ename from emp in the SQL dialog.
Put the results of the query in G1 populating G1 to e.g. G20
Now select cells A2 to A15 where you want the LOV to be used.
Select menu data and validation.
Choose list from the drop down
Enter G1:G20 as source
 
Now you will see that a dropdown symbol appears when you select a cell in the range A2 to A15.
 
Refreshing the LOV is as simpel as rerunning the query. This can be done from the refresh queries button or you can do it using a macro or by selecting the first cell of the results (G1) and going to the SQL dialog.
Back to top
 
 

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