One of my recent requirement I had a excel macro that basically does data refresh using database connection and finally saves the file to local drive. I was asked to automate the process of running the macro then pick the file that gets saved from local drive and finally upload it to the FTP server. script also contains logging as well.
Below is complete Power-Shell snippet.
function Get-TimeStamp {
$timeStamp = "[" + (Get-Date).ToShortDateString() + " " + ((Get-Date).ToShortTimeString()) + "]"
Return $timeStamp
}
Write-Output "`n" | Out-file E:\PowerShell\Logs\log.txt -append
Write-Output "$(Get-TimeStamp) *******************Starting the PowerShell Script*******************" | Out-file E:\PowerShell\Logs\log.txt -append
#ftp server configuration
$ftp = "ftp://abc/def/"
$user = "username"
$pass = "password"
#Input file
$FilePath="E:\PowerShell\S.xlsb"
#Target file
$targetFilePath = "E:\PowerShell\L.xlsb"
Remove-Item $targetFilePath
#Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
#Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false
#Stop displaying alerts
$objExcel.DisplayAlerts = $false
Write-Output "$(Get-TimeStamp) Process the source file....." | Out-file E:\PowerShell\Logs\log.txt -append
#Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
"Running the macro..."
#Run the macro
$objExcel.Run("MacroName")
#close the workbook
$WorkBook.close()
#quit the instance of excel
$objExcel.Quit()
Write-Output "$(Get-TimeStamp) Document copied to target drive" | Out-file E:\PowerShell\Logs\log.txt -append
#creating web commendlet object for uploading to FTP server
$webclient = New-Object System.Net.WebClient
#passing credentials to webclient object
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
#creating object for file and target file
$file = Get-Item -Path $targetFilePath
$uri = New-Object System.Uri($ftp+$file.Name)
Write-Output "$(Get-TimeStamp) Sleeping for 60 seconds..." | Out-file E:\PowerShell\Logs\log.txt -append
Start-Sleep 60
Write-Output "$(Get-TimeStamp) start uploading the file" | Out-file E:\PowerShell\Logs\log.txt -append
#uploading the file
$webclient.UploadFile($uri, $targetFilePath)
Write-Output "$(Get-TimeStamp) upload completed" | Out-file E:\PowerShell\Logs\log.txt -append
Write-Output "$(Get-TimeStamp) *******************Finishing PowerShell Script*******************" | Out-file E:\PowerShell\Logs\log.txt -append
