Looking for a macro that will send an encrypted excel spreadsheet 1

[L34621]

I am looking for a way to automatically mail an Impromptu report saved as an excel spreadsheet that is password protected.

I already have a macro that that will open Impromptu, attach to the catalog, create an excel spreadsheet and then e-mail it. But now I need to have the excel spreadsheet mailed with password protection (or encrypted).

I have Office 2007 and Cognos Impromptu Admin 7.3

Any help you can give is much appreciated! thanks!

 

[drlex]

L34621,
I'm using Impromptu 7.4 with Office 2003, and this code may be of assistance, although I understand that Office 2007 is a little more wary of being automated by another application, so you may need to check settings/permissions.
This will open a pre-existing spreadsheet 'test.xls' and apply two passwords - an opening password and a write password.

Sub Main
   Dim strfilename as string
   Dim objExcel as Object
   strfilename = "C:\test.xls"
   Set objExcel = CreateObject("Excel.Application")
   objExcel.Visible = 1 'To assist debugging. Not necessary in final version
   objExcel.Application.Workbooks.Open strfilename
   objExcel.Application.DisplayAlerts = False   'To prevent any Y/N prompt windows from interrupting the flow
   objExcel.Application.Workbooks(1).SaveAs strfilename, Fileformat:=-4143, Password:="OpenPassword", WriteResPassword:="WritePassword", ReadOnlyRecommended:=False, CreateBackup:=False
   objExcel.Application.Workbooks(1).Close
   objExcel.Application.Quit
End Sub

(The fileformat = -4143 is to specify the Excel format, the prior DisplayAlerts=False should prevent the overwrite warning).

Lex

soi la, soi carré

 

[L34621]

Thanks so much! great info! and it's much appreciated!

You are a rock star!!!