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
Dynamic SQL statements based on cell contents (Read 6601 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Dynamic SQL statements based on cell contents
20.11.06 at 11:06:54
 
Dynamic SQL statements based on cell contents
 
SQL*XL user Brian wrote:
 
What I really would like is the combination of a dynamic SQL statement based on the content of excel cells and the ability to right click refresh data as per standard Excel. I got as far as recognising that your product requires a macro to do this.  
 
Answer:
You can do this in two ways:

  • Using SQL source = worksheet:
    You can use Excel formulas to build the SQL statement on the worksheet. Example:
    =concatenate("select * from ", A1, " where empno = ", A2)
     
    Then open the SQL dialog to run the query by selecting as SQL source to take the selected cell(s) in the worksheet. To rerun the query simply follow the same pattern.
     
  • Using the SQLQueryValue worksheet function:
    You can also do this by using the SQLQueryValue worksheet function. Example:
    =SQLQueryValue(concatenate("select * from ", A1, " where empno = ", A2))
     
    This will show the first column of the first row. You can show more of the results by selecting the cell with this formula so the formula shows again in the Excel formula toolbar. Then select all the cells you want to display the results in. E.g. when the cell containing the value is A1 you could select A1Cheesy10 showing 4 columns and 10 rows.
    After selecting the cells put the cursor at the end of the formula in Excel's formula toolbar and press Ctrl-Shift-Enter.
Back to top
 
 

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