Solved

Passthrough criteria in excel 2003 from oracle 10g

Posted on 2010-08-25
3
410 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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