Search code examples
excelvbahistogram

Creating a macro to make a pivot table + histogram does not work

I have made a logfile parsing tool using grep that creates 2 text files. 1 text file contains all the error codes + error messages and the other only the error codes.

I import them via a simple vba script into an excel file. Then I have two macro's that make text to columns so it is possible for the excel to read them more easily, this works fine.

The error codes + error messages (on sheet 2) only contain the codes + messages after the macro to put them into a column and then the doubles are deleted, making it a sort of list where the user can then see what error codes gives which message. This is OK.

The error codes are then put into Sheet 1 and the macro just creates a list of all the codes in column A.

Now, what I would like to do is have a macro that will take all of these error codes (the range will differ depending on which log file has been parsed an how many error codes it will contain) and put them into a histogram so the user can see visually which error codes comes up the most.

My ideal situation would be that the user just defines a path and then everything else is automated to the point where the histogram will show up and gives a clear look on which error is comming back the most.

What I have so far is that the grep parses through the log file and makes a few new txt files. The Excel file is then opened automatically and the txt files are being brought in automatically into the correct sheets. Then the macros are doing their things.

What I would need help with is how to create an automation for the histogram. I tried to search a lot on google and on here but most of the code is so convoluted and so big that it is not helpful for what I am trying to do here. I hope I can find an easy solution to this issue.

Thanks in advance for your ideas!

When recording a macro like Norie suggested, I get the following error:

Run Time error '1004' Application defined or Object defined error.

This is the code that the VBA then produces automatically:

Option Explicit
Sub Histogram()
'
' Histogram Macro
'

'
    Columns("A:A").Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R107C1", Version:=6).CreatePivotTable TableDestination:= _
        "Sheet10!R3C1", TableName:="PivotTable25", DefaultVersion:=6
    Sheets("Sheet10").Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable25")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable25").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable25").RepeatAllLabels xlRepeatLabels
    With ActiveSheet.PivotTables("PivotTable25").PivotFields("40520")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable25").AddDataField ActiveSheet.PivotTables( _
        "PivotTable25").PivotFields("40520"), "Count of 40520", xlCount
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range( _
        "[From notepad to excel test 1.xlsm]Sheet10!PivotTable25")
End Sub

It seems like the source is incorrect. I tried changing "Sheet1!R1C1:R107C1" to "Sheet1!$A:$A" but this did not help either.

Solution

  • I wouldn't say the source is wrong, the problem is it's hard-coded.

    You need to change it so it's not hard-coded, specifically you need to change 107 to reflect the last row of data in column A on Sheet1.

    Also, some other things are being give seemingly arbitrary names, e.g. PivotTable25 - it would be better if you used specific names in the code.

    Another thing, you should add a header to the column with the error codes.

    In the following code it's assumed the data is on Sheet1 and has a header of 'Errors', and 'ErrorPivot' has been used for the name of the pivot table being created.

    Sub Histogram()
Dim wsPivot As Worksheet
Dim chtErrors As Chart
Dim ptErrors As PivotTable
Dim lngLastRow As Long

    lngLastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    
    Set wsPivot = Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R" & lngLastRow & " C1", Version:=6).CreatePivotTable TableDestination:= _
        wsPivot.Name & "!R3C1", TableName:="ErrorPivot", DefaultVersion:=6
        
    Set ptErrors = wsPivot.PivotTables("ErrorPivot")
    
    With ptErrors
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    
    With ptErrors.PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    
    ptErrors.RepeatAllLabels xlRepeatLabels
    
    ptErrors.AddDataField ptErrors.PivotFields("Errors"), "Count of Errors", xlCount
        
    With ptErrors.PivotFields("Errors")
        .Orientation = xlRowField
        .Position = 1
    End With
    
    Set chtErrors = wsPivot.Shapes.AddChart2(201, xlColumnClustered).Chart
    
    chtErrors.SetSourceData Source:=ptErrors.DataBodyRange
    
End Sub