Microsoft excel not open in another application

SESA758156 

It seems that your PowerShell script is having trouble with the Excel file being perceived as open, even when it isn't. Here are some steps you can take to resolve the issue and ensure that your script runs smoothly:

1. Ensure Proper Closing of Excel Objects:
Make sure you're properly closing and releasing the Excel COM objects. This includes closing the workbook and quitting the Excel application. Additionally, explicitly releasing the COM objects can help prevent issues with files being locked. Here's how you can modify your script to include proper cleanup:

```powershell
# Create Excel instance
$excel = New-Object -ComObject Excel.Application

# Hide Excel instance
$excel.Visible = $false

# Locate file
$filePath = "https://confidential-my.sharepoint.com/personal/onlineUser/Documents/onlineExcel.xlsx?web=1"

# Open Excel workbook
$workbook = $excel.Workbooks.Open($filePath)

# Set Excel worksheet
$worksheet = $workbook.ActiveSheet

# Unsort Excel worksheet
$worksheet.Sort.SortFields.Clear()
$worksheet.Sort.SetRange($worksheet.UsedRange)
$worksheet.Sort.Apply()

# Get yesterday's date
$yesterdaysDate = (Get-Date).AddDays(-1).ToString("M/d/yyyy")

# Unfilter Excel worksheet
if ($worksheet.AutoFilterMode) {
$worksheet.AutoFilterMode = $false
}

# Filter Excel worksheet
$filter = $worksheet.ListObjects("OfficeForms.Table").Range.AutoFilter(3, $yesterdaysDate)

# Define microzones
$zone1 = @()
$zone2 = @()
$zone3 = @()
$zone4 = @()
$zone5 = @()
$zone6 = @()
$zone7 = @()
$superZone = @($zone1; $zone2; $zone3; $zone4; $zone5; $zone6; $zone7)

# Get Excel values
$values = @()
for ($i = 9; $i -le 15; $i++) {
foreach ($cell in $worksheet.ListObjects("tableName").Range.Columns.Item($i).SpecialCells(12).Cells) {
$values += $cell.Value2
}
}

# Remove Excel values
$values = $values | Where-Object { $_ -match "^\d" }

# Identify incomplete microzones
$emptyValues = @()
foreach ($zone in $superZone) {
if (-not ($values -contains $zone)) {
$emptyValues += $zone
}
}

# Close Excel workbook
$workbook.Close($false) # False to avoid saving changes

# Quit Excel instance
$excel.Quit()

# Release COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

# Garbage collect
[GC]::Collect()
[GC]::WaitForPendingFinalizers()

# Create Outlook instance
$outlook = New-Object -ComObject Outlook.Application

# Create mail item
$mail = $outlook.CreateItem(0)

# Set mail values
$mail.To = "user@dotcom"
$mail.Subject = "subject"
$body = "body"
$mail.Body = $body

# Send mail values
$mail.Send()

# Release Outlook COM objects
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($mail) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($outlook) | Out-Null

# Garbage collect
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
```

2. Check for File Locks:
Sometimes files can be locked by other processes or instances of Excel. Ensure that no other instances of Excel are running in the background that might be accessing the file.

3. **Verify File Path:**
Ensure that the file path you are using is accessible and correct. If the file is stored on SharePoint, make sure that the URL is properly formatted and accessible from the script.

4. Error Handling:
Add error handling to your script to capture and log any errors that might provide additional information about why the file is considered open.

5. Network Issues:
If the file is located on a network share or SharePoint, network issues could sometimes cause problems with file access. Ensure that the network connection is stable and that permissions are correctly set.

By incorporating these suggestions, you should be able to resolve the issue with the Excel file being considered open and ensure that your script runs without any interruptions. click here