VBA Code: Populating data in different sheets

[ygsunilkumar]

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