Programmatically change chart properties using IMPORTEXCEL

PowerShell Help
1

Hi,

I am using the Add-ExcelChart command in the IMPORTEXCEL module to create excel charts. I have been able to achieve this but I would like to change one of the chart properties via Powershell that can be done manually under Format Chart Area > Chart Options > Properties.

I would like to set the property “Don’t move or size with cells”

Below is an extract from my script that defines and creates a chart:

      $ChartTitle="Live vs Archived Data"
      $tableName = 'LiveVsArch' # $tableName is the name to give to the Excel table hosting the data to be graphed
      $LiveVsArchWorksheetName="LiveVsArch Chart"
      $excel = $LiveVsArchive | Export-Excel -Path $ExportFilePath -WorksheetName $LiveVsArchWorksheetName -AutoSize -TableStyle Medium9 -TableName $tableName -PassThru -Append # In the existing workbook, create a new Worksheet and add the data as an Excel Table

      $ChartDef3 = @{ # $ChartDef3 defines the size, position and data of the Live vs Archived data graph
         Title = $ChartTitle
         ChartType = 'ColumnStacked'
         XRange = "$tableName[Versus]"
         YRange = "$tableName[Total_vs]"
         YAxisTitleText="Size (GB)"
         YAxisTitleSize=10
         YAxisTitleBold=$true
        #SeriesHeader = "Size`n(GB)"
         Row = 0
         Column = 4
         Width = 700
         Height = 500
        }
      $chart = Add-ExcelChart @ChartDef3 -NoLegend -Worksheet $excel.$LiveVsArchWorksheetName -PassThru # Create a 2nd chart and populate it with data from the last two columns of the table
      $chart.PlotArea.Fill.Color = [System.Drawing.Color]::LightYellow # Set the Plot Area colour to Light Yellow
      $chart.Fill.Color = [System.Drawing.Color]::LightBlue # Set the Fill colour around the plot area to Light Blue
      $chart.Series[0].Fill.Color = [System.Drawing.Color]::Green # Set the colour of the data bars to Green
      $chart.XAxis.Font.Color = [System.Drawing.Color]::Blue
      $chart.YAxis.Font.Color = [System.Drawing.Color]::Blue
      $chart.YAxis.Title.Font.Color = [System.Drawing.Color]::Black

Is there any way to change this property programmatically?

2

You can set the EditAs property to Absolute to set the “Don’t move or size with cells” property.

$chart.EditAs = 'Absolute'

However, this moves the chart to the top left corner of the sheet and it’s not easily repositioned (SetPosition() doesn’t work when EditAs is ‘Absolute’).

3

Thanks Matt, I’ll give that a try.

What would the value be to ‘move but don’t size with cells‘ ?

Primarily, my requirement is that the chart does not change size when rows or columns height and width are adjusted.

4

Set the value to 'OneCell'

5

Thanks Matt. I can work with those options. I looked everywhere for a chart parameter that would do it but had no luck.

6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.