How to select charts in the excel workbook and export it using Powershell?

Question

I have an excel workbook with charts in different worksheets. I need to export all the charts/pivot tables in an image format using Powershell.

I was able to open excel workbook and activate the current worksheet using powershell(code below). But I don't know how to select the chart on the worksheet and export it.

$Excel = New-Object -ComObject excel.application
$Excel.visible = $true
$Excel.WindowState = "xlMaximized"
$source_wb = $excel.Workbooks.open(C:/abc.xml)
$source_ws = $source_wb.Worksheets.item(1) 
$source_ws.activate()

How do I continue with this code to select the chart and export it?

Doug Finke has done a lot of this already in his importexcel module. github.com/dfinke/ImportExcel you can find the full module on the Gallery — thom schumacher

Mehul Gogri Mehul Gogri · Accepted Answer · 2018-03-28T19:57:47

This code worked for me. (Just attaching the for loop from my script that was doing the chart export)

$macros_wb = $excel.Workbooks.open(xyz.xlsx)

$chart_worksheets = @("Open Bugs", "Open Stories", "Change Rate", "Cumulative Chart", "Weekly Progress")

$OutputType = "JPG"

foreach ($item in $chart_worksheets)
    {
        $macros_ws = $macros_wb.WorkSheets.item($item)
        $macros_ws.activate()
        $excelchart = $macros_ws.ChartObjects(1)
        $Excel.Goto($excelchart.TopLeftCell,$true) 
        $ImagePath = Join-Path -Path $Destination -ChildPath ($macros_ws.Name + "_" + ($excelchart.Chart.ChartTitle.Text) + ".$OutputType")
        if ($excelchart.Chart.Export($ImagePath, $OutputType)) #Export returns true/false for success/failure
            {Write-Output "Exported $ImagePath"}
        else
            {Write-Output "Failure Exporting $ImagePath"}
    }

How to select charts in the excel workbook and export it using Powershell?

1 Answers