ygsunilkumar
05-27-2010, 01:39 AM
Hi,
I am trying to pull the Oracle SQL Query output into the different tabs in Excel.
In the attached excel sheet there are 2 jobs that is PROG, IT and output of the query should be generated in two different sheets with the names of the jobs that is PROG, IT.
Below the VBA Code for reference. Please help how do it?
Thanks in advance.
Sub Load_data()
Sheets("Data Entry Status").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
Dim JOB As Variant
JOB = worksheet("JOB").range("a1").value
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"
'SQL Query
Query = " select employee_id, name, job_id, job from employees where job_id = (select job_id from jobs where job = "&JOB&") order by employee_id
rs.Open Query, cn
With Sheet8
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
Sheet8.Activate
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 With
End Sub
I am trying to pull the Oracle SQL Query output into the different tabs in Excel.
In the attached excel sheet there are 2 jobs that is PROG, IT and output of the query should be generated in two different sheets with the names of the jobs that is PROG, IT.
Below the VBA Code for reference. Please help how do it?
Thanks in advance.
Sub Load_data()
Sheets("Data Entry Status").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
Dim JOB As Variant
JOB = worksheet("JOB").range("a1").value
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"
'SQL Query
Query = " select employee_id, name, job_id, job from employees where job_id = (select job_id from jobs where job = "&JOB&") order by employee_id
rs.Open Query, cn
With Sheet8
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
Sheet8.Activate
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 With
End Sub