Solved

Passthrough criteria in excel 2003 from oracle 10g

Posted on 2010-08-25
3
397 Views
Last Modified: 2012-05-10
Guys
I have an excel VB script that connects to oracle 10g then returns values from a query. at present I hardcode the sql query in the script but would like to make it open so that users can enter a value on the spreadsheet and the sql query will use that instead.

I am aware that you can do this using the import database wizard and create parameters but would like to use a method similar to what I have now.

Any ideas

Regards
Sub Balance_Analysis()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = Sheets("BalanceAnalysis").Cells(1, 1)

'Dim source As String
Dim user As String
Dim password As String
Dim Company As String
Dim rg As Range
    Source = Login.TextBox1.Value
    user = Login.TextBox2.Value
    password = Login.TextBox3.Value
    Company = Login.TextBox4.Value
    
    'Toggle the worksheets
    Application.DisplayAlerts = True
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Sheets("BalanceAnalysis").Visible = True
    Sheets("BalanceAnalysis").Select
    Cells.Select
    Selection.ClearContents
    
    ' open the database
Set cn = New ADODB.Connection
    cn.Open "Provider=OraOLEDB.Oracle;" & _
           "Data Source=" & Source & ";" & _
           "User Id=" & user & ";" & _
           "Password=" & password & ""
Set rs = New ADODB.Recordset
    With rs
       
    sSql = "select distinct company, accounting_year, account, account_desc, " & _
    "sum(amount_balance) over ( PARTITION BY account order by account range unbounded preceding) as cumulative " & _
    "from accounting_balance_auth " & _
    "where company = 'M02' " & _
    "and accounting_year = '2010' " & _
    "and accounting_period <= '2' " & _
    "order by 3"
    
    rs.Open sSql, cn, adOpenStatic, adLockReadOnly, adCmdText
        
For intColIndex = 0 To rs.Fields.Count - 1 ' the field names
            TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
  Next
    TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data

  End With
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
     
    'Toggle the worksheets
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("p&l").Select
    Range("A3").Select
    'Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = False
    Application.ScreenUpdating = True
  
End Sub

Open in new window

0
Comment
Question by:DarrenJackson
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
calacuccia earned 500 total points
ID: 33520438
This will add a Worksheet variable (Declare it as Worksheet on top), with a name "Reader" where in cells A1 to A3 your data are written down by the user and then uses the values of the cells in the string.

    'Declare ReadingSheet
    Set ReadS = Worksheets("Reader")
    sSql = "select distinct company, accounting_year, account, account_desc, " & _
    "sum(amount_balance) over ( PARTITION BY account order by account range unbounded preceding) as cumulative " & _
    "from accounting_balance_auth " & _
    "where company = '" & ReadS.Range("A1") & "' " & _
    "and accounting_year = '" & ReadS.Range("A2") & "' " & _
    "and accounting_period <= '" & ReadS.Range("A3") & "' " & _
    "order by 3"
0
 

Author Comment

by:DarrenJackson
ID: 33520480
calacuccia

Amazing very quick response thankyou

Works perfect
0
 

Author Closing Comment

by:DarrenJackson
ID: 33520483
Excellent  Thankyou
0

Featured Post

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question