Microsoft excel not open in another application
I am trying to run a PowerShell script that opens excel, reads some data, and closes excel. I was running the script earlier with no problems, but now there is a message that prevents the email from being sent that says the file is open in another application. The file is not open and there are no other users in the workbook. How can I prevent this message so that my email can send without any interaction?
# 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 yesterdays 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
# removed list elements for confidentiality
$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
$excel.Workbooks.Close()
# close excel instance
$excel.Quit()
# release excel instance
#$object = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
# 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()
# close outlook instance
$outlook.Quit()
I am trying to run a PowerShell script that opens excel, reads some data, and closes excel. I was running the script earlier with no problems, but now there is a message that prevents the email from being sent that says the file is open in another application. The file is not open and there are no other users in the workbook. How can I prevent this message so that my email can send without any interaction? # 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 yesterdays 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
# removed list elements for confidentiality
$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
$excel.Workbooks.Close()
# close excel instance
$excel.Quit()
# release excel instance
#$object = [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
# 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()
# close outlook instance
$outlook.Quit() Read More