Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Pivot Table VBA

Status
Not open for further replies.

ramadasu

IS-IT--Management
Feb 14, 2003
1
SG
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top