Script to open encrypted, dynamic excel files

Question

Post reply

Script to open encrypted, dynamic excel files

Lrobinson 93181

Right there with Babe

Points: 791
More actions
April 30, 2013 at 9:04 am

#292770

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, but 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.
Any insight greatly appreciated!
Thanks,
Lorna

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

If you post your PowerShell code I can help you port it to VB.net which you can use inside a SSIS Script Task.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lrobinson 93181 Right there with Babe Points: 791 More actions · Answer 2

Hi, I actually have this code working well now, however, I would love to change it to vb and put it in a pkg.

$objExcel = new-object -comobject excel.application

$objExcel.Visible = $True

$objExcel.DisplayAlerts = $False

$MMDDYYYY=[DateTime]::Today.AddDays(-1).ToString("MMddyyyy")

$Workbook = $objExcel.Workbooks.Open("\\files\Reporting\Census\DailyCensus_$MMDDYYYY.xls",0,$False,1,"password")

$worksheet = $workbook.Worksheets.Item(1)

$sheet = $workbook.Sheets.Item(1) # Activate the first worksheet

[void]$sheet.Cells.Item(1,1).EntireRow.Delete() # Delete the first two rows

[void]$sheet.Cells.Item(2,2).EntireRow.Delete() # Delete the first two rows

$worksheet = $workbook.Worksheets.Item(2)

$sheet = $workbook.Sheets.Item(2) # Activate the second worksheet

[void]$sheet.Cells.Item(1,1).EntireRow.Delete() # Delete the first two rows

[void]$sheet.Cells.Item(2,2).EntireRow.Delete() # Delete the first two rows

$Workbook.Password = ""

$a = $Workbook.SaveAs("\\files\Reporting\Census2\DailyCensus_$MMDDYYYY.xls")

$objExcel.Quit()

$Workbook=$Null

$objExcel=$Null

[GC]::Collect()

Lowell SSC Guru Points: 323518 More actions · Answer 3

this looks right to me:

Imports Excel = Microsoft.Office.Interop.Excel

Private Sub ImportExcel(ByVal PR01filename As String)

Try

xlApp = New Excel.Application

xlApp.Visible = True

xlApp.DisplayAlerts = False

Dim MMDDYYYY As String = Today.AddDays(-1).ToString("MMddyyyy")

xlWorkBook = xlApp.Workbooks.Open("\\files\Reporting\Census\DailyCensus_" & MMDDYYYY & ".xls", 0, False, 1, "password")

xlWorkSheet = xlWorkBook.Worksheets(1) '--Not sure of the name! 1-based index, not zero

range = xlWorkSheet.UsedRange

xlWorkSheet.Cells.Item(1, 1).EntireRow.Delete() '# Delete the first two rows

xlWorkSheet.Cells.Item(2, 2).EntireRow.Delete() '# Delete the first two rows

xlWorkSheet = xlWorkBook.Worksheets(2)

xlWorkSheet.Cells.Item(1, 1).EntireRow.Delete() '# Delete the first two rows

xlWorkSheet.Cells.Item(2, 2).EntireRow.Delete() '# Delete the first two rows

xlWorkBook.Password = ""

xlWorkBook.SaveAs("\\files\Reporting\Census2\DailyCensus_" & MMDDYYYY & ".xls")

xlWorkBook.Close()

xlApp.Quit()

PostMessage(xlApp.Hwnd, WM_QUIT, 0, 0)

releaseObject(xlApp)

releaseObject(xlWorkBook)

releaseObject(xlWorkSheet)

--Damn yuu Excel, i WILL Kill you!

Dim proc As System.Diagnostics.Process

For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")

proc.Kill()

Next

Catch ex2 As Exception

Try

PostMessage(xlApp.Hwnd, WM_QUIT, 0, 0)

xlWorkBook.Close()

xlApp.Quit()

Catch ex As Exception

End Try

Try

releaseObject(xlApp)

releaseObject(xlWorkBook)

releaseObject(xlWorkSheet)

Catch ex As Exception

End Try

Finally

releaseObject(xlApp)

releaseObject(xlWorkBook)

releaseObject(xlWorkSheet)

End Try

End Sub

Private Sub releaseObject(ByVal obj As Object)

Try

System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

obj = Nothing

Catch ex As Exception

obj = Nothing

Finally

GC.Collect()

End Try

End Sub

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

Nice job Lowell.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lrobinson 93181 Right there with Babe Points: 791 More actions · Answer 5

HI, Thank you for your input and time!

I'm having alot of trouble (errors) getting it to run properly though. I had added my Powershell script to an "Execute Process Task" in my pkg, and that seems to work properly. Do you know of any reason it would be better to run the VBScript instead?

Thanks!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

Mainly because it is native. One less dependency.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato