Powershell Unable to find Excel and open Excel File

Question

I am trying to have Powershell copy, rename than edit a excel file. It copies and renames the file as intended however when I go to open the file with excel it is unable to find the file. See attached code.

Thank you for the help.

#Export Textbox outputs
$S0 = $textBox1.Text
$jobname = $textBox2.Text
$contractor = $TextBox3.Text

#combine textbox outputs
$folder = "$S0" + "_" + "$jobname" + "_" + "$contractor"
$subsubfolder = ".\"+"$folder" + "\Dir"
$takeoffname = "$s0" + "_takeoff.xlsx"

#Excel
$xl = New-Object -ComObject excel.application
Start-Sleep -Seconds 5
$xl.Visible = $true
Start-Sleep -Seconds 5
$wb = $xl.Workbooks.Open("$subsubfolder\$takeoffname")
$data = $wb.Worksheets.Item("Storm")
$Data.Cells.Item(1,2) = "$jobname"
$data.Cells.Item(1,7) = "$S0"
$wb.Save()
$xl.Quit()

NEW updated Code - Added Join path and It broke the create folder loop. Sorry IF the added requirement to make the folder creates extra problems.

$S0     = $TextBox1.Text
    $jobname    = $TextBox2.Text
    $contractor = $TextBox3.Text

$folder = ' {0}_{1}_{2}' -f $S0, $jobname, $Contractor
$file  = '{0}_takeoff.xlsx' -f $S0
$PILname = 'PIL_{0}.xlsx' -f $S0
Write-host $folder

New-Item -ItemType Directory "./$folder"
foreach($line in Get-Content $Filenames)
{
New-Item $folder\$line -ItemType Directory
}
$subfolder   = '{0}\1 - Estimating Original Quote Material' -f $folder
$subsubfolder = Join-Path -Path $PWD - ChildPath $Subfolder
$filePath = Join-Path -Path $PWD -ChildPath (Join-Path -Path $subsubfolderfolder -ChildPath $file)
$PILpath = Join-Path -Path $PWD -ChildPath (Join-Path -Path $subsubfolderfolder -ChildPath $PILname)
Write-host $filePath
Write-host $subsubfolder
pause
#Copy Files
Copy-Item '.\_master_takeoff.xlsx' "$subsubfolder\_master_takeoff.xlsx"
Copy-Item '.\PIL_S0XXXXX .xlsx' $subsubfolder
#Rename Files
Rename-Item -Path "$subsubfolder\_master_takeoff.xlsx" -newname $takeoffname
Rename-Item -Path "$subsubfolder\PIL_S0XXXXX .xlsx" -newname $PILpath

$xl = New-Object -ComObject excel.application
Start-Sleep -Seconds 5
$xl.Visible = $true
Start-Sleep -Seconds 5
$wb = $xl.Workbooks.Open("$subsubfolder\$takeoffname")
$data = $wb.Worksheets.Item("Storm")
$Data.Cells.Item(1,2) = "$jobname"
$data.Cells.Item(1,7) = "$S0"
$wb.Save()
$xl.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Answer 1

By taking strings off textboxes and combining that to a file path with string concatenation, you're bound to end up with a path that doesn't exist. Having said that, the error comes from using the .\ in the path.

Powershell may know where that is, but Excel will have no idea where to look for the file. Excel has its own Default path, usually pointing to the Documents folder and when given relative paths, it will use that.

Always use existing, absolute file paths for opening stuff in external applications.

Better use something like this

#Export Textbox outputs
$prefix     = $TextBox1.Text
$jobname    = $TextBox2.Text
$contractor = $TextBox3.Text

#combine textbox outputs to form the directory (I like using the -f format operator)
$file     = '{0}_takeoff.xlsx' -f $prefix
$folder   = '{0}_(1}_{2}\Dir' -f $prefix, $jobname, $contractor
$filePath = Join-Path -Path $PWD -ChildPath (Join-Path -Path $folder -ChildPath $file)

# test if the file can be founc
if (Test-Path $filePath -PathType Leaf) {
    $xl = New-Object -ComObject excel.application
    $xl.Visible = $true
    $wb = $xl.Workbooks.Open($filePath)
    $data = $wb.Worksheets.Item("Storm")
    $Data.Cells.Item(1,2) = $jobname
    $data.Cells.Item(1,7) = $prefix
    $wb.Save()
    $xl.Quit()

    # important: clean-up COM objects after use
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
    [System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null
    [System.GC]::Collect()
    [System.GC]::WaitForPendingFinalizers()
}
else {
    Write-Warning "File '$filePath' not found"
}

^{Instead of using $PWD (Print Working Directory) you can also use Get-Location which in fact is the same thing}

---

Since I have no idea why your updated code is creating subfolders, I'll leave that out here.

Please look at how the -f Format operator works because now you're doing that wrong.

Also, to not confuse the working directory for PowerShell and the default path for Excel anymore, define the full root path first in the code. Below I'm using a variable called $workingDir for that.

Copy-Item can copy and rename at the same time.

# let's forget about the 'Set-Location' and use absolute paths from the beginning
$workingDir = '\\Server\Share\Folder'  # set this to the real path

# Export Textbox outputs
$S0         = $TextBox1.Text
$jobname    = $TextBox2.Text
$contractor = $TextBox3.Text

# combine textbox outputs to form the directory (I like using the -f format operator)
$PILname  = 'PIL_{0}.xlsx' -f $S0
$file     = '{0}_takeoff.xlsx' -f $S0
$folder   = '{0}_(1}_{2}\1 - Estimating Original Quote Material' -f $S0, $jobname, $contractor
$folderPath = Join-Path -Path $workingDir -ChildPath $folder      # --> Full absolute path to the working folder
$filePath   = Join-Path -Path $folderPath -ChildPath $file        # --> Full absolute path to the file

Write-host $filePath
Write-host $folderPath

#Copy and rename master Files
$masterFile = Join-Path -Path $workingDir -ChildPath '_master_takeoff.xlsx'
$pilFile    = Join-Path -Path $workingDir -ChildPath 'PIL_S0XXXXX.xlsx'
Copy-Item -Path $masterFile -Destination $filePath
Copy-Item -Path $pilFile -Destination (Join-Path -Path $folderPath -ChildPath $PILname)

############################
#Write to new take off file
############################

# Call excel and open file
$xl = New-Object -ComObject excel.application
$xl.Visible = $true
$wb = $xl.Workbooks.Open($filePath)
$data = $wb.Worksheets.Item("Storm")
$Data.Cells.Item(1,2) = $jobname
$data.Cells.Item(1,7) = $S0
$wb.Save()
$xl.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

Answer 2

Everything seems ok with the code, but you are using relative paths.

If you are doing that, you need to change the working directory before opening the excel.

Ex: Set-Location C:\