title: Script to open encrypted excel file, that has a dynamic filename ending in "mmddyyyy"
body: <p>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. </p>
  
  <p>Here is an example of the stripped down code:</p>
  
  <pre><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()
  </code></pre>
  
  <p>Powershell also displays a "fullyqualifiederrorid": InvokeMethodOnNull error message.</p>