hi sir,
am useing below this code to collect data in exel sheet then i am creating
pivot table using that data.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Integer
Dim row As Integer
Dim str1 As String
end1 = Cells(1, 26)

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

cn.Open ( _
"User ID=sujatas" & _
";Password=ss" & _
";Data Source=mdlp" & _
";Provider=MSDAORA.1")

rs.Open "SELECT to_char(INSdate,'MON-YY') MONTH,to_char(INSdate,'YYYY')" & _
"CYEAR,insdate,RATE_TYPE,PRIPUB,REGION1,PRIPROCTG1 ,SUM(AMOUNT)
AMT,SUM(TOTCC) CC FROM AD_CLIENT_MAP where INSDate between '01-FEB-09' AND
'28-FEB-09' GROUP BY INSdate,RATE_TYPE,PRIPUB,REGION1,PRIPROCTG1 UNION SELECT
to_char(INSdate,'MON-YY') MONTH,to_char(INSdate,'YYYY')
CYEAR,insdate,RATE_TYPE,PRIPUB,REGION1,PRIPROCTG1, SUM(AMOUNT) AMT,SUM(TOTCC)
CC FROM AD_CLIENT_MAP where INSDate between '01-FEB-08' AND '" & end1 & "'
GROUP BY INSdate,RATE_TYPE,PRIPUB,REGION1,PRIPROCTG1", 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

sir i want directly that data or record set goes to pivot table

thanks and regards
amaya