Change the axis type of an Excel chart using PowerShell

Question

How do I change the axis type of an Excel chart's X axis using PowerShell?

To do this manually using Excel:

open workbook in Excel
select X axis chart
select "Axis options"
configure "Text axis" under the "Axis Type" option

Update: it's XLSX format. I should clarify: the issue is that the data set I'm plotting uses dates on the x axis and Excel is intelligently filling in the gaps (so if I have May 5 and May 7 in the data set, Excel plots May 6 too). Swapping to Text appears to fix this issue in the Excel app. I wish to automate this like the rest of my data analysis and charting.

What format, XLS or XLSX?

Taosique
– Taosique

2016-04-20 16:30:58 +00:00
Commented Apr 20, 2016 at 16:30 — Taosique
– Taosique, Commented Apr 20, 2016 at 16:30
@Taosique please see the question update. Thanks.

Federer
– Federer

2016-04-21 08:05:22 +00:00
Commented Apr 21, 2016 at 8:05 — Federer
– Federer, Commented Apr 21, 2016 at 8:05

Taosique · Accepted Answer · 2016-04-21 10:40:32Z

7

+300

There you are:

# Launch Excel:
$app = New-Object -comobject Excel.Application

# Open a Workbook:
$app.Workbooks.Open("C:\Users\admin\Desktop\Workbook.xlsx")

# Activate the chart you need (note: you don't need full path with filename now):
$app.Workbooks("Workbook.xlsx").Sheets("Sheet1").ChartObjects("Chart 1").Activate()

# Change the category type of axis. Axes(1) means category axis. Possible values:
# xlCategoryScale = 2
# xlTimeScale = 3
# xlAutomaticScale = -4105
$app.ActiveChart.Axes(1).CategoryType = 2

# Save & close the workbook
$app.Workbooks("Workbook.xlsx").Close($True)

You can now return to step 2, open another workbook and repeat these actions. After you're done, close the Excel application and clean up:

$app.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($app)
Remove-Variable app

edited Apr 21, 2016 at 10:40

answered Apr 21, 2016 at 9:29

Taosique

7,9143 gold badges33 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Federer Over a year ago

Thank you for both the answer and the last bit that releases the ComObject. I suspect you'll say experience but may I ask what documentation you used, e.g. to find out the TimeScale value or the Axes(1) meaning?

Taosique Over a year ago

I used these references: msdn.microsoft.com/en-us/library/… msdn.microsoft.com/en-us/library/… .To find values, I used Excel VBA editor: stepped on breakpoint and added watch for xlCategory to see it equals 1. Also its a good way to find what VBA code you need by recording a macro, doing things manually in Excel and then looking at the code it generates.

Collectives™ on Stack Overflow

Change the axis type of an Excel chart using PowerShell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related