macro to input password when saving doc in excel
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?. |
macro to input password when saving doc in excel
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?. |
macro to input password when saving doc in excel
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?. |
All times are GMT +1. The time now is 09:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com