dcsimg
Results 1 to 8 of 8

Thread: Daily PivotTable

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2019
    Posts
    4

    Daily PivotTable

    Hi all.
    I need to create a daily PivotTable extracting data from a website. I have been able to extract the data, but I am stuck with the PivotTable. This is what the code looks like:

    Sub Update()

    Dim CurrentDate As String

    'Create daily database
    CurrentDate = Format(Date, "mmm-dd-yy")
    Sheets.Add.Name = CurrentDate


    'Data Extraction
    With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;file:///C:/Pipeline Reports/Dan%20Pending%20Renewal.htm", _
    Destination:=Range("$A$1"))
    .Name = "COM%20UW%20PENDING%20NEW"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
    End With


    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Selection.Font.Bold = True

    'Create daily PivotTable
    Sheets.Add
    Sheets(1).Name = "Summary " & CurrentDate
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "ActiveDate!R1C1:R1000C233", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="ActiveDate!R2C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion14


    The code is not identifying SourceData:= "ActiveDate!R1C1:R1000C233"

    Thanks for the help!

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,389

    Re: Daily PivotTable

    i have previously posted in this forum code for creating a pivot table, i will see if i can find it and add a link
    http://www.vbforums.com/showthread.p...ate+pivotcache
    the forum search returned many threads that might help

    "ActiveDate!R1C1:R1000C233"
    is activedate a sheet name? does it exist?
    Last edited by westconn1; Aug 12th, 2019 at 04:30 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2019
    Posts
    4

    Re: Daily PivotTable

    I will review the recommended post. Thank you!

    Quote Originally Posted by westconn1 View Post
    is activedate a sheet name? does it exist?
    Well, actually, it should be CurrentDate, not ActiveDate. Good catch. Still doesn't work though. The code does create a new sheet called "Aug-13-19", pulls the data and creates another sheet called " Summary Aug-13-19", but it fails when trying to link the PivotTable to SourceData:="ActiveDate!R1C1:R1000C233", which I was hoping to mean SourceData:="Aug-13-19!R1C1:R1000C233"

    Thanks!

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,389

    Re: Daily PivotTable

    maybe more like
    Code:
    SourceData:=currentdate & "!R1C1:R1000C233",
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2019
    Posts
    4

    Re: Daily PivotTable

    Still not working. It seems this whole part of the code is wrong, because I actually went and wrote the names of the sheets and it still gave me an error. I suspect it just records wrong? I took this straight from recording. I am looking at the other post you recommended and it seems way more complex than this recorded code (and I confess it looks beyond my skill level as well).

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,389

    Re: Daily PivotTable

    i did not remember that the code in that tread was so terrible to read, i certainly struggled with it when i was testing it, some others maybe better

    just to test, try assigning the range to a variable first then make that the datasource like
    Code:
    set myrange = sheets(currentdate).Cells(1, 1).Resize(1000, 233)
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    myrange, Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="ActiveDate!R2C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion14
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    New Member
    Join Date
    Aug 2019
    Posts
    4

    Re: Daily PivotTable

    Thank you for the above.
    Unfortunately, it didn't go through either.

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,389

    Re: Daily PivotTable

    do you want to post some sample data or workbook (zip first) for me to test with?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width