locked
Changing the trendline to show Linear with forward period RRS feed

  • Question

  • Hello,

    I am trying to use the Linear Trendline chart to go 50 days in the future.

    The linear Trendline is the best option for me to use.

    The command I am using to show 50 days in the future is 

    $tl=$chart.seriesCollection(1).trendlines().add(-4132,2,2,50)

    but it is using xlLinear (which isn't what I want).

    All my code that I use within powershell for the XLS part is below

                                                                                             

    THanks.

    ################################
    # Creating excel com object
    ################################

            $xl = new-object -ComObject Excel.Application   
            $fileName = "C:\scripts\tempxlsserverhdrive1.xlsx"
            $wb = $xl.Workbooks.Open($fileName)

    ################################
    #Open the first sheet of the excel 
    ################################
            $wsChart = $wb.WorkSheets.item(1) 


    #################################################################
    #FORMATTING THE COLUMNS
    #################################################################


            $objRange = $wsChart.UsedRange
            [void]$objRange.EntireColumn.Autofit() 
            $wb.Save()

    ######################
    #CREATING THE GRAPH
    ######################

            $xlChart=[Microsoft.Office.Interop.Excel.XLChartType]


            ################################
            # Activating the Data sheet
            ################################
            $wsChart.activate() 
            # Adding the Chart
            $chart = $wsChart.Shapes.AddChart().Chart

            ################################
            #selecting the Range for X and Y
            ################################ 
            $range = $wsChart.range("a${xrow}:b$yrow") # sets the Data range we want to chart

            #########################################
            # Providing the chart type - Line chart
            #########################################
            $chart.ChartType = 4
            $chart.setSourceData($range)

            # Setting up the position of chart (Not required if the sheet has just one chart). It will create the chart at top left corner
            #$wsChart.shapes.item("Chart 1").top = 50
            #$wsChart.shapes.item("Chart 1").left = 200

            #Resize the Chart 
            $RngToCover = $wsChart.Range("D5:AA30") # This is where we want the chart

            $ChtOb = $chart.Parent # This selects the current Chart
            $ChtOb.Top = $RngToCover.Top # This moves it up to row 5
            $ChtOb.Left = $RngToCover.Left # and to column D 
            $ChtOb.Height = $RngToCover.Height # resize This sets the height of your chart to Rows 5 - 30
            $ChtOb.Width = $RngToCover.Width # resize This sets the width to Columns D - AA


            # Set it true if want to have chart Title and Legend
            $chart.HasTitle = $true
            $chart.HasLegend = $true


            # Providing the Title for the chart
            $chart.ChartTitle.Text = "$ServerName Future Free Disk Usuage + 57 Days  - $ServerVolume"

            #####################################################
            #adding trend line 
            #https://msdn.microsoft.com/en-us/vba/excel-vba/articles/xltrendlinetype-enumeration-excel   XlTrendlineType Enumeration
            #xlLinear -4132
            #xlExponential 5
            #xlMovingAvg 6
            #xlLogarithmic -4133
            #xlPolynomial 3
            #xlPower 4

            #####################################################

            $tl=$chart.seriesCollection(1).trendlines().add()

           #$chart.seriesCollection(1).trendlines().add(-4132,2,2,57)
           #use $chart.seriesCollection(1).trendlines().add(5,2,2,40)   too hide the info the chart



            # Save the sheet
            $wb.Save()  

            # Closing the work book and xl
            $wb.close() 
            $xl.Quit()
            # Releasting the excel com object
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl)

    Wednesday, February 21, 2018 12:05 AM

Answers

  • FOUND the fix

    $tl=$chart.seriesCollection(1).trendlines().add()
            $tl.DisplayEquation = $flase
            $tl.DisplayRSquared = $flase
            $tl.Forward = 50
            $tl.Name = 'Future Trend'

    There we go. The above comments will replace $tl=$chart.seriesCollection(1).trendlines().add(-4132,2,2,57)

    • Marked as answer by Goce_D Wednesday, February 21, 2018 1:25 AM
    Wednesday, February 21, 2018 1:25 AM

All replies

  • i can get the Linear Trendline if I use this

    $tl=$chart.seriesCollection(1).trendlines().add()

    but not sure how I can add the extra 50 days to it.

    I do understand the parameters $chart.seriesCollection(1).trendlines().add(-4132,2,2,57)    within the add area. just cant work out the linear trendline.

    Wednesday, February 21, 2018 12:33 AM
  • FOUND the fix

    $tl=$chart.seriesCollection(1).trendlines().add()
            $tl.DisplayEquation = $flase
            $tl.DisplayRSquared = $flase
            $tl.Forward = 50
            $tl.Name = 'Future Trend'

    There we go. The above comments will replace $tl=$chart.seriesCollection(1).trendlines().add(-4132,2,2,57)

    • Marked as answer by Goce_D Wednesday, February 21, 2018 1:25 AM
    Wednesday, February 21, 2018 1:25 AM
  • Hi,

    Good to hear that you have solved this issue by yourself. In addition, thanks for sharing your solution in the forum as it would be helpful to anyone who encounters similar issues.

    If there is anything else we can do for you, please feel free to post in the forum.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, February 21, 2018 2:08 AM