Pivot table vba

I am using following function to create pivote table from sql source and export it to excel. Excel file is creating sucessfull. But data is loading in excel file when I open the created file. But my requirement is it should be loaded automatically and saved with data Before opening the file. Because I need to automate the process Every day data will change. Same file If I open next day then next day data will appear. I hope u understand my requirement.

Data should be loaded in exel file withought opening file. I am attaching the function for pivot table for your verification. I hope I will get the solution as soon as possible.

Sub PivotTest()
Dim strProvider
Dim view
Dim fsets
Dim c
Dim newtotal
Dim PivotTable1
Dim rs

strProvider = "Microsoft.Jet.OLEDB.4.0"
Set cnnConnection = CreateObject("ADODB.Connection")
cnnConnection.Open "dsn=DsnName1;uid=Uid1;pwd=pwd1"
Set PivotTable1 = CreateObject("OWC.PivotTable")
PivotTable1.ConnectionString = cnnConnection.ConnectionString

Dim tmpExcel, pivotTbl

pivotTbl = "d:\rama\PivotTest3.xls"

PivotTable1.CommandText = "SELECT top 100 b.DATAFIELD1, c.DATAFIELD2, a.ROWFIELD2, a.ROWFIELD1 " & _
"FROM Table1 a, Table2 b, Table3 c " & _
"WHERE c.FolderName = a.ROWFIELD2 AND b.Folders = a.ROWFIELD2 "

' Get variables from the pivot table
Set view = PivotTable1.ActiveView
Set fsets = PivotTable1.ActiveView.FieldSets
Set c = PivotTable1.Constants

' Add Category to the Row axis and Item to the Column axis
view.RowAxis.InsertFieldSet fsets("[ROWFIELD1]")
view.RowAxis.InsertFieldSet fsets("[ROWFIELD2]")

' Add a new total - Sum of Price
Set newtotal = view.AddTotal("Sum of DATAFIELD1", view.FieldSets("[DATAFIELD1]").Fields(0), c.plFunctionSum)
view.DataAxis.InsertTotal newtotal
view.DataAxis.InsertFieldSet view.FieldSets("[DATAFIELD1]")

Set newtotal = view.AddTotal("Sum of DATAFIELD2", view.FieldSets("[DATAFIELD2]").Fields(0), c.plFunctionSum)
view.DataAxis.InsertTotal newtotal
view.DataAxis.InsertFieldSet view.FieldSets("[DATAFIELD2]")

PivotTable1.Refresh

' Set some visual properties
PivotTable1.DisplayExpandIndicator = True
PivotTable1.DisplayFieldList = True
PivotTable1.Refresh

tmpExcel = "d:\rama\PivotTest.xls"
PivotTable1.Export tmpExcel, vbNoOpen

Set cnnConnection = Nothing
MsgBox ("Excel file created successfully")
End Sub


If you have any quiries please let me know.

Rama Dasu Puli,

If u will use excel object,I provider u a link to learn it:http://www.microsoft.com/officedev/...es/OffObjPr.htm