Wednesday, February 29, 2012

To unlock Excel worksheet or workbook protection


You can download the workbook allinternalpasswords.xls if you don't want to cut and paste the macro below.

Option 1. Open allinternalpasswords.xls. And then open the excel protected file. Press ALT + F11 to open VBA, run the macro. then save.

Option 2. Open protected file, press ALT + F11 to open VBA. Click Insert >> Module then copy and paste the script below  and then run the macro



Option Explicit


Public Sub AllInternalPasswords()
    ' Breaks worksheet and workbook structure passwords. Bob McCormick
    '  probably originator of base code algorithm modified for coverage
    '  of workbook structure / windows passwords and for multiple passwords
    '
    ' Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1)
    ' Modified 2003-Apr-04 by JEM: All msgs to constants, and
    '   eliminate one Exit Sub (Version 1.1.1)
    ' Reveals hashed passwords NOT original passwords
    Const DBLSPACE As String = vbNewLine & vbNewLine
    Const AUTHORS As String = DBLSPACE & vbNewLine & _
            "Adapted from Bob McCormick base code by " & _
            "Norman Harker and JE McGimpsey"
    Const HEADER As String = "AllInternalPasswords User Message"
    Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"
    Const REPBACK As String = DBLSPACE & "Please report failure " & _
            "to the microsoft.public.excel.programming newsgroup."
    Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _
            "now be free of all password protection, so make sure you:" & _
            DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _
            DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _
            DBLSPACE & "Also, remember that the password was " & _
            "put there for a reason. Don't stuff up crucial formulas " & _
            "or data." & DBLSPACE & "Access and use of some data " & _
            "may be an offense. If in doubt, don't."
    Const MSGNOPWORDS1 As String = "There were no passwords on " & _
            "sheets, or workbook structure or windows." & AUTHORS & VERSION
    Const MSGNOPWORDS2 As String = "There was no protection to " & _
            "workbook structure or windows." & DBLSPACE & _
            "Proceeding to unprotect sheets." & AUTHORS & VERSION
    Const MSGTAKETIME As String = "After pressing OK button this " & _
            "will take some time." & DBLSPACE & "Amount of time " & _
            "depends on how many different passwords, the " & _
            "passwords, and your computer's specification." & DBLSPACE & _
            "Just be patient! Make me a coffee!" & AUTHORS & VERSION
    Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _
            "Structure or Windows Password set." & DBLSPACE & _
            "The password found was: " & DBLSPACE & "$$" & DBLSPACE & _
            "Note it down for potential future use in other workbooks by " & _
            "the same person who set this password." & DBLSPACE & _
            "Now to check and clear other passwords." & AUTHORS & VERSION
    Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _
            "password set." & DBLSPACE & "The password found was: " & _
            DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _
            "future use in other workbooks by same person who " & _
            "set this password." & DBLSPACE & "Now to check and clear " & _
            "other passwords." & AUTHORS & VERSION
    Const MSGONLYONE As String = "Only structure / windows " & _
             "protected with the password that was just found." & _
             ALLCLEAR & AUTHORS & VERSION & REPBACK
    Dim w1 As Worksheet, w2 As Worksheet
    Dim i As Integer, j As Integer, k As Integer, l As Integer
    Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
    Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
    Dim PWord1 As String
    Dim ShTag As Boolean, WinTag As Boolean
    
    Application.ScreenUpdating = False
    With ActiveWorkbook
        WinTag = .ProtectStructure Or .ProtectWindows
    End With
    ShTag = False
    For Each w1 In Worksheets
            ShTag = ShTag Or w1.ProtectContents
    Next w1
    If Not ShTag And Not WinTag Then
        MsgBox MSGNOPWORDS1, vbInformation, HEADER
        Exit Sub
    End If
    MsgBox MSGTAKETIME, vbInformation, HEADER
    If Not WinTag Then
        MsgBox MSGNOPWORDS2, vbInformation, HEADER
    Else
      On Error Resume Next
      Do      'dummy do loop
        For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
        For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
        For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
        For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
        With ActiveWorkbook
          .Unprotect Chr(i) & Chr(j) & Chr(k) & _
             Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
             Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
          If .ProtectStructure = False And _
          .ProtectWindows = False Then
              PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
                Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
                Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
              MsgBox Application.Substitute(MSGPWORDFOUND1, _
                    "$$", PWord1), vbInformation, HEADER
              Exit Do  'Bypass all for...nexts
          End If
        End With
        Next: Next: Next: Next: Next: Next
        Next: Next: Next: Next: Next: Next
      Loop Until True
      On Error GoTo 0
    End If
    If WinTag And Not ShTag Then
      MsgBox MSGONLYONE, vbInformation, HEADER
      Exit Sub
    End If
    On Error Resume Next
    For Each w1 In Worksheets
      'Attempt clearance with PWord1
      w1.Unprotect PWord1
    Next w1
    On Error GoTo 0
    ShTag = False
    For Each w1 In Worksheets
      'Checks for all clear ShTag triggered to 1 if not.
      ShTag = ShTag Or w1.ProtectContents
    Next w1
    If ShTag Then
        For Each w1 In Worksheets
          With w1
            If .ProtectContents Then
              On Error Resume Next
              Do      'Dummy do loop
                For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
                For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
                For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
                For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
                .Unprotect Chr(i) & Chr(j) & Chr(k) & _
                  Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
                  Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
                If Not .ProtectContents Then
                  PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
                    Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
                    Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
                  MsgBox Application.Substitute(MSGPWORDFOUND2, _
                        "$$", PWord1), vbInformation, HEADER
                  'leverage finding Pword by trying on other sheets
                  For Each w2 In Worksheets
                    w2.Unprotect PWord1
                  Next w2
                  Exit Do  'Bypass all for...nexts
                End If
                Next: Next: Next: Next: Next: Next
                Next: Next: Next: Next: Next: Next
              Loop Until True
              On Error GoTo 0
            End If
          End With
        Next w1
    End If
    MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
End Sub







Automatic Save A Backup Copy of A Workbook in Excel 2007


If the Excel file always save a backup file:
1.       Click FILE à Save As à Tools (drop down) à General Options




2.       Unchecked  “Always create backup”. Click OK

Friday, February 17, 2012

Configure Automatic Replies for a user in Exchange 2010


A user is out of office for some reason – on vacation, sick, on a sabbatical or extended leave of absence, or traveling to a remote location on business, and forgets to set an automatic reply, also known as an Out Of Office message or OOF in Exchange/Outlook lingo. As an Exchange administrator, you get an email from the user’s manager asking you to configure an OOF for the user.
In previous versions of Exchange, you would need to access the user’s mailbox to be able to do this. Out of Office messages are stored in the Non-IPM tree of a user’s mailbox along with other metadata. Without access to the mailbox, you can’t modify data in it. Two ways for an admin to access a mailbox:
  1. Grant yourself Full Access mailbox permission to the user’s mailbox.
  2. Change the user’s password and log in as user.
It is safe to say that either of these options is potentially dangerous. The first option grants the administrator access to all of the data in the user’s mailbox. The second option grants the administrator access to all of the data that the user account can access within your company and locks the user out of his own user account (as the user in question no longer knows the account password).
In Exchange 2010, you can configure auto-reply options for your users without using either of the above options. You must be a member of a role group that has either the Mail Recipients or User Options management roles.

Configure auto-reply options using the Exchange Control Panel

