?
Solved

Passthrough criteria in excel 2003 from oracle 10g

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

571 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