Adding a Password protect function when creating excel files from worksheets

[VBA_Newbie_28173]

I have a file with many tabs in it, each tab for a specific person. The data from tab to tab is confidential. I have the code below that takes each tab in the workbook and saves it as an individual file. I want to add password protection to each file upon creation so I can save all files on our intranet and know that each person can only access their file. I want the code to look at the tab name for the workbook currently being created, and access a list on a separate tab that lists what the password should be, and save the workbook with that password. Is this possible? As my screen name says, I am a newbie at VB, so any help would be appreciated. Thanks so much.

Option Explicit

Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
'save book in this folder
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xls"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub

[jmcilhinney]

Is this actually a VB.NET question? That looks rather like Excel VBA, so nothing to do with VB.NET. If that's the case, you should use the Report Post icon at the bottom of your post to send a message to the mods to ask them to move this thread to the appropriate forum. Please DO NOT double post.

[ChrisE]

I have a file with many tabs in it, each tab for a specific person. The data from tab to tab is confidential. I have the code below that takes each tab in the workbook and saves it as an individual file. I want to add password protection to each file upon creation so I can save all files on our intranet and know that each person can only access their file. I want the code to look at the tab name for the workbook currently being created, and access a list on a separate tab that lists what the password should be, and save the workbook with that password. Is this possible? As my screen name says, I am a newbie at VB, so any help would be appreciated. Thanks so much.

Option Explicit

Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Cells.Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
'save book in this folder
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xls"
.Close SaveChanges:=True
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub

Hi,

you have to add the "password" Part to your SaveAs...

this is Code to create a new ExcelFile

'VBA Code

Private Sub Command1_Click()
test
End Sub

Public Sub test()
    Dim FSyObjekt As Object, FObekt As Object
    Application.ScreenUpdating = False
    Set FSyObjekt = CreateObject("Scripting.FileSystemObject")
    Workbooks.Add
    ActiveWorkbook.SaveAs "C:\Test1.xls", password:="myPassword"
    ActiveWorkbook.Close
    Set FObekt = FSyObjekt.GetFile("C:\Test1.xls")
    FObekt.Attributes = 3
    Application.ScreenUpdating = True
End Sub

Private Sub Command2_Click()
Dim XL As Excel.Application
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Add
XL.ActiveWorkbook.SaveAs "C:\Test2.xls", password:="myPassword"
XL.Application.Quit
Set XL = Nothing
End Sub

regards
Chris

[VBA_Newbie_28173]

You are correct and I have sent a message to the mods. My apologies, and thanks for pointing it out.

[si_the_geek]

Welcome to VBForums

Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

[vbfbryce]

Would a possible alternative be to keep everything in one workbook, and have the users open it and supply a password so they would see only "their sheet?"

If so, try something like the attached.

The password to view the VBA code is abc.

The passwords to make sheets visible are 1 and 2.