none
Powershell 4, Excel 2010, Pivottable RRS feed

  • Question

  • Hello, To all and Doug Finke.

    I am working on a script that obtains data from Excel .xlsx and builds out a pivot table, then eventually a pivot chart.

    the code i am trying to convert it this. I have the rest working.

    This code is from a vb Macro from Excel 2010

        Range("A16").Select
        Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
            False, True, False, True)

    Please help convert this to Powershell. So you can see what i have been doing so far. I converted the below code from.. too...

        With ActiveSheet.PivotTables("PivotTable1").PivotFields("Submit Date")
            .Orientation = xlRowField
            .Position = 2
        End With
    
    
    $PivotFields = $Worksheet2.PivotTables("PivotTable1").PivotFields("Submit Date") # ColumnLables = "Submit Date"
    $PivotFields.Orientation = $xlRowField
    $PivotFields.Position = 1

    This works fine.

    Thank you in advance for helping. Jeff W.


    Jeffrey I Wilson

    • Moved by Bill_Stewart Thursday, October 2, 2014 5:03 PM Move to more appropriate forum
    Thursday, October 2, 2014 4:29 PM

Answers

All replies

  • You are trying to use a grouping command.  Look up the Interop method for grouping to get the format for the class method.

    You can use the C# example almost directly.


    ¯\_(ツ)_/¯

    Thursday, October 2, 2014 6:45 PM
  • Thank you for the reply JRV, but it does not provide me the syntax I am looking for. I have been scratching my head trying to convert this from VB Macro code for a few days now. This is why I am asking for help here. Not to sound helpless, but can you provide me with one line of syntax to convert the "grouping" command in powershell?

    Thanks,

    Jeff W.


    Jeffrey I Wilson

    Thursday, October 2, 2014 7:02 PM
  • Thank you for the reply JRV, but it does not provide me the syntax I am looking for. I have been scratching my head trying to convert this from VB Macro code for a few days now. This is why I am asking for help here. Not to sound helpless, but can you provide me with one line of syntax to convert the "grouping" command in powershell?

    Thanks,

    Jeff W.


    Jeffrey I Wilson

    It is the same in POwershell as it is in VB.Net and C#.  Just add the dollar signs.

    I don't haveyour spreadsheet so I cannot show you easily and I do not feel like building a whole example that you woul understand.

    C# code works well in PowerShell with small adjustments.

    If you want to post your scritp and an example of the spreadsheet I will take a look.


    ¯\_(ツ)_/¯

    Thursday, October 2, 2014 7:47 PM
  • I have most of the code complete, howerver there is one statement that baffles me. It for "Grouping"... I need to Convert this... I am not sure how to go about it. i don't know how to convert this to Powershell.

    Thanks again....

        Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _
            False, True, False, True)


    Jeffrey I Wilson

    Tuesday, October 7, 2014 12:10 PM
  • You need to learn to look up the API calls for Office on MSDN.

    http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.group.aspx

    The Interop documents tell you how to use C#,VB and PowerShell to call the Interop methods.  YOu can also use "Invoke" to call by parameter name on any object method.

    You can also just use PowerShell to get a description of the method signatures.


    ¯\_(ツ)_/¯

    Tuesday, October 7, 2014 12:38 PM
  • Here is one:

    PS C:\scripts> $xl.ActiveSheet.Range('a1','c5').Group

    OverloadDefinitions
    -------------------
    Variant Group (Variant, Variant, Variant, Variant)

    But it may change for different objects.


    ¯\_(ツ)_/¯

    Tuesday, October 7, 2014 12:41 PM
  • I have this to group the data, but I need to use the months and years...

    $range22 = $excel.Range("a7")
    $range22.Group()

    Jeff

    Jeffrey I Wilson


    • Edited by blackd1 Tuesday, October 7, 2014 12:59 PM
    Tuesday, October 7, 2014 12:53 PM
  • What are you asking?  You are trying to group a single column?

    $range.Group($start,$end,$by,$periods)


    ¯\_(ツ)_/¯

    Tuesday, October 7, 2014 1:07 PM
  • Here is a discussion which will help you understand.

    http://stackoverflow.com/questions/26063633/converting-pivot-code-from-vba-to-powershell


    ¯\_(ツ)_/¯

    • Marked as answer by blackd1 Tuesday, October 7, 2014 6:27 PM
    Tuesday, October 7, 2014 1:08 PM
  • # Select the cells. 
    $Worksheet2.Cells.Item(1,1).Select()
    $WorkBook.ShowPivotTableFieldList = $true
    
    #$PivotFields = $Sheet1.PivotTables("PivotTable1").PivotFields($columnFields)
    $PivotFields = $Worksheet2.PivotTables("PivotTable1").PivotFields("Field1") # ColumnLables     = "State"
    $PivotFields.Orientation = $xlColumnField
    $PivotFields.Position = 1 
    $PivotFields = $Worksheet2.PivotTables("PivotTable1").PivotFields("Field2") # ColumnLables = "Submit Date"
    $PivotFields.Orientation = $xlRowField
    $PivotFields.Position = 1 
    $PivotFields = $Worksheet2.PivotTables("PivotTable1").PivotFields("Field3") # ColumnLables = Actual Completion Date"
    $PivotFields.Orientation = $xlDataField
    $PivotFields.Position = 1 
    # Set the range of the group. 
    $range22 = $excel.Range("a7")
    # $range22.Group() # To test the basic group. 
    # Here is where the grouping is done. 
    $periods = @($false, $false, $false, $false, $true, $false, $true)
    $range22.Group($true, $true, [Type]::Missing, $periods)
    

    This is the final working code. I appreciate all your help with this..

    Jeffrey I Wilson

    Tuesday, October 7, 2014 6:28 PM
  • Great.  I haven't done Excel pivot tables in a very long time.  I had to fish for the memories.

    ¯\_(ツ)_/¯

    Tuesday, October 7, 2014 6:31 PM
  • Hey jrv. i am new to this, how do you get "points", I have a few posts, but not sure how this works...

    Thanks. JIW


    Jeffrey I Wilson

    Tuesday, October 7, 2014 6:47 PM
  • Hey jrv. i am new to this, how do you get "points", I have a few posts, but not sure how this works...

    Thanks. JIW


    Jeffrey I Wilson


    Read the forums help on the report page of your profile.

    ¯\_(ツ)_/¯

    Tuesday, October 7, 2014 8:30 PM