Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    For Next loop (2000)

    I'm trying to export different pages of a pivot pivot table to html. I have set the Html filename from a cell range and I use the publish objects method to send to html. At the moment, I manually select the page on the pivot table and then click on a macro to post that page. However, I want to publish all the pages in the pivot table and so I'm trying to achieve this by adding a For Next loop into the code. Not to good with the VBA but something like this :-

    Sub Export()
    Dim Name
    Dim ChangePivotItem
    Filename = Range("c2")
    Changeover = ActiveSheet.PivotTables("PivotTable1").PivotFields ("Name")
    For i = 1 To Changeover
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
    "Cocuments and SettingsView1Desktop" & Filename & ".htm", "pivot", "$G$2:$j$8", _
    xlHtmlStatic, "holdfile071105_7339", Filename)
    .Publish (True)
    .AutoRepublish = True
    Next Changeover
    End Sub

    Could anyone offer some help on the coding.

    Many thanks

    Robert

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Next loop (2000)

    <post:=539,727>post 539,727</post:> shows how you can loop through the values of the page field in a pivot table.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Next loop (2000)

    Thanks Hans
    This works perfectly

    Robert

  4. #4
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Next loop (2000)

    Actually this only worked once for me and I'm afraid I can't understand why.

    I get Run time error 1004:-
    "Unable to set default property of the PivotItem class"

    The fault occurs at "pf.CurrentPage = pi.Name"

    Sub Export()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

    Set ws = ActiveSheet
    Set pt = ws.PivotTables(1)
    Set pf = pt.PageFields(1)
    For Each pi In pf.PivotItems
    pf.CurrentPage = pi.Name
    Filename = Range("c2")
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
    "Cocuments and SettingsView1Desktop" & Filename & ".htm", "pivot", "$G$2:$I$8", _
    xlHtmlStatic, "holdfile071105_7339", Filename)
    .Publish (False)
    .AutoRepublish = True
    End With
    Next pi
    Set pi = Nothing
    Set pf = Nothing
    Set pt = Nothing
    Set ws = Nothing
    End Sub

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Next loop (2000)

    Sorry, I can't explain that. You might attach a stripped down copy of your workbook. Remove or modify sensitive data.

  6. #6
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Next loop (2000)

    Hello Hans,

    I set up a stripped down version for you, but that works OK, so no sense in posting.

    However, in my original spreadsheet, my current Pivot page is made from a field in my data that is titled "surnames". When I deleted a "surname" from my data, the Pivot table seems to refresh OK, and everything seemed fine.

    However, the code falls down at

    "pf.CurrentPage = pi.Name"

    Running the mouse over the code shows that pi.name is set to the now deleted name. Can't understand where this picks up from now that the underlying data and the pivot table have both been refreshed.

    Is this because the Pivotcache needs to be refreshed in some way?

    Regards

    Robert

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Next loop (2000)

    You might try executing

    ActiveWorkbook.PivotCaches(1).Refresh

    either manually (type/paste it in the Immediate window and press Enter) or in a macro.

  8. #8
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Next loop

    Many thanks for all your help but unfortunately, still having the problems. I attach an example spreadsheet as an example. It seems that when the underlying data changes problems arise in the code.


    Regards
    Robert

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Next loop

    You've got the line that sets the page field the wrong way round. It should be

    pf.CurrentPage = pi.Name

    instead of

    pi.Name = pf.CurrentPage

    Instead of refreshing the pivot table, you can use the following code from Excel -- Pivot Tables -- Clear Items:

    Sub DeleteOldItemsWB()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem

    On Error Resume Next
    For Each ws In ActiveWorkbook.Worksheets
    For Each pt In ws.PivotTables
    pt.RefreshTable
    pt.ManualUpdate = True
    For Each pf In pt.VisibleFields
    If pf.Name <> "Data" Then
    For Each pi In pf.PivotItems
    If pi.RecordCount = 0 And _
    Not pi.IsCalculated Then
    pi.Delete
    End If
    Next pi
    End If
    Next pf
    pt.ManualUpdate = False
    pt.RefreshTable
    Next pt
    Next ws
    End Sub

    Call it only once, at the beginning of your procedure, not inside the loop.

  10. #10
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For Next loop (2000)

    In Excel 2002 these two lines from http://www.contextures.com/xlPivot04.htm) seem to do the trick

    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh

    I will also try out the excel 97-2000 code (also on this website) to make my spreadsheet backward compatible.

    Thanks for all your help
    Robert

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: For Next loop (2000)

    Your post has 2000 in the subject line, so I posted code for Excel 2000. If you had mentioned 2002 I would have posted code for that version.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •