Wrox Programmer Forums
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 23rd, 2003, 03:16 AM
Registered User
 
Join Date: Oct 2003
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Pivot Table

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 Pulu,
Singapore






Similar Threads
Thread Thread Starter Forum Replies Last Post
DefaultVersion in Pivot Table Amit Mohanty Excel VBA 1 August 1st, 2006 07:40 PM
Editing in Pivot Table brrmsc VBScript 0 November 29th, 2005 01:10 AM
Pivot Table vbsolo Excel VBA 3 November 23rd, 2005 01:28 AM
pivot table without total thelos Excel VBA 1 September 23rd, 2005 11:40 AM
Pivot Table mikeparams SQL Server 2000 1 February 9th, 2005 10:10 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.