Password Protection in Excel

[vc333]

Hi all, I'm new to this board so apologies if this is not clear.

I have a macro that runs reports that are selected from a drop down filter. It then attaches itself to an email. However, I need to put a password on each report that is sent out. How can I go into the Visual Basic Editor and edit this so that it adds a generic password to each report?

 

[Trevor G]

Welcome to the Forum,

What code do you have that generates the reports, (you can use Alt + F11 on the keyboard to get into VBA) and what format are you using to send them out, ie are they Excel Workbooks?

 

[vc333]

Hi trevor, thank you.

The code that saves the report is as follows:

Sub SaveReport(StrName As String, wkbreport As Workbook)
Dim StrFile As String
Dim strPath As String
Dim strdate As String
Dim rgLkp As Range
Set rgLkp = Sheets("Report Save").Range("a1").CurrentRegion
StrFile = Application.VLookup(StrName, rgLkp, 3, 0)
strPath = Application.VLookup(StrName, rgLkp, 2, 0)
If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
Select Case Application.VLookup(StrName, rgLkp, 4, 0)
Case "D": strdate = " " & Format(Now(), "yyyymmdd")
Case "DD MMM YYYY": strdate = " " & Format(Now(), "DD MMM YYYY")

Case "M"
If StrName = "OPS (ByAreas)" Then
strdate = " " & Format(Now(), "mmmm yyyy")
Else
strdate = " " & Format(DateAdd("m", (Day(Now()) < 12) * 1, Now()), "mmmm yyyy")
End If

Case Else: strdate = ""
End Select

If StrName = "Group Risk" Then
wkbreport.SaveAs strPath & StrFile & strdate & "r.xls"
wkbreport.Sheets(1).Columns("o:q").Delete
wkbreport.SaveAs strPath & StrFile & strdate & "n.xls"
Workbooks.Open strPath & StrFile & strdate & "r.xls"
ThisWorkbook.Activate

ElseIf StrName = "Non Core" Then
wkbreport.SaveAs strPath & StrFile & strdate & ".xls", FileFormat:=xlNormal, Password:="PassWord"

Else
wkbreport.SaveAs strPath & StrFile & strdate & ".xls"
End If
End Sub

Can I stick the code in here somewhere?

 

[Trevor G]

In the if statement below you can use the same part of the code to set the password.

If StrName = "Group Risk" Then
wkbreport.SaveAs strPath & StrFile & strdate & "r.xls"
wkbreport.Sheets(1).Columns("o:q").Delete
wkbreport.SaveAs strPath & StrFile & strdate & "n.xls"
Workbooks.Open strPath & StrFile & strdate & "r.xls"
ThisWorkbook.Activate

ElseIf StrName = "Non Core" Then
wkbreport.SaveAs strPath & StrFile & strdate & ".xls", FileFormat:=xlNormal, Password:="PassWord"

Else
wkbreport.SaveAs strPath & StrFile & strdate & ".xls"
End If

would be something like this

If StrName = "Group Risk" Then
wkbreport.SaveAs strPath & StrFile & strdate & "r.xls"", FileFormat:=xlNormal, Password:="PassWord"
wkbreport.Sheets(1).Columns("o:q").Delete
wkbreport.SaveAs strPath & StrFile & strdate & "n.xls"", FileFormat:=xlNormal, Password:="PassWord"
Workbooks.Open strPath & StrFile & strdate & "r.xls"
ThisWorkbook.Activate

ElseIf StrName = "Non Core" Then
wkbreport.SaveAs strPath & StrFile & strdate & ".xls", FileFormat:=xlNormal, Password:="PassWord"

Else
wkbreport.SaveAs strPath & StrFile & strdate & ".xls"", FileFormat:=xlNormal, Password:="PassWord"
End If

 

[vc333]

Thanks for this. It seems to have worked to an extent but when I run the Macro it comes with a debugger "Run time error 1004, workbook is protected and cannot be changed". Can you shed any light as to why this happening please?

 

[vc333]

Hi Trevor, sorry I have run the macro again and it works perfectly! Thank you so much for taking time out to help me.

 

[Trevor G]

Please to read you have a solution.