VS Code with PowerShell extension fails to assig a value to a variable

[Gt3pccb]

Prerequisites

• I have written a descriptive issue title.
• I have searched all open and closed issues to ensure it has not already been reported.
• I have read the troubleshooting guide.
• I am sure this issue is with the extension itself and does not reproduce in a standalone PowerShell instance.
• I have verified that I am using the latest version of Visual Studio Code and the PowerShell extension.
• If this is a security issue, I have read the security issue reporting guidance.

Summary

When using VScode, ( I opened at bug there but was promptly closed by @andreamah. this code fails to assign a value to $worksheetName and $Chart. the code works just fine in a PowerShell window.

$excel                          = New-Object -ComObject Excel.Application 
$workbook                       = $excel.Workbooks.Open($ExcelFile)


#we need to save to a file because sometimes the ComObject acts up
Remove-Item  $tempValues  -Force -Confirm:$false -ErrorAction SilentlyContinue| Out-Null
($Workbook.Worksheets | Where-Object {$_.Name -imatch "pivot"} | Select-Object -Property Name).Name | Set-Content -Path  $tempValues  -Encoding ascii -Force -ErrorAction Stop 

[string]$worksheetName  = $null
$worksheetName          =   Get-Content -Path  $tempValues  -Encoding ascii 
($workbook.Worksheets | select-Object -Property Name).Name
<#If ($worksheetName -ilike $null)
{
  Write-Host "We could not find the worksheet name" -ForegroundColor Red
  $WSheetNameArr  = @()
  $WSheetNameArr  = ($workbook.Worksheets | select-Object -Property Name).Name 
  $worksheetName  = $WSheetNameArr | Where-Object {$_ -imatch "pivot"}
  #Pause
}#>
Remove-Item  $tempValues  -Force -Confirm:$false  -ErrorAction SilentlyContinue | Out-Null

# Get the worksheet containing the charts
$worksheet = $workbook.Worksheets.Item($worksheetName)
$worksheet.Activate()
# Get the charts on the worksheet
$chartObjects                   = $worksheet.ChartObjects()
Remove-Item -Path  $tempValues  -Force -Confirm:$false -ErrorAction SilentlyContinue | Out-Null

$chartName      = $null
($chartObjects | Where-Object {$_.Name -imatch "chart"} | Select-Object -Property Name).Name |  Set-Content -Path  $tempValues -Encoding ascii -Force
$chartName      = ($chartObjects | Where-Object {$_.Name -imatch "chart"} | Select-Object -Property Name).Name # Get-Content -Path  $tempValues -Encoding ascii
<#If ($chartName  -ilike $null)
{
  Write-Host "We could not find the chart name" -ForegroundColor Red
  $charNamesArr               = @()
  $charNamesArr               = ($chartObjects | Select-Object -Property Name).Name
  $chartName                  = $charNamesArr | Where-Object {$_.Name -imatch "chart"}
  #Pause
}#>
#Remove-Item -Path  $tempValues  -Force -Confirm:$false  -ErrorAction SilentlyContinue | Out-Null

$chart                          = $worksheet.ChartObjects($chartName).Chart
$chart.HasTitle                 = $true
$chart.ChartTitle.Text          = "Write perf in MBps"
$chart.Axes(1).HasTitle         = $true
$chart.Axes(1).AxisTitle.Text   = "Secs"
$chart.Axes(2).HasTitle         = $true
$chart.Axes(2).AxisTitle.Text   = "MBps"

#Set the active worksheet to RawData so we can find all the MT values
#$worksheet = $workbook.Worksheets.Item('RawData')
$firstRowRange = $worksheet.UsedRange.Rows(1)
# Get the range of data in the first row
$firstRowRange = $worksheet.UsedRange.Rows(1)
#$firstRowRange = $worksheet.UsedRange.Rows.count
# Find the column where the first cell is "MT"
$mtColumnIndex = 0
for ($i = 1; $i -le $firstRowRange.Columns.Count; $i++) {
  if ($firstRowRange.Cells.Item(1, $i).Value2 -eq "MT") {
      $mtColumnIndex = $i
      break
  }
}

# Check if the "MT" column was found
if ($mtColumnIndex -eq 0) {
  Write-Host "Could not find a column where the first cell is 'MT'"
  exit
}

# Create a named range for the "MT" column
$mtColumnRange = $worksheet.Columns($mtColumnIndex).EntireColumn
$mtColumnRange.Name = "MT"

# Get the unique values in the "MT" column
$uniqueValues = $mtColumnRange.Value2 | Sort-Object -Unique | Where-Object {$_ -inotlike "MT"}

$worksheet = $workbook.Worksheets.Item($worksheetName)

# Loop through the values of "MT" from 8 to 128
#$uniqueValues = 8,16
foreach($mt in $uniqueValues) {

# Set the name of the new worksheet
$newWorksheetName = [regex]::Escape("MT-" + $mt.ToString())

# Get the index of worksheet to copy
($Workbook.Worksheets | Where-Object {$_.Name -imatch "pivot"} | Select-Object -Property index).index | Set-Content -Path  $tempValues -Encoding ascii -Force -ErrorAction Stop
[Int]$WroksheetIndex                  =  Get-Content -Path  $tempValues -Encoding ascii #($Workbook.Worksheets | Where-Object {$_.Name -imatch "pivot"} | Select-Object -Property Name).Name #$Workbook.Worksheets | Where-Object {$_.Name -imatch "pivot"}
Remove-Item  $tempValues -Force -Confirm:$false  -ErrorAction SilentlyContinue | Out-Null


$workbook.Worksheets($WroksheetIndex).Copy($workbook.Worksheets($workbook.WorkSheets.count))
$newWorksheet = $Workbook.Worksheets |  Where-Object {$_.name -ilike "PivotData (2)"}
$newWorksheet.Name = $newWorksheetName
$newWorksheet.Activate()
$lastSheet = $workbook.WorkSheets.Item($workbook.WorkSheets.Count) 
$newWorksheet.Move([System.Reflection.Missing]::Value, $lastSheet)

# Get the pivot chart to modify
$pivotChart = $newWorksheet.ChartObjects($chartName).Chart

# Set the "MT" filter to display only the given value
$pivotChart.PivotLayout.PivotTable.PivotFields("MT").CurrentPage = $mt

# Set the chart title
$pivotChart.HasTitle = $true
$pivotChart.ChartTitle.Text = "Write Performance in MBps at $mt"

# Set the axis titles
$pivotChart.Axes(1).HasTitle = $true
$pivotChart.Axes(1).AxisTitle.Text = "Seconds"
$pivotChart.Axes(2).HasTitle = $true
$pivotChart.Axes(2).AxisTitle.Text = "MBps"

$pivotChartProps = $newWorksheet.ChartObjects($chartName)
$pivotChartProps.Top = 10
$pivotChartProps.Left = 0
$pivotChartProps.Width = $pivotChartProps.Width * 1.2
$pivotChartProps.Height = $pivotChartProps.Height * 1.3


}

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



$workbook.Save()
$excel.Quit()
# important: clean-up COM objects after use
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()```

### PowerShell Version

```console
$psversiontable

Name                           Value
----                           -----
PSVersion                      7.4.1
PSEdition                      Core
GitCommitId                    7.4.1
OS                             Microsoft Windows 10.0.22631
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Visual Studio Code Version

code --version
1.87.2
863d2581ecda6849923a2118d93a088b0745d9d6
x64

Extension Version

ms-vscode.powershell@2024.0.0

Steps to Reproduce

Rund the script and it will fail.

Visuals

No response

Logs

No response

[JustinGrote]

Can you more specifically provide an error? Also have you considered the ImportExcel module rather than using the Excel COM APIs directly? I currently cannot reproduce your code because it requires Windows Excel to be installed.

You may want to try running it in a PowerShell terminal in vscode vs the window that's labeled PowerShell extension to also see if there is a difference, also make sure you're not running Windows PowerShell vs PowerShell 7+

[github-actions]

This issue has been labeled as needing feedback and has not had any activity a week. It has been closed for housekeeping purposes.