VSCode fails to assign a value

[Gt3pccb]

Type: Bug

When one does a few oprations VScode fails to assign values $excel = New-Object -ComObject Excel.Application $workbook = $excel.Workbooks.Open($ExcelFile)

These 2 fail ($Workbook.Worksheets | Where-Object {$.Name -imatch "pivot"} | Select-Object -Property Name).Name` | Set-Content -Path $tempValues -Encoding ascii -Force -ErrorAction Stop $Worksheet = ($workbook.Worksheets | select-Object -Property Name).Name | Where-Object {$ -imatch "pivot"} There are multiple worksheets including one named "RawDataPivot"

VS Code version: Code 1.87.2 (863d2581ecda6849923a2118d93a088b0745d9d6, 2024-03-08T15:20:17.278Z) OS version: Windows_NT x64 10.0.22631 Modes:

System Info

|Item|Value| |---|---| |CPUs|11th Gen Intel(R) Core(TM) i7-11370H @ 3.30GHz (8 x 3302)| |GPU Status|2d_canvas: enabled
canvas_oop_rasterization: enabled_on
direct_rendering_display_compositor: disabled_off_ok
gpu_compositing: enabled
multiple_raster_threads: enabled_on
opengl: enabled_on
rasterization: enabled
raw_draw: disabled_off_ok
skia_graphite: disabled_off
video_decode: enabled
video_encode: enabled
vulkan: disabled_off
webgl: enabled
webgl2: enabled
webgpu: enabled| |Load (avg)|undefined| |Memory (System)|31.84GB (17.29GB free)| |Process Argv|--crash-reporter-id e759a85e-721f-408b-b3e7-6d7c110988f4| |Screen Reader|no| |VM|0%|

Extensions (1)

Extension|Author (truncated)|Version ---|---|--- powershell|ms-|2024.0.0

A/B Experiments

``` vsliv368cf:30146710 vspor879:30202332 vspor708:30202333 vspor363:30204092 vscod805:30301674 binariesv615:30325510 vsaa593cf:30376535 py29gd2263:30899288 c4g48928:30535728 azure-dev_surveyone:30548225 2i9eh265:30646982 962ge761:30959799 pythongtdpath:30769146 welcomedialogc:30910334 pythonidxpt:30866567 pythonnoceb:30805159 asynctok:30898717 pythontestfixt:30902429 pythonregdiag2:30936856 pyreplss1:30897532 pythonmypyd1:30879173 pythoncet0:30885854 h48ei257:30998030 pythontbext0:30879054 accentitlementst:30995554 dsvsc016:30899300 dsvsc017:30899301 dsvsc018:30899302 cppperfnew:30979542 d34g3935:30971562 fegfb526:30981948 bg6jg535:30979843 ccp1r6:30993540 dsvsc020:30976470 pythonaic:30996667 dsvsc021:30996838 g1icg217:30999571 ```

[Gt3pccb]

for some reason these 2 lines got stripped out and go at the very top $excel = New-Object -ComObject Excel.Application $workbook = $excel.Workbooks.Open($ExcelFile)

[IllusionMH]

/needsMoreInfo

How this is related to VS Code editor itself and not just problem with the script?

Does it behave differently in VS Code and in OS outside of VS Code?

P.S. If you paste code blocks you should wrap them in code blocks ```

[vscodenpa]

Thanks for creating this issue! We figured it's missing some basic information or in some other way doesn't follow our issue reporting guidelines. Please take the time to review these and update the issue.

Happy Coding!

[Gt3pccb]

#  #Find chart names
$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      = 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()

[IllusionMH]

It should be three backtics ``` before & after code block.

However there's a lot of code but it doesn't answer main questions

How this is related to VS Code editor itself and not just problem with the script?

Does it behave differently in VS Code and in OS outside of VS Code?

[Gt3pccb]

I can't get some parts of the block, as Github can't "render" them. But there is not much to it. If someone is internal to MSFT let me know, I could demonstrate the problem, as it happens quite often.

[IllusionMH]

Why this issue is created in issue tracker for VS Code editor itself? What is issue with VS Code?

[Gt3pccb]

It should be three backtics ``` before & after code block.

However there's a lot of code but it doesn't answer main questions

How this is related to VS Code editor itself and not just problem with the script?

Does it behave differently in VS Code and in OS outside of VS Code?

The code works when running directly from the shell, 100% of the time. But when I try to run it in VS the variable assignments for both "worksheetName" and "chartName" the behavior is inconsistent. For example: if I insert a breakpoint just before the variable assignments, and run the line on the terminal it works, but when the line gets executed in the script the assignment sometimes does not work that is why I added the second assignment if the first one does not work.

[andreamah]

This seems to be an issue with the extension that you're using to run this, which seems to be the powershell extension based on what you have installed. Please file this here instead https://github.com/PowerShell/vscode-powershell/issues