Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 March 9th, 2012, 02:39 PM
Registered User
 
Join Date: Mar 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default problem in pivot refresh

hello
please help me

i have to commands creat and refresh

creat piovt table no problem

refresh piovt table give me error no 1004

this is code


Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim ptCache As PivotCache
Dim ptTable As PivotTable
Dim xlCalc As XlCalculation
Dim stCon As String
Dim stSQL As String

Private Sub creat_Click()
Dim rnStart As Range
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets.Add

With wsSheet
Set rnStart = .Range("A1")
End With

wsSheet.Name = "ADO PT"

stCon = "Provider=SQLOLEDB.1;Password=1000;Persist Security Info=True;User ID=sa;Initial Catalog=main;Data Source=P1"
stSQL = "SELECT ITEMNMBR from iv00101 where ITEMNMBR like '170%' ITEMNMBR like '178%' "

ADO_Call stCon, stSQL

Set ptCache = wbBook.PivotCaches.Add(SourceType:=xlExternal)

'Add the Recordset as the source to the pivotcache.
With ptCache
Set .Recordset = rst
End With
x = ptCache.RecordCount
'Create the pivottable
Set ptTable = ptCache.CreatePivotTable(TableDestination:=rnStart , _
TableName:="PT_ADO")
End Sub



Private Sub refresh_Click()
Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets("ADO PT")
Set ptCache = wbBook.PivotCaches(1)


stCon = "Provider=SQLOLEDB.1;Password=1000;Persist Security Info=True;User ID=sa;Initial Catalog=main;Data Source=P1"
stSQL = "SELECT ITEMNMBR from iv00101 where ITEMNMBR like '174%' ITEMNMBR like '178%' "


ADO_Call stCon, stSQL

'Add the Recordset as the source.
Set ptCache.Recordset = rst

With wsSheet
Set ptTable = .PivotTables("PT_ADO")
End With

'Refresh the data.
ptTable.RefreshTable

'Release the Recordset from the memory.
If CBool(rst.State And adStateOpen) Then rst.Close
Set rst = Nothing

End Sub



Private Function ADO_Call(stCon As String, stSQL As String) As ADODB.Recordset

Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset

'Temporarily change some settings.
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

'Open the connection and fill the Recordset.
With cnt
.CursorLocation = adUseClient
.Open stCon
Set rst = .Execute(stSQL)
End With

'Disconnect the Recordset.
Set rst.ActiveConnection = Nothing

If CBool(cnt.State And adStateOpen) Then cnt.Close
Set cnt = Nothing

'Restore the settings.
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With
End Function
 
Old March 9th, 2012, 11:16 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Can you specify where the error occurs (the line of code)

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old March 10th, 2012, 08:20 AM
Registered User
 
Join Date: Mar 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thanks Shasur

error occurs (ptTable.RefreshTable)

in Private Sub refresh_Click
 
Old March 10th, 2012, 09:59 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

This might occur if the DataSource is disconnected or not available in the location. Can you try refreshing the same manually from the sheet and check if it works properly

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter Pivot Table(s) based on Cell Value outside Pivot steplawn Beginning VB 6 0 May 11th, 2010 09:01 PM
Refresh problem hsucp2p PHP How-To 6 January 4th, 2008 07:02 PM
Still the "Refresh problem" ... fskilnik Dreamweaver (all versions) 10 October 20th, 2005 11:25 AM
Pivot Table dataaxis Alignment Problem -Urgent vinod_yadav1919 Javascript How-To 0 April 11th, 2005 04:52 AM
Refresh Problem gkirk Beginning PHP 2 February 25th, 2005 04:14 PM





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