Styjun

How can I safely determine the output voltage and current of a transformer?

How does this piece of code determine array size without using sizeof( )?

multiline equation inside a matrix that is a part of multiline equation

Does a non-singular matrix have a large minor with disjoint rows and columns and full rank?

Polynomial division: Is this trick obvious?

Why does string strummed with finger sound different from the one strummed with pick?

Would a "ring language" be possible?

Why did the soldiers of the North disobey Jon?

Why aren't satellites disintegrated even though they orbit earth within their Roche Limits?

Is there an academic word that means "to split hairs over"?

​Cuban​ ​Primes

How does the Heat Metal spell interact with a follow-up Frostbite spell?

Who is frowning in the sentence "Daisy looked at Tom frowning"?

What kind of environment would favor hermaphroditism in a sentient species over regular, old sexes?

Why use a retrograde orbit?

How could it be that 80% of townspeople were farmers during the Edo period in Japan?

Why is it correct to use ～た in this sentence, even though we're talking about next week?

Is there any deeper thematic meaning to the white horse that Arya finds in The Bells (S08E05)?

What would a Dragon have to exhale to cause rain?

Promotion comes with unexpected 24/7/365 on-call

How can we delete item permanently without storing in Recycle Bin?

Why does the U.S military use mercenaries?

Why is so much ransomware breakable?

How to handle professionally if colleagues has referred his relative and asking to take easy while taking interview

Script to open encrypted excel file, that has a dynamic filename ending in “mmddyyyy”

open a password protected Excel in powershellPowershell Excel Automation Save Workbook by DateHow to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?Parse MS Excel files dynamically with SSISPowershell extract CPU, Memory and Free Disc space from serversHow to protect or open protected excel file using SSISAutorun Excel with VBA script, and auto insert a file based on a date maskExcecuting a Powershell script within a SSIS packageUnprotecting VBA in XLS 2010 via PowershellPowerShell File Path for ReadyshareSSIS with PowerShell script to refresh Excel connections?Running VBA macro in Excel - Error During Saving File

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

0

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 also displays a "fullyqualifiederrorid": InvokeMethodOnNull error message.

excel powershell vbscript ssis encryption

share|improve this question

edited May 1 '13 at 14:53

N West

6,1542037

asked Apr 30 '13 at 19:16

Lorna RobinsonLorna Robinson

36123

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is your specific question? Powershell allows the use of arguments, so you could pass the name into the script... What code do you have so far?
  
  – N West
  Apr 30 '13 at 19:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  My powershell script needs to open a file daily , change the password, delete some rows, and then save with no password. I can do everything except open a dynamic filename with the mmddyyyy ext at the end of each file. REPLACE: $Workbook = $objExcel.Workbooks.Open("\filesSHCDaily_04262013.xls",0,$False,1,"password") with code to open ("\filesSHCDaily_mmddyyyy.xls",0,$False,1,"password") where mmddyyyy is always "yesterday".
  
  – Lorna Robinson
  Apr 30 '13 at 20:24
  
  

  
  
  
  

add a comment | 

0

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 also displays a "fullyqualifiederrorid": InvokeMethodOnNull error message.

excel powershell vbscript ssis encryption

share|improve this question

edited May 1 '13 at 14:53

N West

6,1542037

asked Apr 30 '13 at 19:16

Lorna RobinsonLorna Robinson

36123

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  What is your specific question? Powershell allows the use of arguments, so you could pass the name into the script... What code do you have so far?
  
  – N West
  Apr 30 '13 at 19:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  My powershell script needs to open a file daily , change the password, delete some rows, and then save with no password. I can do everything except open a dynamic filename with the mmddyyyy ext at the end of each file. REPLACE: $Workbook = $objExcel.Workbooks.Open("\filesSHCDaily_04262013.xls",0,$False,1,"password") with code to open ("\filesSHCDaily_mmddyyyy.xls",0,$False,1,"password") where mmddyyyy is always "yesterday".
  
  – Lorna Robinson
  Apr 30 '13 at 20:24
  
  

  
  
  
  

