问题描述
您好,我的 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屋!
更多推荐
[db:关键词]
发布评论