Script to open encrypted excel file, that has a dy

Question

Hi I need a script for my SSIS package that will open my encrypted excel file, that has a dynamic filename ending in "mmddyyyy". I have a powershell script that will open and reset the password using the filename, but I do not know how to use a dynamic filename there. Also, I think I would prefer a vb script that will open the file and remove the password (or maybe not as long as I can extract the data while it is open?) as I will likely set it up as a SQL job to extract and send to sql daily.

Here is an example of the stripped down code:

objExcel = new-object -comobject excel.application 
$objExcel.Visible = $True 
$objExcel.DisplayAlerts = $False 
$MMDDYYYY=[DateTime]::Today.AddDays(-1).ToString("MMddyyyy")    
$objExcel.Workbooks.Open("\\files\Reporting\Census\Daily_$MMDDYYYY.xls",0,$False,‌​1,"password")
$Workbook.Password = ""
$strPath="\\\files\Reporting\Census\Daily_$MMDDYYYY.xls" 
$a.activeworkbook.SaveAs($strPath) 
$objExcel.Quit() 
$Workbook=$Null 
$objExcel=$Null 
[GC]::Collect()

Powershell also displays a "fullyqualifiederrorid": InvokeMethodOnNull error message.

Answer 1

You can set a variable with the current date - 1 like this:

$MMddyyyy=[DateTime]::Today.AddDays(-1).ToString("MMddyyyy")
$objExcel.Workbooks.Open("\\files\SHC\Daily_$MMddyyyy.xls",0,$False,1,"password")

More "Powershell-y", using Get-Date instead of the [DateTime]::Today object.

$MMddyyyy=(Get-Date).AddDays(-1).ToString("MMddyyyy")
$objExcel.Workbooks.Open("\\files\SHC\Daily_$MMddyyyy.xls",0,$False,1,"password")