PDA

View Full Version : Populating Oracle SQL Queries in different Sheets in Excel using VBA code



ygsunilkumar
03-31-2010, 05:15 AM
Hi, I am trying to populate the 3 different Oracle SQL Queries into 3 Sheets that is "Sheet1, Sheet2, Sheet3". Please help me how do I achieve. Below the code I have tried but it runs only 1 SQL Query. Thanks in Advance.



Private Sub Load_data()
Sheets("Sheet1").Select
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Integer
Dim row As Integer
Dim Query As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

Sheet1.Activate
Query = "select * from employees where employee_id = 100"

Sheet2.Activate

Query = "select * from employees where employee_id = 200"

Sheet3.Activate
Query = "select * from employees where employee_id = 300"

cn.Open ("User ID= " & UserForm.txtusrname & ";Password=" & UserForm.txtPassword & ";Data Source=" & UserForm.cboInstance & ";Provider=MSDAORA.1")

rs.Open Query, cn

col = 0
'First Row: names of columns
Do While col < rs.Fields.Count
Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

'Now actual data as fetched from select statement
row = 1
Do While Not rs.EOF
row = row + 1
col = 0

Do While col < rs.Fields.Count
Cells(row, col + 1) = rs.Fields(col).Value
col = col + 1
Loop

rs.MoveNext
Loop

End Sub

Bob Phillips
03-31-2010, 05:52 AM
Untested



Private Sub Load_data()
Sheets("Sheet1").Select
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Integer
Dim row As Integer
Dim Query As String
Dim mtxData As Variant

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open "User ID= " & UserForm.txtusrname & ";" & _
"Password=" & UserForm.txtPassword & ";" & _
"Data Source=" & UserForm.cboInstance & ";" & _
"Provider=MSDAORA.1"

Query = "select * from employees where employee_id = 100"
rs.Open Query, cn
With Sheet1

'First Row: names of columns
Do While col < rs.Fields.Count
.Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

mtxData = rs.getrows
.Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData
End With

Query = "select * from employees where employee_id = 200"
rs.Open Query, cn
With Sheet2

'First Row: names of columns
Do While col < rs.Fields.Count
.Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

mtxData = rs.getrows
.Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData
End With

Query = "select * from employees where employee_id = 300"
rs.Open Query, cn
With Sheet3

'First Row: names of columns
Do While col < rs.Fields.Count
.Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

mtxData = rs.getrows
.Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData
End With
End Sub

ygsunilkumar
03-31-2010, 06:02 AM
Hi, Thanks for the code but I got the below error when I run the code. Please help

Run-time error '3705':

Operation is not allowed when the object is open

Bob Phillips
03-31-2010, 06:35 AM
Where?

ygsunilkumar
03-31-2010, 06:41 AM
here

Query = "select * from employees where employee_id = 200"
rs.Open Query, cn

Run-time error '3705':

Operation is not allowed when the object is open

Jan Karel Pieterse
03-31-2010, 06:49 AM
Just before each "end with" you need a

.Close

statement.

RichardSchollar
03-31-2010, 06:49 AM
Close rs first before you try to open it again:


Query = "select * from employees where employee_id = 200"

rs.Close

rs.Open Query, cn

Richard

Bob Phillips
03-31-2010, 06:54 AM
Maybe this



Private Sub Load_data()
Sheets("Sheet1").Select
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.Open "User ID= " & UserForm.txtusrname & ";" & _
"Password=" & UserForm.txtPassword & ";" & _
"Data Source=" & UserForm.cboInstance & ";" & _
"Provider=MSDAORA.1"

RunQuery cn, Sheet1, 100
RunQuery cn, Sheet2, 200
RunQuery cn, Sheet3, 300

Set cn = Nothing
End Sub

Private Function RunQuery(cn As ADODB.Connection, ws As Worksheet, limit)
Dim rs As ADODB.Recordset
Dim Query As String
Dim mtxData As Variant
Dim col As Integer
Dim row As Integer

Set rs = New ADODB.Recordset

Query = "select * from employees where employee_id = " & limit
rs.Open Query, cn
With ws

'First Row: names of columns
Do While col < rs.Fields.Count
.Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

mtxData = rs.getrows
.Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData
End With

Set rs = Nothing
End Function

ygsunilkumar
04-01-2010, 12:20 AM
Hi, the below query is populating all the 3 SQL Queries in the different sheets but data which is populating is column wise(vertically but not horizontally) but I need to populate data as per the original table data. Please help.




Private Sub Load_data()
Sheets("Sheet1").Select
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Integer
Dim row As Integer
Dim Query As String
Dim mtxData As Variant

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open "User ID= " & UserForm.txtusrname & ";" & _
"Password=" & UserForm.txtPassword & ";" & _
"Data Source=" & UserForm.cboInstance & ";" & _
"Provider=MSDAORA.1"

Query = ""select * from employees where employee_id = 100'"
rs.Open Query, cn
With Sheet1
col = 0
'First Row: names of columns
Do While col < rs.Fields.Count
.Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop


mtxData = rs.GetRows
.Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData




End With
rs.Close
Query = ""select * from employees where employee_id = 200'"
rs.Open Query, cn
With Sheet2
col = 0
'First Row: names of columns
Do While col < rs.Fields.Count
.Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

mtxData = rs.GetRows
.Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData


End With
rs.Close
Query = ""select * from employees where employee_id = 300'"
rs.Open Query, cn
With Sheet3
col = 0
'First Row: names of columns
Do While col < rs.Fields.Count
.Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

mtxData = rs.GetRows
.Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData


End With
End Sub



I think only this code has to be changed.



mtxData = rs.GetRows
.Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData

Bob Phillips
04-01-2010, 12:52 AM
Change



mtxData = rs.GetRows


to



mtxData = Application.Transpose(rs.GetRows)