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
Binding variables to a cell (Read 5585 times)
aDba
YaBB Newbies
*


I Love SQL*XL

Posts: 2
Binding variables to a cell
01.05.06 at 16:35:23
 
I need some help.... I'm evaluating this software (love it) but am stuck on binding a variable to a spreadsheet in VBA.  I'm a DBA type so this may be obvious to others   :-[
 
I can get this to work when I manully run it, but I'm missing something in the VB code.
 
Thanks for your help!
Greg.
 
 
:dThriveSpec!B3 = 20060301
 
 
    With SQLXL.Sql
       .Clear
       .appendText "SELECT   sas.unit_id   ,hs.unit_nm     ,hs.fa_id   ,hs.center_id   ,hs.center_name  ,cc.cost_ctr_id  ,id.item_val_txt as ssn   ,hs.empl_fte_pct_qty AS fte     ,   fpt.target_point    * hs.empl_fte_pct_qty   / 100 AS target_points      ,SUM (sas.total_value) AS total_points      ,ROUND (  (  SUM (sas.total_value)          / (  fpt.target_point           * hs.empl_fte_pct_qty           / 100))         * 100, 2) AS target_pct     ,comm_user.thfn$save_pct_target ((ROUND (  (  SUM (sas.total_value)         / (  fpt.target_point           * hs.empl_fte_pct_qty           / 100))         * 100, 2)       )       ,5          ,hs.empl_fte_pct_qty        ,20060301       ) AS payout     FROM commission.ths$acct_serv sas       ,commission.thd$hier_snap hs        ,commission.thd$fa_point_target fpt     ,pay_prfmc.cost_ctr cc      ,hierarchy.item_detl id  WHERE 1 = 1    AND sas.unit_id = "
       .appendText "hs.unit_id     AND hs.fa_id = fpt.fa_id  AND sas.unit_id = id.unit_id  AND id.item_id = 1  AND hs.center_id = cc.ctr_id(+)  AND hs.fa_id = cc.fnctl_area_id(+)     AND hs.snap_date = TO_DATE (:dThriveSpec!B3, 'YYYYMMDD')    AND fpt.target_start_dt <= TO_DATE (20060301, 'YYYYMMDD')    AND fpt.target_end_dt > TO_DATE (20060301, 'YYYYMMDD')  GROUP BY sas.unit_id     ,hs.unit_nm     ,hs.empl_fte_pct_qty    ,hs.fa_id   ,fpt.target_point   ,hs.center_id   ,hs.center_name  ,cc.cost_ctr_id  ,id.item_val_txt  ORDER BY target_pct DESC"
       .Parse
    End With
    Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
    SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
    With Targets(litExcel)
       .AutoFilter = True
       .AutoFit = True
       .Headings = True
       .Sort = False
       .StartFromCell = "dThriveSpec!$D$1"
       .Transpose = False
       .SQLInNote = False
       .FormatData = True
       .FreezePanes = True
       .PasteInsert = False
    End With
    With SQLXL.Sql.Statements(1)
       .ShowParametersDlg = False
       .ShowResultsetDlg = False
    End With
    SQLXL.Sql.Statements(1).Execute
 
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Binding variables to a cell
Reply #1 - 05.05.06 at 10:45:34
 
Hi,
 
I have recently also noticed that parameters were not successfully recorded by the macro recorder. This was fixed in SQL*XL 4.0.43. Can you download this upgrade and try it again please?  
 
Another note is that the macro recorder always records .ShowParametersDlg = False assuming you have correctly set the values for the parameters. If you set it to True it will display the parameters dialog so you can set and verify the parameters. Perhaps handy for debugging...
 
Back to top
 
 

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


I Love SQL*XL

Posts: 2
Re: Binding variables to a cell
Reply #2 - 22.05.06 at 19:03:15
 
Thanks for your assistance.  Great software, just purchased a license =)
 
I saw your feedback on the version, and originally loaded 4.0.43 but it didn't capture the bind variables in the macro.  I found an example in the documentation so didn't hold me up.  
 
Thanks again.
Greg.
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Binding variables to a cell
Reply #3 - 23.05.06 at 08:45:49
 
Hi Greg,
 
Thank you for purchasing the license. The latest release is now version 4.0.45 and that will contain the macro recording support for parameters.
 
Best regards, Gerrit-Jan Linker
Linker IT Software BV.
Back to top
 
 

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