add a comment | 

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 also displays a "fullyqualifiederrorid": InvokeMethodOnNull error message.

excel powershell vbscript ssis encryption

share|improve this question

edited May 1 '13 at 14:53

N West

6,1542037

asked Apr 30 '13 at 19:16

Lorna RobinsonLorna Robinson

36123

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()

share|improve this question

edited May 1 '13 at 14:53

N West

6,1542037

asked Apr 30 '13 at 19:16

Lorna RobinsonLorna Robinson

36123

share|improve this question

edited May 1 '13 at 14:53

N West

6,1542037

asked Apr 30 '13 at 19:16

Lorna RobinsonLorna Robinson

36123

edited May 1 '13 at 14:53

N West

6,1542037

edited May 1 '13 at 14:53

N West

6,1542037

asked Apr 30 '13 at 19:16

Lorna RobinsonLorna Robinson

36123

asked Apr 30 '13 at 19:16

Lorna RobinsonLorna Robinson

36123

2 Answers
2

active

oldest

votes

2

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")

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")

share|improve this answer

edited May 1 '13 at 13:17

answered Apr 30 '13 at 21:02

N WestN West

6,1542037

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +1 (Get-Date) would be more PoSh than [DateTime]::Today, though.
  
  – Ansgar Wiechers
  Apr 30 '13 at 21:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi, Thank you! It works fine for opening the file, but won't save: $a = $Workbook.SaveAs("\filesSHCDaily_$YYYYMMDD.xls") it presents a RuntimeException???
  
  – Lorna Robinson
  May 1 '13 at 13:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @AnsgarWiechers Added a Get_date version.
  
  – N West
  May 1 '13 at 13:17
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @AnsgarWiechers Looks like you already answered this too in 2012! stackoverflow.com/questions/12272960/…
  
  – N West
  May 1 '13 at 13:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @LornaRobinson Does your userid that you run this with have rights to the network UNC path? Sometimes powershell has issues with permissions to network drives...
  
  – N West
  May 1 '13 at 13:19
  

  
  
  
  

 | 
show 3 more comments

0

I was facing a problem in which I needed to open an Excel password-protected file. However even following the answers in this post and the duplicate (open a password protected Excel in powershell), it still prompted me for a password.
I solved by adding the 6th parameter WriteResPassword. My final code was:

$xl = New-Object -comobject Excel.Application
$xl.DisplayAlerts=$False
$wb=$xl.Workbooks.open("$myFile",3,$false,5,"$mypassword","$mypassword")

Hope this helps anyone else who struggles with password protected files in Powershell.

share|improve this answer

answered Mar 23 at 16:16

Fabio Mendes SoaresFabio Mendes Soares

163

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f16307345%2fscript-to-open-encrypted-excel-file-that-has-a-dynamic-filename-ending-in-mmdd%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

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")

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")

share|improve this answer

edited May 1 '13 at 13:17

answered Apr 30 '13 at 21:02

N WestN West

6,1542037

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +1 (Get-Date) would be more PoSh than [DateTime]::Today, though.
  
  – Ansgar Wiechers
  Apr 30 '13 at 21:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi, Thank you! It works fine for opening the file, but won't save: $a = $Workbook.SaveAs("\filesSHCDaily_$YYYYMMDD.xls") it presents a RuntimeException???
  
  – Lorna Robinson
  May 1 '13 at 13:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @AnsgarWiechers Added a Get_date version.
  
  – N West
  May 1 '13 at 13:17
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @AnsgarWiechers Looks like you already answered this too in 2012! stackoverflow.com/questions/12272960/…
  
  – N West
  May 1 '13 at 13:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @LornaRobinson Does your userid that you run this with have rights to the network UNC path? Sometimes powershell has issues with permissions to network drives...
  
  – N West
  May 1 '13 at 13:19
  

  
  
  
  

 | 
