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
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]"
view.DataAxis.InsertTotal newtotal
view.DataAxis.InsertFieldSet view.FieldSets("[DATAFIELD1]"
Set newtotal = view.AddTotal("Sum of DATAFIELD2", view.FieldSets("[DATAFIELD2]"
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