Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Password protect workbook 1

Status
Not open for further replies.

columbo1977

Programmer
May 9, 2006
49
GB
Hi All

I have code that copies selected sheets to a new workbook then emails that file to a specific address. I want to them password protect that workbook. but I cnnot specify the locaion it will be saved as it is attached to an email? I just need it saving with a password in the email for security purposes. please see below for the code and what I am trying to do.

Code:
Sub Send1Sheet_ActiveWorkbook()

    'ActiveSheet.Copy
    Sheets(Array("1", "2", "3", "4", "5", "6")).Copy
    
    For Each Worksheet In Sheets(Array("1", "2", "3", "4", "5", "6"))
        Worksheet.Protect Password:="ORANGE"
    Next Worksheet
    
    ActiveWorkbook.SaveAs Filename:=LocalFileName, Password:="12"
    
    
   
       
    'ActiveWorkbook.SaveAs Filename:="test.xls", FileFormat:= _
    'xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
    'True, CreateBackup:=False
    Workbook.Protect Password:="test"
    
    
    With ActiveWorkbook
         .SendMail Recipients:="XXX@XXX.uk", _
          Subject:="Test"
         .Close SaveChanges:=False
    End With
    
End Sub
 
Save it too C:Temp first, do what you have to, and then email it.

Never knock on Death's door: ring the bell and run away! Death really hates that!
 
The code is in a spreadsheet that is used by a team of 23 and it is all accessed by a button to make it easy so I need this to happen in the VBA code automattically.

Columbo
 
Yes the first action of your program saves a copy of the file to the c:temp, then do as you will.

Never knock on Death's door: ring the bell and run away! Death really hates that!
 
It comes up with an error? not sure what path it saves into but these are work computers and so are very restricted?

code is below.

Sub Send1Sheet_ActiveWorkbook()
'Create a new Workbook from the LPS sheet and then emails _
it to the address below

'ActiveSheet.Copy
Sheets(Array("CM400-1", "00-2", "00-3", "00-4", "00-5", "00-6")).Copy

For Each Worksheet In Sheets(Array("00-1", "00-2", "00-3", "00-4", "00-5", "00-6"))
Worksheet.Protect Password:="ORANGE"
Next Worksheet

ActiveWorkbook.SaveAs Filename:="c:\temp\test.xls", FileFormat:= _
xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
True, CreateBackup:=False


With ActiveWorkbook
.SendMail Recipients:="x@x.com", _
Subject:="Test"
.Close SaveChanges:=False
End With

End Sub

Thanks
 
It comes up with an error?

???
Where does it break?
What is the error?

Never knock on Death's door: ring the bell and run away! Death really hates that!
 
Error 1004 cannot access c:\temp

it errors when saving

ActiveWorkbook.SaveAs Filename:="c:\temp\test.xls", FileFormat:= _
xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
True, CreateBackup:=False

They are restricted workstations
 
Does the workstation have a c:\temp folder ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
No that is what i was saying before I dont know where it saves, and I cannot really specify a folder as too many people will be using the sheet.

That is why I wanted something that password protects the document when created so when it is attached to the email it is secure ( as can be)

Thanks
 
try this
Code:
dim sTemp as string
sTemp = Environ("TEMP")

ActiveWorkbook.SaveAs Filename:= sTemp & "\test.xls", FileFormat:= _
    xlNormal, Password:="1234", WriteResPassword:="x", ReadOnlyRecommended:= _
    True, CreateBackup:=False

unless your user setup's is completely messed up they should have access to their TEMP directory. This may differ from user to user, so using the above code will retrieve the correct pathname regardless of the user.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

FAQ219-2884
FAQ181-2886
 
That is excellent works like a dream. Thanks for that.

If I wanted to send the entire workbook and not selective sheets what would I put in

ActiveWorkbook.copy

doesnt seem to work?

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top