Pivot Table VBA

[ramadasu]

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&quot
cnnConnection.Open "dsn=DsnName1;uid=Uid1;pwd=pwd1"
Set PivotTable1 = CreateObject("OWC.PivotTable&quot
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]&quot
view.RowAxis.InsertFieldSet fsets("[ROWFIELD2]&quot

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

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

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&quot
End Sub


If you have any quiries please let me know.

Rama Dasu Puli

 

[SkipVought]

Rama Dasu Puli,

Sounds to me like you are PUSHING the data out FROM Access TO Excel -- am I correct?

It is a much easier approch to PULL the data FROM Access TO Excel on, for instance, the Workbook_Open Event. All it takes is a query via Data/Get External Data... to be refreshed on the open event.

Does that get the job done?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

It will do but there is no way of getting the data there WITHOUT opening the workbook. You could set up a schedule to open it overnight and get the data, then close down again but that's about it

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244