Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Passthrough criteria in excel 2003 from oracle 10g

Posted on 2010-08-25
3
Medium Priority
?
427 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 2000 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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

886 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