Hello, I believe the issue is with a bug in the excel object that does not aloow it to be run in a task in windows task scheduler unless the user has checked that the user has to be logged on to run. If you have it to run without user logged on the excel
part of the script will not run correctly. I have been testing and breaking my head with this. here is my code
$filenameB = "C:\Users\administrator\Documents\My Dropbox\2010-12-31_1510_SYSB_Last_Month_GroupName_Logins_GroupCalls.csv"
$filenameC = "C:\Users\administrator\Documents\My Dropbox\2010-12-31_1510_SYSC_Last_Month_GroupName_Logins_GroupCalls.csv"
$filenameE = "C:\Users\administrator\Documents\My Dropbox\2010-12-31_1510_SYSE_Last_Month_GroupName_Logins_GroupCalls.csv"
$dataB = Import-Csv $filenameB
$dataC = Import-Csv $filenameC
$dataE = Import-Csv $filenameE
#Creat a new excel Object
$Excel = New-Object -ComObject Excel.Application
#make it invisible
$Excel.visible = $False
#No alerts
$Excel.DisplayAlerts = $False
#NOw we add a workbook
$file1 = "C:\Users\administrator\Documents\My Dropbox\System_BCE_2011_TalkGroups_Logins_GroupCalls - Copy.xlsx"
$Excel = $Excel.Workbooks.open($file1)
$SheetB = $Excel.Worksheets.Item(1)
$SheetC = $Excel.Worksheets.Item(2)
$SheetE = $Excel.Worksheets.Item(3)
$date = (Get-Date).AddMonths(-1)
$month = Get-Date($date) -f "MM-yyyy"
Switch ($month)
{
"12-2010" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,5) = $line.logins
$SheetB.Cells.Item($intRow,6) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC){
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,5) = $line.logins
$SheetC.Cells.Item($intRow,6) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE) {
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,5) = $line.logins
$SheetE.Cells.Item($intRow,6) = $line.group_calls
$intRow++
}
}
"01-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,7) = $line.logins
$SheetB.Cells.Item($intRow,8) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{ $SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,7) = $line.logins
$SheetC.Cells.Item($intRow,8) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,7) = $line.logins
$SheetE.Cells.Item($intRow,8) = $line.group_calls
$intRow++
}
}
"02-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,9) = $line.logins
$SheetB.Cells.Item($intRow,10) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,9) = $line.logins
$SheetC.Cells.Item($intRow,10) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,9) = $line.logins
$SheetE.Cells.Item($intRow,10) = $line.group_calls
$intRow++
}
}
"03-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,11) = $line.logins
$SheetB.Cells.Item($intRow,12) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,11) = $line.logins
$SheetC.Cells.Item($intRow,12) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,11) = $line.logins
$SheetE.Cells.Item($intRow,12) = $line.group_calls
$intRow++
}
}
"04-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,13) = $line.logins
$SheetB.Cells.Item($intRow,14) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,13) = $line.logins
$SheetC.Cells.Item($intRow,14) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,13) = $line.logins
$SheetE.Cells.Item($intRow,14) = $line.group_calls
$intRow++
}
}
"05-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,15) = $line.logins
$SheetB.Cells.Item($intRow,16) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,15) = $line.logins
$SheetC.Cells.Item($intRow,16) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,15) = $line.logins
$SheetE.Cells.Item($intRow,16) = $line.group_calls
$intRow++
}
}
"06-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,17) = $line.logins
$SheetB.Cells.Item($intRow,18) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,17) = $line.logins
$SheetC.Cells.Item($intRow,18) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,17) = $line.logins
$SheetE.Cells.Item($intRow,18) = $line.group_calls
$intRow++
}
}
"07-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,19) = $line.logins
$SheetB.Cells.Item($intRow,20) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,19) = $line.logins
$SheetC.Cells.Item($intRow,20) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,19) = $line.logins
$SheetE.Cells.Item($intRow,20) = $line.group_calls
$intRow++
}
}
"08-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,21) = $line.logins
$SheetB.Cells.Item($intRow,22) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,21) = $line.logins
$SheetC.Cells.Item($intRow,22) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,21) = $line.logins
$SheetE.Cells.Item($intRow,22) = $line.group_calls
$intRow++
}
}
"09-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,23) = $line.logins
$SheetB.Cells.Item($intRow,24) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,23) = $line.logins
$SheetC.Cells.Item($intRow,24) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,23) = $line.logins
$SheetE.Cells.Item($intRow,24) = $line.group_calls
$intRow++
}
}
"10-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,25) = $line.logins
$SheetB.Cells.Item($intRow,26) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,25) = $line.logins
$SheetC.Cells.Item($intRow,26) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,25) = $line.logins
$SheetE.Cells.Item($intRow,26) = $line.group_calls
$intRow++
}
}
"11-2010" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,27) = $line.logins
$SheetB.Cells.Item($intRow,28) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,27) = $line.logins
$SheetC.Cells.Item($intRow,28) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,27) = $line.logins
$SheetE.Cells.Item($intRow,28) = $line.group_calls
$intRow++
}
}
"12-2011" { $intRow = 5
foreach ($line in $dataB)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetB.Cells.Item($intRow,2) = $line.UniqueID
$SheetB.Cells.Item($intRow,29) = $line.logins
$SheetB.Cells.Item($intRow,30) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataC)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetC.Cells.Item($intRow,2) = $line.UniqueID
$SheetC.Cells.Item($intRow,29) = $line.logins
$SheetC.Cells.Item($intRow,30) = $line.group_calls
$intRow++
}
$intRow = 5
foreach ($line in $dataE)
{
#put the data into these cells and repeat down
#write-host $line.UniqueID $line.GroupName $line.agency $line.Division $line.logins $line.group_calls
$SheetE.Cells.Item($intRow,2) = $line.UniqueID
$SheetE.Cells.Item($intRow,29) = $line.logins
$SheetE.Cells.Item($intRow,30) = $line.group_calls
$intRow++
}
}
}
$SheetB.SaveAs($file1)
$Excel.Close()
#Email Section Part 1
#Setting Up My PSCRedentials
$secpasswd = ConvertTo-SecureString "password$" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential("tech", $secpasswd)
#Send email using send-mailmessage
send-mailmessage -to scotta@me.tv-from "Automated Monthly Report<ScottA@me.com>" -subject "Previous Month's Logins&GroupCalls For Systems(B,C,E)" -body "Please let me know if you have any questions. "
-attachments $file1 -smtpserver techjunkie.tv -Cc scotta@email.org-credential $mycreds
Let me know what you think.
Visit my site for other scripts for your use.
PowerShell Tips, SQL Examples, Learn Php
Scott your Tech From Miami
-
Proposed as answer by
www.techjunkie.tv
Sunday, January 02, 2011 1:11 AM
Other recent topics
