how to check if word and excel are password protected in a directory
Hi!
I am selecting a directory and then I want to loop through the files and check which xls and which docs are password protected and drop their names into column A in excel. I haven't gotten to the part of inserting into excel yet.
I am trying to figure out how to check for password protected. I don't want to have to open each file separately.
I am currently testing with excel, in a test directory i have one password protected xls and the other one is open. When i run my app both of them give me the message that they are password protected.
I am not trying to crack password here...i am trying to enter the file names of the password protected xls and then remove them from the directory.
I would really appreciate your help.
Thanks a lot!
Thanks!
Tammy
Function IsProtected(ByVal filepath_x As String) As Boolean
IsProtected = False
On Error GoTo Err_Hndlr
Dim Excel As Excel.Application
Dim Workbook As Excel.Workbook
Workbook = New Excel.Workbook
Dim excelsheet As Excel.Worksheet
excelsheet = New Excel.Worksheet
Workbook.Unprotect(Password:="")
Exit Function
Err_Hndlr:
IsProtected = True
End Function
Private Sub button18_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button18.Click
There is no reliable way to know if a workbook is protected by a password or not ,other than simply trying to open the file and seeing if the attempt succeeds.
I am sure that there are ways of looking into the file contents itself, and seeing if it is protected, but such approaches will amount to "hacks" in one way or another. Also keep in mind that there are many different file types as well; for example, Excel 2003 files are binary format while Excel 2007 files are XML-based.
You may be able to search the Internet and/or find some other forum or group that could help you more with a file-inspection approach. But I would simply attempt to call Excel.Workbooks.Open and trap the error for each file that fails.
By the way, in your code, I think it is a mistake to name your Workbook variable "Workbook". It leads to confusion when you later call "Workbook.Protect" or the like. It makes it look like you are calling a static method (which VB.NET calls a shared method) and I think it will only lead to confusion for you down the road...
Instead, I would suggest naming your variable "myWorkbook", or even better, something shorter like "xlWB" or just "wb".
It is not a hack since I am trying to see if they open and if they are password protected I want to log the file name and delete them from the folder so as not to waste time with those files.
I understand the problem behind the diff versions of Excel. That will definetly be a problem.
In the meantime since i could not figure out thr excel one I came up with the word check...but i am having problems with thr vb code.
i have the following folder with 2 docs....one has a password and one doesn't. The message to enter the password pops up and when i press ok then it returns my message that the doc is password protected . i do not want that box to enter password to even pop up. and then it should tell me in a mesg box that the other doc can be open but it does nmot.
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' Declare a variable named theFolderBrowser of type FolderBrowserDialog.
Dim theFolderBrowser As New FolderBrowserDialog
' Set theFolderBrowser object's Description property to give the user instructions.
theFolderBrowser.Description = "Please select a folder to get info on."
' Set theFolderBrowser object's ShowNewFolder property to false when
' the a FolderBrowserDialog is to be used only for selecting an existing folder.
theFolderBrowser.ShowNewFolderButton = False
' Optionally set the RootFolder and SelectedPath properties to
' control which folder will be selected when browsing begings
' and to make it the selected folder.
' For this example start browsing in the Desktop folder.
theFolderBrowser.RootFolder = System.Environment.SpecialFolder.Desktop
' Default theFolderBrowserDialog object's SelectedPath property to the path to the Desktop folder.
theFolderBrowser.SelectedPath = My.Computer.FileSystem.SpecialDirectories.Desktop
' If the user clicks theFolderBrowser's OK button..
If theFolderBrowser.ShowDialog = Windows.Forms.DialogResult.OK Then
' Set the FolderChoiceTextBox's Text to theFolderBrowserDialog's did
' SelectedPath property.
TextBox1.Text = theFolderBrowser.SelectedPath
End If
End Sub
Private Sub button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim appWord As Word.Application
Dim doc As Object
Dim strFName As String
Dim bError As Boolean
Dim FS As New Scripting.FileSystemObjectClass
Dim Fol As Scripting.FolderClass
Dim Fil As Scripting.File
Fol = FS.GetFolder(TextBox1.Text)
For Each Fil In Fol.Files
If Microsoft.VisualBasic.Right(Fil.Name, 3) = "doc" Then
On Error GoTo ErrorHandle
appWord = CreateObject("Word.Application")
strFName = "C:\TEST_VB\TestVBDOC.doc"
'strFName = TextBox1.Text & "\" & Fil.Name
'MessageBox.Show("the file name is " & strFName & ".")
'
' Open the document and pass a bogus password to see if the file is password protected.
' If it is, then we ignore it and continue on with the next file.
It is not a hack since I am trying to see if they open and if they are password protected I want to log the file name and delete them from the folder so as not to waste time with those files.
Oh, your *goal* is totally legitimate. But trying to read the native file without Excel is going to require some undocumented "tricks" or things that you (or someone else) has worked out. It is in this regard that I call it a "hack". Their later file formats (particularly for Excel 2007) are now open source, so, in theory, you could read the documentation on that ... but this is very complex stuff.
Quote:
Originally Posted by jtammyg
I understand the problem behind the diff versions of Excel. That will definetly be a problem.
Could definitely be if you need to read Excel 2007 at some point. This is why trying to .Open() the file via Excel will be your safest bet, I think.
In order to prevent the password dialog box from opening up when a password is required, provide an empty string for the password argument, something like:
Doing this will allow a workbook that does not have password protection to be opened, while throwing an exception for those workbooks that cannot be opened. You can then trap the exception using error handling:
Code:
Sub MySub()
Dim wb As Excel.Workbook
Dim pathToMyWorkbook As String = "C:\PathTyMyWorkbook\MyWorkbook.xls"
Try
wb = xlApp.Workbooks.Open(pathToMyWorkbook, Password:="")
Catch
wb = Nothing
End Try
If wb IsNot Nothing Then
MessageBox.Show(wb.Name & " is not password protected.")
Else
MessageBox.Show(pathToMyWorkbook & " is password protected.")
End If
End Sub
I have modified the code to loop through a folder. Here is what I came up with, which works great with the code you sent me. I really appreciate your help here.
Private Sub button18_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button18.Click
Dim FS As New Scripting.FileSystemObjectClass
Dim Fol As Scripting.FolderClass
Dim Fil As Scripting.File
Fol = FS.GetFolder(TextBox2.Text)
Dim xlApp As Excel.Application
xlApp = New Excel.Application
Dim wb As Excel.Workbook
For Each Fil In Fol.Files
If Microsoft.VisualBasic.Right(Fil.Name, 3) = "xls" Then
Dim pathToMyWorkbook As String = TextBox2.Text & "\" & Fil.Name
If wb IsNot Nothing Then
MessageBox.Show(wb.Name & " is not password protected.")
Else
MessageBox.Show(pathToMyWorkbook & " is password protected.")
End If
End If
Next
End sub
I have the following questions:
1) Will this code still work with other versions of Excel (like previous and also 2007)?
2) How can I insert the file names of the xls that are password protected into a spreadsheet for future reference? and then delete them from the folder?
3) How can I get a total count of the password protected and non-password protected excel files?
I know it's a lot of questions. I'm sorry...i am trying to learn as i go along here.
I do really appreciate your time and help on this.
By the way, you can make use of the [code] tags to make your code look better in your posts. (See how I used them here, for example, by hitting the Reply button and seeing how I used them.)
Quote:
Originally Posted by jtammyg
I have the following questions:
1) Will this code still work with other versions of Excel (like previous and also 2007)?
It will work on Excel 2000 and above for sure. As for Excel '97 I'm not certain because Excel '97 is based on VB 5.0 and I don't know if the Scripting.FileSystemObjectClass exists for VB 5.0. You'd have to test. (That said, there are very few users still using Excel '97 at this point.)
You can avoid using the Scripting.FileSystemObjectClass (which is COM-based, anyway -- that is, it is not native to .NET) by using VB.NET file I/O commands. You can have a read of the File I/O In VB.Net tutorial for more on this.
Quote:
Originally Posted by jtammyg
2) How can I insert the file names of the xls that are password protected into a spreadsheet for future reference? and then delete them from the folder?
Ummm... you sure you want to delete these files? You can delete a file using the Scripting.FileSystemObjectClass or via File I/O In VB.Net.
You can insert the name into a spreadsheet by assigning the Range.Value for a cell, such as:
Code:
Dim wb As Excel.Workbook = xlApp.Workbooks.Add()
Dim ws As Excel.Worksheet = wb.Worksheets(1)
ws.Cells(1,1).Value = "Name of my password protected workbook"
Quote:
Originally Posted by jtammyg
3) How can I get a total count of the password protected and non-password protected excel files?
Yep. Just count them as you iterate through the list of files that you get via your file IO operations.
1) I have been looking at the I/O tutorial and is still not to clear how to achieve the count of password protected and not password protected from that.
2) I tried writing the password protected file names to a workbook but it is writing one per password protected file....and i want to get all the password protected files in that specific selected directory in a single workbook. I have no clue how to do this. Can you please help me again?
Here is the code for that:
Code:
For Each Fil In Fol.Files
If Microsoft.VisualBasic.Right(Fil.Name, 3) = "xls" Then
Dim pathToMyWorkbook As String = TextBox2.Text & "\" & Fil.Name
Try
wb = xlApp.Workbooks.Open(pathToMyWorkbook, Password:="", UpdateLinks:=0)
Catch
wb = Nothing
End Try
If wb IsNot Nothing Then
MessageBox.Show(wb.Name & " is not password protected.")
Else
MessageBox.Show(pathToMyWorkbook & " is password protected.")
Dim wb2 As Excel.Workbook = xlApp.Workbooks.Add()
Dim ws As Excel.Worksheet = wb2.Worksheets(1)
ws.Cells(1, 1).Value = TextBox2.Text
ws.Cells(1, 2).Value = Fil.Name
Dim strFileName As String = "C:\TEST_VB\Password Protected Files Reports\Password Protected Files Report" & _
Now().Month & "-" & Now().Day & "-" & Now().Year & " " & Date.Now.ToString("hh mm ss") & ".xls"
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try
If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If
wb2.SaveAs(strFileName)
'Close all Excel objects and remove from memory
xlApp.Application.Quit()
Runtime.InteropServices.Marshal.ReleaseComObject(ws)
Runtime.InteropServices.Marshal.ReleaseComObject(wb2)
GC.Collect()
GC.WaitForPendingFinalizers()
End If
End If
Next
Thanks a lot again, Mike!
Last edited by Colin Legg; 03-28-2009 at 09:18 AM.
Reason: added line breaks to prevent scrolling
I have the following code which works fine for excel, but not for word. Even though i have the displayalerts = false, I still get the boxes to enter the passwords or the message that the file has been reserved by person1 and you want to open as read only.
I do not want to show any of these windows nor do i have Word open the docs that are not password protected like it is happening now.
here is the code:
Code:
Private Sub button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim FS As New Scripting.FileSystemObjectClass
Dim Fol As Scripting.FolderClass
Dim Fil As Scripting.File
Fol = FS.GetFolder(TextBox1.Text)
Dim WrdApp As Word.Application
WrdApp = New Word.Application
Dim dc As Word.Document
Dim xlApp As Excel.Application
xlApp = New Excel.Application
Dim wb As Excel.Workbook
xlApp.DisplayAlerts = False
WrdApp.DisplayAlerts = False
WrdApp.DisplayAlerts = Word.WdAlertLevel.wdAlertsNone
WrdApp.Visible = False
Dim flcntpwdxls As Integer
Dim flcntnopwdxls As Integer
Dim flcntpwddoc As Integer
Dim flcntnopwddoc As Integer
Dim flcntpwd As Integer
Dim flcntnopwd As Integer
'Dim connstring As String
For Each Fil In Fol.Files
If Microsoft.VisualBasic.Right(Fil.Name, 3) = "doc" Then
Dim pathToMyWorkbook As String = TextBox1.Text & "\" & Fil.Name
Try
dc = WrdApp.Documents.Open(pathToMyWorkbook, PasswordDocument:="")
Catch
dc = Nothing
End Try
If dc IsNot Nothing Then
'MessageBox.Show(dc.Name & " is not password protected.")
flcntnopwddoc = flcntnopwddoc + 1
Else
flcntpwddoc = flcntpwddoc + 1
'MessageBox.Show(pathToMyWorkbook & " is password protected.")
Me.ListBox1.Items.Add(Fil.Name)
End If
End If
If Microsoft.VisualBasic.Right(Fil.Name, 3) = "xls" Then
Dim pathToMyWorkbook As String = TextBox1.Text & "\" & Fil.Name
Try
wb = xlApp.Workbooks.Open(pathToMyWorkbook, Password:="", UpdateLinks:=0)
Catch
wb = Nothing
End Try
If wb IsNot Nothing Then
'MessageBox.Show(wb.Name & " is not password protected.")
flcntnopwdxls = flcntnopwdxls + 1
Else
flcntpwdxls = flcntpwdxls + 1
'MessageBox.Show(pathToMyWorkbook & " is password protected.")
Me.ListBox1.Items.Add(Fil.Name)
End If
End If
Next
flcntpwd = flcntpwdxls + flcntpwddoc
flcntnopwd = flcntnopwdxls + flcntnopwddoc
MessageBox.Show("There are " & flcntpwd & _
" password protected files. There are " & flcntnopwd & " non-password protected files.")
'========================================================================================
'Export all password protected file names to an Excel spreadsheet for marking afterwards
'========================================================================================
Dim wb2 As Excel.Workbook = xlApp.Workbooks.Add()
Dim ws As Excel.Worksheet = wb2.Worksheets(1)
Dim r As Integer
For r = 1 To ListBox1.Items.Count
ws.Cells(r, 1) = ListBox1.Items(r - 1)
Next r
Dim strFileName As String = "C:\TEST_VB\Password Protected Files Reports\XLS Password Protected Files Report" & _
Now().Month & "-" & Now().Day & "-" & Now().Year & " " & Date.Now.ToString("hh mm ss") & ".xls"
Dim blnFileOpen As Boolean = False
Try
Dim fileTemp As System.IO.FileStream = System.IO.File.OpenWrite(strFileName)
fileTemp.Close()
Catch ex As Exception
blnFileOpen = False
End Try
If System.IO.File.Exists(strFileName) Then
System.IO.File.Delete(strFileName)
End If
wb2.SaveAs(strFileName)
MessageBox.Show("The Password Protected Files Report has been saved.")
'Close all Excel objects and remove from memory
xlApp.Application.Quit()
Runtime.InteropServices.Marshal.ReleaseComObject(ws)
Runtime.InteropServices.Marshal.ReleaseComObject(wb2)
GC.Collect()
GC.WaitForPendingFinalizers()
End sub
Last edited by Colin Legg; 03-28-2009 at 09:18 AM.
Reason: added line breaks to prevent scrolling
I'm not a Word programmer so I don't know the Word Object Model well...
Display Alerts is not supposed to suppress these kinds of messages (they don't in Excel either), so the key is to pass in the correct arguments when calling the Word.Application.Documents.Open() method.
You are passing in "" for the 'PasswordDocument' argument, which would have been my guess. I don't know why this wouldn't suppress the message to the user.
To suppress the Read-Only notification, I would simply open the document as Read-Only in the first place by passing in True for the 'ReadOnly' argument.
Beyond this though, you might have to go on a Word-specific forum for more help if these don't work...
Unclean as it sounds - and I have not tried this - my guess would be to pass a long string into the PasswordDocument parameter which could not conceivably match the password of a protected document.
If the document opens then it was not password protected, else the passwords do not match.
Thank you so much Mike. I am still stuck on the word issue but for the most part is working fine. I am now in the process of trying to see if pdf is open etc which is driving me nuts.
I just wanted to thank you very much for your help.
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET. subscribe
how to check if word and excel are password protected in a directory
Linear Mode
