Don’t rely on locking/password protecting to hide your data!

January 2, 2009

I’ve seen this a few too many times.  Someone wants to prevent the user of a spreadsheet from viewing detail data, so they hide the sheets (or cells), lock it, and password protect it.  In about 4 line of code, that data is visible again:

Sub copyWorkbook()
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 01/02/2009
‘ Makes a copy of a workbook’s values for each workbook

Sub copyWorkbook()
    Dim sourceWB As Workbook, destWB As Workbook, sourceWS As Worksheet, destWS As Worksheet

    Set destWB = Workbooks(”Destination.xls”)
    Set sourceWB = Workbooks(”Workbook_that_has_the_data_you_want.xls”)

     For Each destWS In destWB.Worksheets ‘I like to keep one workbook in there, just to avoid problems
        If destWS.Name <> “__BLANK__” Then
            destWS.Delete
        End If
    Next destWS
   
    ‘Here’s the important stuff
    For Each sourceWS In sourceWB.Worksheets
        destWB.Worksheets.Add().Name = sourceWS.Name
        sourceWS.Range(”A:IV”).Copy (destWB.Worksheets(sourceWS.Name).Range(”A1″))
    Next sourceWS
   
End Sub

Posted in Excel