How to pass field name with spaces like 'My Visitors' without underscore

[cnEUL]

try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return}

#Get rid of pre-exisiting sheet
$xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx"
Write-Verbose -Verbose -Message  "Save location: $xlSourcefile"
Remove-Item $xlSourcefile -ErrorAction Ignore

$data = ConvertFrom-Csv @"
Week, 'My Visitors'
1,11916
2,11665
3,13901
4,15444
5,21592
6,15057
7,26187
8,20662
9,28935
10,32443
"@

$cd = New-ExcelChartDefinition `
    -XRange Week `
    -YRange 'My Visitors' `
    -Title "No. Of Visitors" `
    -ChartType ColumnClustered `
    -NoLegend `
    -ChartTrendLine Linear

$data | Export-Excel $xlSourcefile -Show -AutoNameRange -AutoSize -TableName Visitors -ExcelChartDefinition $cd

Result: WARNING: Range name ''My Visitors'' contains illegal characters, they will be replaced with '_'.
But also, excel could not show the chart it has problems with at least one reference of a formular.


Chris
`

[dfinke]

The -AutoNameRange range cannot work with ' ' in the name. If you try to create a named range with at space in the name in the Excel app, I believe it complains too.

[cnEUL]

Can I specify something absolute as an alternative, such as a column number?

[dfinke]

For AutoNameRange? No

[MMitsialis]

Hi, I might be mistaken, but it seem you have a mismatch in your hash table columns and your variables. ` $data = ConvertFrom-Csv @" Week, **'My Visitors**' ` Does not match ` -YRange '**My Visitors**' ` **'My Visitors**' <> '**My Visitors**' Maybe change to: 'My Visitors' or MyVisitors Ciao, Marc

…

On Wed, 11 Jan 2023, 15:59 Christian Niedergesäß, ***@***.***> wrote: try {Import-Module $PSScriptRoot\..\..\ImportExcel.psd1} catch {throw ; return} #Get rid of pre-exisiting sheet $xlSourcefile = "$env:TEMP\ImportExcelExample.xlsx" Write-Verbose -Verbose -Message "Save location: $xlSourcefile" Remove-Item $xlSourcefile -ErrorAction Ignore $data = ConvertFrom-Csv @" Week, **'My Visitors**' 1,11916 2,11665 3,13901 4,15444 5,21592 6,15057 7,26187 8,20662 9,28935 10,32443 "@ $cd = New-ExcelChartDefinition ` -XRange Week ` -YRange '**My Visitors**' ` -Title "No. Of Visitors" ` -ChartType ColumnClustered ` -NoLegend ` -ChartTrendLine Linear $data | Export-Excel $xlSourcefile -Show -AutoNameRange -AutoSize -TableName Visitors -ExcelChartDefinition $cd Result: WARNING: Range name ''My Visitors'' contains illegal characters, they will be replaced with '_'. But also, excel could not show the chart it has problems with at least one reference of a formular. Chris ` — Reply to this email directly, view it on GitHub <#1372>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACONYAMFQ6WDP2ICYSOAKWTWR24DFANCNFSM6AAAAAATYCKBQQ> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[dfinke]

Yes, that would work, no spaces. Export-Excel will add a _ to keep it from failing and issue a warning.