Hi, every day I create a large number of sensitive excel spreadsheets that
need to be saved with a password that changes every day.

I have tried recording a macro (I am not a programmer) to do it and I have
succeeded to some degree in that it copies the data from a specific cell and
inserts it into the password section when saving the document, the only
problem is that it takes the data that was originally in the cell ie the data
that was in the cell when the macro was created, not the new data that I
would input each day into the relevant cell

Does anyone out there know how I can do this?.

Sub Saveit_Pword()
Dim pword As String
pword = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Name, FileFormat:=xlNormal _
, Password:=pword, WriteResPassword:=pword, ReadOnlyRecommended:= _
False, CreateBackup:=True
Application.DisplayAlerts = True
End Sub

Will save to the default folder with a password you enter in Sheet1 A1


Gord Dibben MS Excel MVP

On Wed, 29 Oct 2008 08:28:00 -0700, Nissassa
wrote:

Hi, every day I create a large number of sensitive excel spreadsheets that
need to be saved with a password that changes every day.

I have tried recording a macro (I am not a programmer) to do it and I have
succeeded to some degree in that it copies the data from a specific cell and
inserts it into the password section when saving the document, the only
problem is that it takes the data that was originally in the cell ie the data
that was in the cell when the macro was created, not the new data that I
would input each day into the relevant cell

Does anyone out there know how I can do this?.

Note: as written, if A1 is empty, the pword will be blank so will not be
required upon opening.

Make this change

pword = Sheets("Sheet1").Range("A1").Value
If pword = "" Then Exit Sub 'add this line here
Application.DisplayAlerts = False


Gord


On Wed, 29 Oct 2008 13:10:12 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Sub Saveit_Pword()
Dim pword As String
pword = ActiveWorkbook.Sheets("Sheet1").Range("A1").Value
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Name, FileFormat:=xlNormal _
, Password:=pword, WriteResPassword:=pword, ReadOnlyRecommended:= _
False, CreateBackup:=True
Application.DisplayAlerts = True
End Sub

Will save to the default folder with a password you enter in Sheet1 A1


Gord Dibben MS Excel MVP

On Wed, 29 Oct 2008 08:28:00 -0700, Nissassa
wrote:

Hi, every day I create a large number of sensitive excel spreadsheets that
need to be saved with a password that changes every day.

I have tried recording a macro (I am not a programmer) to do it and I have
succeeded to some degree in that it copies the data from a specific cell and
inserts it into the password section when saving the document, the only
problem is that it takes the data that was originally in the cell ie the data
that was in the cell when the macro was created, not the new data that I
would input each day into the relevant cell

Does anyone out there know how I can do this?.