Click here to Skip to main content
Click here to Skip to main content

Implementing Password Security on Excel Workbooks Using Visual Studio .NET

, 26 May 2006
Rate this:
Please Sign up or sign in to vote.
Creating password protected Excel workbooks using VB.NET.

Sample Image - Excel_Security.jpg

Introduction

Developing secure products are as important as the product itself. Most security measures involve data encryption and passwords. Excel sheets are mostly used for reporting purposes and reports mostly based on confidential data, so security is very important for Excel based reports.

Here, I have tried to develop a small utility for a security implementation. The same code can be used in both Visual Studio 2003 and 2005, and the code can be implemented in C# with proper syntax changes.

Code

Imports System.IO
Module Module1
    Public Filename As String
    Public oexcel As Excel.Application
    Public obook As Excel.Workbook
    Public osheet As Excel.Worksheet
    Sub Main()
        Try
            'File name and path, here i used abc file to be 
            'stored in Bin directory in the sloution directory
            Filename = AppDomain.CurrentDomain.BaseDirectory & _
                       "abc.xls"
            'check if file already exists then delete 
            'it to create a new file
            If File.Exists(Filename) Then
                File.Delete(Filename)
            End If
            If Not File.Exists(Filename) Then
                'create new excel application
                oexcel = CreateObject("Excel.Application")
                oexcel.Application.DisplayAlerts = False
                'add a new workbook
                obook = oexcel.Workbooks.Add
                oexcel.Visible = True

                Console.WriteLine("Generating Auto Report")
                osheet = oexcel.Worksheets(1)
                osheet.Name = "Test Sheet"
                osheet.Range("A1:G1").Merge()
                osheet.Range("A1").Value = "Implementing " & _ 
                       "Password Security on Excel " & _ 
                       "Workbook Using Studio.Net"
                osheet.Range("A1").Font.Color = RGB(255, 255, 255)
                osheet.Range("A1").Interior.ColorIndex = 5
                osheet.Range("A1").Font.Bold = True


                'password variable and value
                Dim password As String = "abc"
                'if you are using excel 11 or above 
                'then use the following code
                obook.Password = password
                obook.SaveAs(Filename)
                'otherwise use the folowing one
                obook.SaveAs(Filename, password:=password)
                'end application object and session
                osheet = Nothing
                obook.Close()
                obook = Nothing
                oexcel.Quit()
                oexcel = Nothing
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
        End Try
    End Sub
End Module

Working With the Demo

Download the demo project and unzip it, and then run "Excel_Automation.exe". It will generate an Excel sheet in the same directory with the name "abc.xls". When you try to open the Excel sheet, it will ask to enter the password, enter "abc", then the book will be visible to you.

Working With the Code

Here, I have attached the VB.NET code file, download it and then create a new console application project, and add the file to that project, and try with your desired changes. You can run the same code in both versions of Visual Studio .NET.

Summary

This is a small effort to generate a secure report for your critical data. Any feedback/comments are welcomed.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Malik Nasir
Team Leader ZonG, China Mobile Company
Pakistan Pakistan
I am currently based in Islamabad,Pakistan. I have done my MS Computer Science and I am involved in Microsoft Visual Studio.net Windows/Web application development since 2003 untill that i have been working in VB, ASP and web scripting languages. Currently i am working in ZonG as Team LEad IT Projects and Planing. I am also the administrator of Business Process

Comments and Discussions

 
QuestionHow to do in VB.NET PinmemberJats_4ru4-Sep-07 23:31 
AnswerRe: How to do in VB.NET Pinmemberjonesberyl17-Aug-10 22:58 
QuestionIs it standard Excel workbook protection? PinmemberBozhko Maxim23-Apr-07 2:28 
QuestionWhat is "studio.net"? PinmemberRobert Someone26-May-06 8:29 
AnswerRe: What is "studio.net"? PinmemberMalik Nasir26-May-06 22:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 26 May 2006
Article Copyright 2006 by Malik Nasir
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid