dcsimg
Results 1 to 9 of 9

Thread: [RESOLVED] Creating Pivot table using VB6 Code

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Resolved [RESOLVED] Creating Pivot table using VB6 Code

    Can anyone tell me ? . why it shows empty pivottables collection .see at the following line . let me know please .
    Set pt = ActiveSheet.PivotTables.Add(cacheofPvt, Range("A1", "A91"), "MYPT")
    Code:
    Sub CreatePivot()
    '
    ' Macro1 Macro
    '
    Dim pt As PivotTable
    Dim cacheofPvt As PivotCache
    Dim pf As PivotField
    Dim pi As PivotItem
    On Error Resume Next
    Sheets("pivot").Select
    ActiveSheet.PivotTables("MYPT").TableRange2.Clear
    
    'set the cache of PT (DataSource)
    Sheets("Data").Select
    Set caheofPvt = ActiveWorkbook.PivotCaches.Create(xlDatabase)
    Sheets("Pivot").Select
    Set pt = ActiveSheet.PivotTables.Add(cacheofPvt, Range("A1", "A91"), "MYPT")
    
       ' Columns("A:A").Select
    Application.Run "March03.xlsx!createPivot"
    
    End Sub
    Attached Images Attached Images  
    Last edited by firoz.raj; Jul 15th, 2013 at 12:15 PM.

  2. #2

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Creating Pivot table using VB6 Code

    i removed the on error resume next . and now it says .unable to get the pivotTables property of the worksheet class .
    at this line ActiveSheet.PivotTables("MYPT").TableRange2.Clear . let me know some valuable comment .
    Code:
    Sub CreatePvt()
    '
    ' Macro1 Macro
    '
    Dim pt As PivotTable
    Dim cacheofPvt As PivotCache
    Dim pf As PivotField
    Dim pi As PivotItem
    
    Sheets("pivot").Select
    ActiveSheet.PivotTables("MYPT").TableRange2.Clear
    
    'set the cache of PT (DataSource)
    Sheets("Data").Select
    Set caheofPvt = ActiveWorkbook.PivotCaches.Create(xlDatabase)
    Sheets("Pivot").Select
    Set pt = ActiveSheet.PivotTables.Add(cacheofPvt, Range("A1", "A91"), "MYPT")
    
       ' Columns("A:A").Select
        Application.Run "March03.csv!Macro1"
    
    End Sub

  3. #3
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,309

    Re: Creating Pivot table using VB6 Code

    Don't know (yet)....don't mess with pivottables at all...BUT, if you can solve your immediate issue, please note that this line:
    Set caheofPvt = ActiveWorkbook.PivotCaches.Create(xlDatabase)
    has 'caheofPvt', where it should be 'casheoPvt'

    Can you share a copy of your excel workbook (assuming it is not of personal nature)? Also, I hope you are usiing Option Explicit, and have declared all your variables properly (this would have shown the 'caheofPvt' issue.....

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Creating Pivot table using VB6 Code

    Still it says .Unable to get the PivotTables property of the Worksheet class . even i have written index name instead of name in pivottables collection property.
    Code:
    ActiveSheet.PivotTables(1).TableRange1.Clear
    and following is the code for it .
    Code:
    Option Explicit
        Dim pt As PivotTable
        Dim cacheofPvt As PivotCache
        Dim pf As PivotField
        Dim pi As PivotItem
    
    Sub CreatePivot()
    
    '
    ' Macro1 Macro
    '
       
    Sheets("pivot").Select
    ActiveSheet.PivotTables("pivot").TableRange2.Clear
    
    'set the cache of PT (DataSource)
    Sheets("Data").Select
    Set cacheofPvt = ActiveWorkbook.PivotCaches.Create(xlDatabase)
    Sheets("Pivot").Select
    Set pt = ActiveSheet.PivotTables.Add(cacheofPvt, Range("A1", "C17"), "PIVOT")
    
       ' Columns("A:A").Select
     Application.Run "storename.xlsx!CreatePivot"
    End Sub
    Last edited by firoz.raj; Jul 16th, 2013 at 07:40 AM.

  5. #5
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    7,309

    Re: Creating Pivot table using VB6 Code

    [QUOTE=firoz.raj;4461505]Still it says .Unable to get the PivotTables property of the Worksheet class . let me know please .[QUOTE]

    I am sure it does.....your program just never gets to that point because of your error in the line you mentioned....I wanted to play around with this 'new' (to me, that is) topic of pivot tables from VB, hence I asked if you can provide your Excel Workbook (or at least the portion with the data for your PT.

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,084

    Re: Creating Pivot table using VB6 Code

    Rhis really should be moved to the Office forum since it is using Excel VBA rather than VB

  7. #7

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Creating Pivot table using VB6 Code

    Rhis really should be moved to the Office forum since it is using Excel VBA rather than VB
    but it is totally VB6 Code DataMiser .later i would like to utilize this code in my vb6 Project .i will write all the code inside the bas module of vb6 ?.
    Last edited by firoz.raj; Jul 16th, 2013 at 08:01 AM.

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,084

    Re: Creating Pivot table using VB6 Code

    Looks like VBA to me. I see a comment that says Macro which hints at VBA I also see references to ActiveWorksheet which indicates Excel
    I do not see any code that creates an instance of Excel so to me that indicates this is being done in Excel and if that be the case then it is VBA not VB6 code.

    Note: VB6 code is not a macro and it is not done from within Office products, That is VBA which shows up as VB version 6 but is a different animal with some shared features.

    Edit even if you do want to do it in VB6 it is still better suited to the Office area as it is definitely using Excel specific features.

  9. #9

    Thread Starter
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Creating Pivot table using VB6 Code

    Now it is working fine . but i want to use this Sub(Macro) in other same type of Excel sheet . can anyone tell me ? . How should i enable it to utilize this macro in other excel sheet. later i want to implement the following code in My VB Application.
    Code:
    Sub CreateSales()
    '
    ' CreateSales Macro
    '
    ' Keyboard Shortcut: Ctrl+s
    '
        Columns("A:A").EntireColumn.AutoFit
        Columns("A:A").Select
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
            :=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
            TrailingMinusNumbers:=True
        Range("A11").Select
        Columns("A:A").ColumnWidth = 22.57
        Columns("B:B").EntireColumn.AutoFit
        Range("C3:C4").Select
        Selection.ClearContents
        Range("D3:F4").Select
        Selection.Cut
        Range("C3").Select
        ActiveSheet.Paste
        Range("C10").Select
        Selection.ClearContents
        Range("D10:F10").Select
        Selection.Cut
        Range("C10").Select
        ActiveSheet.Paste
        Range("C12").Select
        Selection.ClearContents
        Range("B12").Select
        ActiveCell.FormulaR1C1 = "Mark & Spencer Panorama"
        Range("D12:F12").Select
        Selection.Cut
        Range("C12").Select
        ActiveSheet.Paste
        Range("B10").Select
        ActiveCell.FormulaR1C1 = "Marks & Spencer MOA2"
        Columns("A:A").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        Rows("1:1").Select
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A1").Select
        ActiveCell.FormulaR1C1 = "SNO"
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "STOREID"
        Range("C1").Select
        ActiveCell.FormulaR1C1 = "LOCATION"
        Range("D1").Select
        ActiveCell.FormulaR1C1 = "DATE"
        Range("E1").Select
        ActiveCell.FormulaR1C1 = "QTY"
        Range("F1").Select
        ActiveCell.FormulaR1C1 = "RETAIL"
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "1"
        Range("A3").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("A2:A3").Select
        Selection.AutoFill Destination:=Range("A2:A15")
        Range("A2:A15").Select
        Range("A1").Select
        Sheets.Add
        ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "16!R1C1:R15C6", Version:=xlPivotTableVersion12).CreatePivotTable _
            TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
            :=xlPivotTableVersion12
        Sheets("Sheet1").Select
        Cells(3, 1).Select
        Sheets("16").Select
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "STOREID"
        Range("A1").Select
        Sheets("Sheet1").Select
        Range("C8").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("SNO")
            .Orientation = xlRowField
            .Position = 1
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("LOCATION")
            .Orientation = xlRowField
            .Position = 2
        End With
        With ActiveSheet.PivotTables("PivotTable1").PivotFields("DATE")
            .Orientation = xlRowField
            .Position = 3
        End With
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("QTY"), "Sum of QTY", xlSum
        ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RETAIL"), "Sum of RETAIL", xlSum
        Range("A8").Select
        ActiveSheet.PivotTables("PivotTable1").PivotFields("SNO").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        Range("A10").Select
        ActiveSheet.PivotTables("PivotTable1").PivotFields("DATE").Subtotals = Array( _
            False, False, False, False, False, False, False, False, False, False, False, False)
        Range("B11").Select
        With ActiveSheet.PivotTables("PivotTable1")
            .InGridDropZones = True
            .ShowDrillIndicators = False
            .RowAxisLayout xlTabularRow
        End With
        Columns("B:B").EntireColumn.AutoFit
        Range("B6").Select
        ActiveSheet.PivotTables("PivotTable1").PivotFields("LOCATION").Subtotals = _
            Array(False, False, False, False, False, False, False, False, False, False, False, False)
        Columns("A:A").ColumnWidth = 11.29
        Range("D5:E19").Select
        Selection.NumberFormat = "#,##0"
        Columns("C:C").Select
        Selection.NumberFormat = "mm/dd/yy;@"
        Columns("C:C").EntireColumn.AutoFit
        Range("A4").Select
        Application.WindowState = xlMinimized
        Application.WindowState = xlNormal
        Application.WindowState = xlMinimized
        ChDir "C:\Users\firoz.mallik\Desktop\Sales"
        Application.WindowState = xlMinimized
        Application.WindowState = xlMinimized
        Application.Run "'16.csv'!CreateSales"
        ActiveWindow.Close
    End Sub

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