show 3 more comments

2

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")

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")

share|improve this answer

edited May 1 '13 at 13:17

answered Apr 30 '13 at 21:02

N WestN West

6,1542037

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  +1 (Get-Date) would be more PoSh than [DateTime]::Today, though.
  
  – Ansgar Wiechers
  Apr 30 '13 at 21:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hi, Thank you! It works fine for opening the file, but won't save: $a = $Workbook.SaveAs("\filesSHCDaily_$YYYYMMDD.xls") it presents a RuntimeException???
  
  – Lorna Robinson
  May 1 '13 at 13:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @AnsgarWiechers Added a Get_date version.
  
  – N West
  May 1 '13 at 13:17
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @AnsgarWiechers Looks like you already answered this too in 2012! stackoverflow.com/questions/12272960/…
  
  – N West
  May 1 '13 at 13:18
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @LornaRobinson Does your userid that you run this with have rights to the network UNC path? Sometimes powershell has issues with permissions to network drives...
  
  – N West
  May 1 '13 at 13:19
  

  
  
  
  

 | 
show 3 more comments

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")

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")

share|improve this answer

edited May 1 '13 at 13:17

answered Apr 30 '13 at 21:02

N WestN West

6,1542037

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

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

share|improve this answer

edited May 1 '13 at 13:17

answered Apr 30 '13 at 21:02

N WestN West

6,1542037

answered Apr 30 '13 at 21:02

N WestN West

6,1542037

answered Apr 30 '13 at 21:02

N WestN West

6,1542037

0

I was facing a problem in which I needed to open an Excel password-protected file. However even following the answers in this post and the duplicate (open a password protected Excel in powershell), it still prompted me for a password.
I solved by adding the 6th parameter WriteResPassword. My final code was:

$xl = New-Object -comobject Excel.Application
$xl.DisplayAlerts=$False
$wb=$xl.Workbooks.open("$myFile",3,$false,5,"$mypassword","$mypassword")

Hope this helps anyone else who struggles with password protected files in Powershell.

share|improve this answer

answered Mar 23 at 16:16

Fabio Mendes SoaresFabio Mendes Soares

163

add a comment | 

0

I was facing a problem in which I needed to open an Excel password-protected file. However even following the answers in this post and the duplicate (open a password protected Excel in powershell), it still prompted me for a password.
I solved by adding the 6th parameter WriteResPassword. My final code was:

$xl = New-Object -comobject Excel.Application
$xl.DisplayAlerts=$False
$wb=$xl.Workbooks.open("$myFile",3,$false,5,"$mypassword","$mypassword")

Hope this helps anyone else who struggles with password protected files in Powershell.

share|improve this answer

answered Mar 23 at 16:16

Fabio Mendes SoaresFabio Mendes Soares

163

add a comment | 

I was facing a problem in which I needed to open an Excel password-protected file. However even following the answers in this post and the duplicate (open a password protected Excel in powershell), it still prompted me for a password.
I solved by adding the 6th parameter WriteResPassword. My final code was:

$xl = New-Object -comobject Excel.Application
$xl.DisplayAlerts=$False
$wb=$xl.Workbooks.open("$myFile",3,$false,5,"$mypassword","$mypassword")

Hope this helps anyone else who struggles with password protected files in Powershell.

share|improve this answer

answered Mar 23 at 16:16

Fabio Mendes SoaresFabio Mendes Soares

163

$xl = New-Object -comobject Excel.Application
$xl.DisplayAlerts=$False
$wb=$xl.Workbooks.open("$myFile",3,$false,5,"$mypassword","$mypassword")

share|improve this answer

answered Mar 23 at 16:16

Fabio Mendes SoaresFabio Mendes Soares

163

answered Mar 23 at 16:16

Fabio Mendes SoaresFabio Mendes Soares

163

answered Mar 23 at 16:16

Fabio Mendes SoaresFabio Mendes Soares

163