If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Changing dsn connection information (Password)
My company has just implemented expiring passwords. Some
of my users have created Excell workbooks containing multiple worksheets, each retrieving data from an external database. This means that when the password expires the user will have to change the password in each worksheet. (In some cases 20 sheets to a book) It appears that you should just be able to update the data source object (dsn) and all worksheets using it should be updated. It doesn't seem to work that way. Once the Workbook is saved, even if you delete the Data Source it still connects using the old connection information. I need to find a simpler way for my users to update their queries when their passwords expire. |
#2
|
|||
|
|||
Changing dsn connection information (Password)
Maybe this will help, I have created a macro which will prompt the user for one password which works for all the sheets or individually. Here is the code I wrote, I hope it helps: Sub Protect_All_Sheets() ' Protect_All_Sheets Macro ' Macro recorded 3/3/2001 by David Allen Marden ' 'This Macto Can Be Used In Any Excel Project Dim CurrentSheetName As String Dim Password As String Dim CheckPassword As String Dim Decision As String CurrentSheetName = ActiveSheet.Name Sheets(1).Select 'Check if sheet is protected. j = 1 If ActiveSheet.ProtectContents = False Then Do While ActiveSheet.ProtectContents = False 'If not, get a password Password = InputBox("Enter a password for this sheet") CheckPassword = InputBox("Re Enter Password for this sheet") If CheckPassword = Password Then ProtectNext = True If j = 1 Then Sheet1ProtectionChanges End If If j = 2 Then Sheet2ProtectionChanges End If If j = 3 Then Sheet3ProtectionChanges End If If j = 4 Then Sheet4ProtectionChanges End If ProtectNext = False Range("Sheet" & j & "Protection").Value = "True" j = j + 1 'ActiveSheet.Protect (Password) ActiveSheet.Protect (Password), DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowSorting:=True Else: MsgBox ("Passwords did not Match") End If Loop End If Decision = InputBox("Typing G will copy last password to all other sheets, Typing I will individualize all sheets, If you type anything else then the first page protected is the only page that will get protected.") If Decision = "i" Or Decision = "I" Then For i = 1 To Sheets.Count - 1 ActiveSheet.Next.Select 'Check if sheet is protected. If ActiveSheet.ProtectContents = False Then Do While ActiveSheet.ProtectContents = False 'If not, get a password Password = InputBox("Enter a password for this sheet") CheckPassword = InputBox("Re Enter Password for this sheet") If CheckPassword = Password Then 'Set Password ProtectNext = True If j = 1 Then Sheet1ProtectionChanges End If If j = 2 Then Sheet2ProtectionChanges End If If j = 3 Then Sheet3ProtectionChanges End If If j = 4 Then Sheet4ProtectionChanges End If ProtectNext = False Range("Sheet" & j & "Protection").Value = "True" j = j + 1 'ActiveSheet.Protect (Password) ActiveSheet.Protect (Password), DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowSorting:=True Else: MsgBox ("Passwords did not Match") End If Loop End If Next End If If Decision = "g" Or Decision = "G" Then For i = 1 To Sheets.Count - 1 ActiveSheet.Next.Select If ActiveSheet.ProtectContents = False Then ProtectNext = True If j = 1 Then Sheet1ProtectionChanges End If If j = 2 Then Sheet2ProtectionChanges End If If j = 3 Then Sheet3ProtectionChanges End If If j = 4 Then Sheet4ProtectionChanges End If ProtectNext = False Range("Sheet" & j & "Protection").Value = "True" j = j + 1 'ActiveSheet.Protect (Password) ActiveSheet.Protect (Password), DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowSorting:=True End If Next End If Sheets(CurrentSheetName).Select Range("D4").Select End Sub Sub Unprotect_All_Sheets() ' Unprotect_All_Sheets Macro ' Macro recorded 3/3/2001 by David Allen Marden ' ' This Macto Can Be Used In Any Excel Project Dim CurrentSheetName As String Dim Password As String Dim CheckPassword As String Dim Decision As String CurrentSheetName = ActiveSheet.Name Sheets(1).Select 'Check if sheet is protected. If ActiveSheet.ProtectContents = True Then Do While ActiveSheet.ProtectContents = True 'If not, get a password Password = InputBox("Enter the password for this sheet") 'Reset Password ActiveSheet.Unprotect (Password) Loop End If Decision = InputBox("Typing G will use the last password to unprotect all other sheets, Typing I will individualize all sheets, If you type anything else then the first page protected is the only page that will get unprotected.") If Decision = "i" Or Decision = "I" Then For i = 1 To Sheets.Count - 1 ActiveSheet.Next.Select 'Check if sheet is protected. If ActiveSheet.ProtectContents = True Then Do While ActiveSheet.ProtectContents = True 'If not, get a password Password = InputBox("Enter a password for this sheet") 'Reset Password ActiveSheet.Unprotect (Password) Loop End If Next End If If Decision = "g" Or Decision = "G" Then For i = 1 To Sheets.Count - 1 ActiveSheet.Next.Select If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect (Password) End If Next End If Sheets(1).Select For i = 1 To 4 Sheets(1).Select If ActiveSheet.ProtectContents = False Then ProtectNext = False Range("Sheet" & i & "Protection").Value = "False" Else: Range("Sheet" & i & "Protection").Value = "True" End If If i = 1 Then Sheet1ProtectionChanges End If If i = 2 Then Sheet2ProtectionChanges End If If i = 3 Then Sheet3ProtectionChanges End If If i = 4 Then Sheet4ProtectionChanges End If If i 4 Then ActiveSheet.Next.Select End If Next Sheets(CurrentSheetName).Select Range("A1").Select End Sub |
#3
|
|||
|
|||
Changing dsn connection information (Password)
Thanks for your response, I will give this a try.
-----Original Message----- Maybe this will help, I have created a macro which will prompt the user for one password which works for all the sheets or individually. Here is the code I wrote, I hope it helps: Sub Protect_All_Sheets() ' Protect_All_Sheets Macro ' Macro recorded 3/3/2001 by David Allen Marden ' 'This Macto Can Be Used In Any Excel Project Dim CurrentSheetName As String Dim Password As String Dim CheckPassword As String Dim Decision As String CurrentSheetName = ActiveSheet.Name Sheets(1).Select 'Check if sheet is protected. j = 1 If ActiveSheet.ProtectContents = False Then Do While ActiveSheet.ProtectContents = False 'If not, get a password Password = InputBox("Enter a password for this sheet") CheckPassword = InputBox("Re Enter Password for this sheet") If CheckPassword = Password Then ProtectNext = True If j = 1 Then Sheet1ProtectionChanges End If If j = 2 Then Sheet2ProtectionChanges End If If j = 3 Then Sheet3ProtectionChanges End If If j = 4 Then Sheet4ProtectionChanges End If ProtectNext = False Range("Sheet" & j & "Protection").Value = "True" j = j + 1 'ActiveSheet.Protect (Password) ActiveSheet.Protect (Password), DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowSorting:=True Else: MsgBox ("Passwords did not Match") End If Loop End If Decision = InputBox("Typing G will copy last password to all other sheets, Typing I will individualize all sheets, If you type anything else then the first page protected is the only page that will get protected.") If Decision = "i" Or Decision = "I" Then For i = 1 To Sheets.Count - 1 ActiveSheet.Next.Select 'Check if sheet is protected. If ActiveSheet.ProtectContents = False Then Do While ActiveSheet.ProtectContents = False 'If not, get a password Password = InputBox("Enter a password for this sheet") CheckPassword = InputBox("Re Enter Password for this sheet") If CheckPassword = Password Then 'Set Password ProtectNext = True If j = 1 Then Sheet1ProtectionChanges End If If j = 2 Then Sheet2ProtectionChanges End If If j = 3 Then Sheet3ProtectionChanges End If If j = 4 Then Sheet4ProtectionChanges End If ProtectNext = False Range("Sheet" & j & "Protection").Value = "True" j = j + 1 'ActiveSheet.Protect (Password) ActiveSheet.Protect (Password), DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowSorting:=True Else: MsgBox ("Passwords did not Match") End If Loop End If Next End If If Decision = "g" Or Decision = "G" Then For i = 1 To Sheets.Count - 1 ActiveSheet.Next.Select If ActiveSheet.ProtectContents = False Then ProtectNext = True If j = 1 Then Sheet1ProtectionChanges End If If j = 2 Then Sheet2ProtectionChanges End If If j = 3 Then Sheet3ProtectionChanges End If If j = 4 Then Sheet4ProtectionChanges End If ProtectNext = False Range("Sheet" & j & "Protection").Value = "True" j = j + 1 'ActiveSheet.Protect (Password) ActiveSheet.Protect (Password), DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowSorting:=True End If Next End If Sheets(CurrentSheetName).Select Range("D4").Select End Sub Sub Unprotect_All_Sheets() ' Unprotect_All_Sheets Macro ' Macro recorded 3/3/2001 by David Allen Marden ' ' This Macto Can Be Used In Any Excel Project Dim CurrentSheetName As String Dim Password As String Dim CheckPassword As String Dim Decision As String CurrentSheetName = ActiveSheet.Name Sheets(1).Select 'Check if sheet is protected. If ActiveSheet.ProtectContents = True Then Do While ActiveSheet.ProtectContents = True 'If not, get a password Password = InputBox("Enter the password for this sheet") 'Reset Password ActiveSheet.Unprotect (Password) Loop End If Decision = InputBox("Typing G will use the last password to unprotect all other sheets, Typing I will individualize all sheets, If you type anything else then the first page protected is the only page that will get unprotected.") If Decision = "i" Or Decision = "I" Then For i = 1 To Sheets.Count - 1 ActiveSheet.Next.Select 'Check if sheet is protected. If ActiveSheet.ProtectContents = True Then Do While ActiveSheet.ProtectContents = True 'If not, get a password Password = InputBox("Enter a password for this sheet") 'Reset Password ActiveSheet.Unprotect (Password) Loop End If Next End If If Decision = "g" Or Decision = "G" Then For i = 1 To Sheets.Count - 1 ActiveSheet.Next.Select If ActiveSheet.ProtectContents = True Then ActiveSheet.Unprotect (Password) End If Next End If Sheets(1).Select For i = 1 To 4 Sheets(1).Select If ActiveSheet.ProtectContents = False Then ProtectNext = False Range("Sheet" & i & "Protection").Value = "False" Else: Range("Sheet" & i & "Protection").Value = "True" End If If i = 1 Then Sheet1ProtectionChanges End If If i = 2 Then Sheet2ProtectionChanges End If If i = 3 Then Sheet3ProtectionChanges End If If i = 4 Then Sheet4ProtectionChanges End If If i 4 Then ActiveSheet.Next.Select End If Next Sheets(CurrentSheetName).Select Range("A1").Select End Sub . |
Thread Tools | |
Display Modes | |
|
|