Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old March 9th, 2012, 01:39 PM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #2 (permalink)  
Old March 9th, 2012, 10:16 PM
Friend of Wrox
Points: 2,950, Level: 22
Points: 2,950, Level: 22 Points: 2,950, Level: 22 Points: 2,950, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 788
Thanks: 1
Thanked 51 Times in 47 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)
Reply With Quote
  #3 (permalink)  
Old March 10th, 2012, 07:20 AM
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
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
Reply With Quote
  #4 (permalink)  
Old March 10th, 2012, 08:59 AM
Friend of Wrox
Points: 2,950, Level: 22
Points: 2,950, Level: 22 Points: 2,950, Level: 22 Points: 2,950, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 788
Thanks: 1
Thanked 51 Times in 47 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)
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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 06: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 03:14 PM



All times are GMT -4. The time now is 11:16 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.