CODEWITHSUNDEEP
functionpowershellreturn
gizmo
gizmo

Reputation: 23

Powershell not returning correct value

As some background, this should take an excel file, and convert it to PDF (and place the PDF into a temporary folder).

E.g. 'C:\Users\gjacobs\Desktop\test\stock.xlsx' becomes 'C:\Users\gjacobs\Desktop\test\pdf_merge_tmp\stock.pdf'

However, the new file path does not return correctly.

If I echo the string $export_name from within the function, I can see that it has the correct value: "C:\Users\gjacobs\Desktop\test\pdf_merge_tmp\stock.pdf".

But once $export_name is returned, it has a different (incorrect value): "C:\Users\gjacobs\Desktop\test\pdf_merge_tmp C:\Users\gjacobs\Desktop\test\pdf_merge_tmp\stock.pdf".

function excel_topdf{

    param(
        $file
    )
    
    #Get the parent path
    $parent = Split-Path -Path $file

    #Get the filename (no ext)
    $leaf = (Get-Item $file).Basename

    #Add them together.
    $export_name = $parent + "\pdf_merge_tmp\" + $leaf + ".pdf"

    echo ($export_name) #prints without issue.

    #Create tmp dir
    New-Item -Path $parent -Name "pdf_merge_tmp" -ItemType "Directory" -Force

    $objExcel = New-Object -ComObject excel.application
    $objExcel.visible = $false

    $workbook = $objExcel.workbooks.open($file, 3)
    $workbook.Saved = $true

    $xlFixedFormat = “Microsoft.Office.Interop.Excel.xlFixedFormatType” -as [type]
    $workbook.ExportAsFixedFormat($xlFixedFormat::xlTypePDF, $export_name)
    $objExcel.Workbooks.close()
    
    $objExcel.Quit()

    return $export_name
}

$a = excel_topdf -file 'C:\Users\gjacobs\Desktop\test\stock.xlsx'
echo ($a)

Upvotes: 1

Views: 1021

Answers (1)

Robert Dyjas
Robert Dyjas

Reputation: 5217

The issue you're experiencing is caused by the way how PowerShell returns from functions. It's not something limited to New-Item cmdlet. Every cmdlet which returns anything would cause function output being altered with the value from that cmdlet.

As an example, let's take function with one cmdlet, which returns an object:

function a {
 Get-Item -Path .
}

$outputA = a
$outputA

#### RESULT ####

    Directory:


Mode                LastWriteTime         Length Name
----                -------------         ------ ----
d--hs-       12/01/2021     10:47                C:\

If you want to avoid that, these are most popular options (as pointed out by Lasse V. Karlsen in comments):

# Assignment to $null (or any other variable)
$null =  Get-Item -Path .

# Piping to Out-Null
Get-Item -Path . | Out-Null

NOTE: The behavior described above doesn't apply to Write-Host:

function b {
 Write-Host "bbbbbb"
}

$outputB = b
$outputB

# Nothing displayed

Interesting thread to check if you want to learn more.

Upvotes: 1

Related Questions