打开加密的 excel 文件的脚本，该文件具有以“mmddyyyy"结尾的动态文件名； [英] Script to open encrypted excel file, that has a dynamic filename ending in "mmddyyyy"

问题描述

您好，我的 SSIS 包需要一个脚本来打开我加密的 excel 文件，该文件的动态文件名以mmddyyyy"结尾.我有一个 powershell 脚本，它将使用文件名打开和重置密码，但我不知道如何在那里使用动态文件名.另外，我想我更喜欢一个可以打开文件并删除密码的 vb 脚本(或者也许不是只要我可以在打开时提取数据?)因为我可能会将它设置为 SQL 作业来提取并每天发送到 sql.

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("\filesReportingCensusDaily_$MMDDYYYY.xls",0,$False,‌​1,"password")
$Workbook.Password = ""
$strPath="\filesReportingCensusDaily_$MMDDYYYY.xls" 
$a.activeworkbook.SaveAs($strPath) 
$objExcel.Quit() 
$Workbook=$Null 
$objExcel=$Null 
[GC]::Collect()

Powershell 还会显示fullyqualifiederrorid":InvokeMethodOnNull 错误消息.

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

推荐答案

你可以设置一个带有当前日期的变量 - 1 像这样:

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

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

更多Powershell-y"，使用 Get-Date 而不是 [DateTime]::Today 对象.

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

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

这篇关于打开加密的 excel 文件的脚本，该文件具有以“mmddyyyy"结尾的动态文件名；的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！