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()

You're starting to make a mess of things. The foreach only creates folders but you never use them. Is that correct? Also, the first (or only) item to replace with -f is {0}, not {1} — Theo
– Theo, Commented Dec 26, 2019 at 16:02
Theo, 1st Thank you for helping me out on my fist Powershell script. The foreach creates folders that users will use at a later date but will not be used by powershell. also fixed the {1} — gizmobrat
– gizmobrat, Commented Dec 26, 2019 at 16:08
I have added new code for you, but left out the creation of the folders. As I understand, these wil be used for some other purpose, so you can add code to do that anywhere after if you like. — Theo
– Theo, Commented Dec 26, 2019 at 16:14

Theo · Accepted Answer · 2019-12-26 16:11:59Z

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()

Sorry It was not my intention to inject a new requirement. The old code was working and I did not think that it would be relevant my apologizes.

Paulo Alves · Accepted Answer · 2019-12-26 14:58:22Z

0

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:\

answered Dec 26, 2019 at 14:58

Paulo Alves

1628 bronze badges

2 Comments

gizmobrat Over a year ago

I have the Set-Location set to \\Server\Folder\Folder... a the start of the code

Paulo Alves Over a year ago

Then, the problems should reside on permissions to the UNC path. Try to copy to a temporary location, edit there, and place back on the UNC path.

Collectives™ on Stack Overflow

Powershell Unable to find Excel and open Excel File

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related