To configure an auto-reply using the ECP: (eg. https://webmail.domain.com/ecp)
  1. From Mail > Options, select Another User (default My Organization).

    Figure 1: Select Another User
  2. Select the user you want to configure the auto-reply for
  3. In the new window, ensure the user's name is displayed in the alert message, and then click Tell people you’re on vacation

    Figure 2: When managing another user in the ECP, an alert near the top of the page displays the name of the user you're managing
  4. From the Automatic Replies tab, configure the auto-reply options for the user (see screenshot).
In Exchange 2007, we introduced the ability to create different Out of Office messages for external and internal recipients. You can also disable or enable Out of Office messages on a per-user basis and on a per-remote domain basis in Remote Domain settings. For details, see previous post Exchange Server 2007 Out of Office (OOF).

Configure auto-reply options using the Shell

This command schedules internal and external auto-replies from 9/8/2011 to 9/15/2011:
Set-MailboxAutoReplyConfiguration bsuneja@e14labs.com –AutoReplyState Scheduled –StartTime “9/8/2011” –EndTime “9/15/2011” –ExternalMessage “External OOF message here” –InternalMessage “Internal OOF message here”
To configure auto-replies to be sent until they're disabled (i.e. without a schedule), set the AutoReplyStateparameter to Enabled and do not specify the StarTime and EndTime parameters. For detailed syntax and parameter descriptions, see Set-MailboxAutoReplyConfiguration.
This command retrieves auto-reply settings for a mailbox.
Get-MailboxAutoReplyConfiguration bsuneja@e14labs.com
This command disables auto-reply configured for a mailbox:
Set-MailboxAutoReplyConfiguration bsuneja@e14labs.com –AutoReplyState Disabled –ExternalMessage $null –InternalMessage $null
Another way:
View the current auto reply settings for a mailbox:Here there has been no message set.
Get-MailboxAutoReplyConfiguration "andy barnes"

RunspaceId       : b1531019-f59a-45aa-9176-3033ca169444
AutoReplyState   : Disabled
EndTime          : 03/08/2010 17:00:00
ExternalAudience : All
ExternalMessage  :
InternalMessage  :
StartTime        : 02/08/2010 17:00:00
MailboxOwnerId   : domain.local/New York/Andy Barnes
Identity         : domain.local/New York/Andy Barnes
IsValid          : True 


Set the auto reply settings for a mailbox:With seperate internal and external messages
Set-MailboxAutoReplyConfiguration -identity "andy barnes" -AutoReplyState enabled -EndTime 03/08/2010 -InternalMessage "I am currently out of the office on until Tuesday 4rd August due to
partying and so forth. Please could all enquiries be directed to ext 123 and emails be sent tosomewhere@domain.local" -ExternalMessage "I am currently out of the office on until Tuesday 4rd August. Please could all enquiries be directed to ext 123 and emails be sent to somewhere@domain.local"



View the current auto reply settings for a mailbox:Here you can see the message we have just set. Notice the internal message is different to the external message so that you can include further details to colleagues, that you might not want external contacts to know.
Get-MailboxAutoReplyConfiguration "andy barnes"

RunspaceId       : b1531019-f59a-45aa-9176-3033ca169444
AutoReplyState   : Enabled
EndTime          : 03/08/2010 16:00:00
ExternalAudience : All
ExternalMessage  :
                  
                   I am currently out of the office on until Tuesday 4rd August. Please could all enquiries be directed to ext 123 and emails be sent to somewhere@domain.local
                  

InternalMessage  :
                  
                   I am currently out of the office on until Tuesday 4rd August due to partying and so forth. Please could all enquiries be directed to ext 123 and emails be sent to somewhere@domain.local
                  

StartTime        : 02/08/2010 16:00:00
MailboxOwnerId   : domain.local/New York/Andy Barnes
Identity         : domain.local/New York/Andy Barnes
IsValid          : True


Disable Out of Office/Auto Reply:
Set-MailboxAutoReplyConfiguration "andy barnes" -AutoReplyState disabled


Disable Out of Office/Auto Reply and clear the Internal/External message:
Set-MailboxAutoReplyConfiguration -identity "andy barnes" -AutoReplyState disabled -InternalMessage "" -ExternalMessage ""