dcsimg
Results 1 to 9 of 9

Thread: Password protected Excel Files

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2019
    Posts
    35

    Password protected Excel Files

    Hello, how do you detect if an Excel file is password protected, currently no error is thrown for me to catch. Instead xlApp becomes visible and a msgbox for a password is prompted, I would prefer to handle this myself like below, however I can't find a away to detect if there is a password on the file before opening it.

    Code:
    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlProtected As Boolean = IsPasswordProtected(xlPath)
    If xlProtected = 0 Then
        xlBook = xlApp.Workbooks.Open(xlPath)
    Else
        xlPassword = InputBox("Please enter the password...", "Password Protected", "password")
        xlBook = xlApp.Workbooks.Open(xlPath, Password:=xlPassword)
    End If

  2. #2
    Junior Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris
    Posts
    20

    Re: Password protected Excel Files

    Hello,

    Use the workbook.haspassword property


    Regards

  3. #3
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,497

  4. #4

    Thread Starter
    Member
    Join Date
    Oct 2019
    Posts
    35

    Re: Password protected Excel Files

    Thanks for the input guys but;

    Quote Originally Posted by Delaney View Post
    Use the workbook.haspassword property
    This is VBA and uses the ActiveWorkbook method which I assume Workbook in question has to already be open.


    Same as above


    Although this is VBA, slightly more interesting but seems like a messy work around. Try to open Workbook with a random password then catch error. Not sure if the error will be different for wrong password vs password not required. I'll leave this one for now and let Excel open and prompt for password.

  5. #5
    Junior Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris
    Posts
    20

    Re: Password protected Excel Files

    Quote Originally Posted by neodotnet View Post
    Thanks for the input guys but;

    This is VBA and uses the ActiveWorkbook method which I assume Workbook in question has to already be open.
    No it is not VBA, it is a VB.net command and I have tested it on VS2010 and VS2017 and it is working. You have an example in VB in the link associated with the post : https://docs.microsoft.com/en-us/off...ok.haspassword.

    For you the commande will be :
    Code:
    if  xlBook.haspassword = true then etc...
    regards

  6. #6

    Thread Starter
    Member
    Join Date
    Oct 2019
    Posts
    35

    Re: Password protected Excel Files

    Quote Originally Posted by Delaney View Post
    No it is not VBA, it is a VB.net command and I have tested it on VS2010 and VS2017 and it is working. You have an example in VB in the link associated with the post : https://docs.microsoft.com/en-us/off...ok.haspassword.

    For you the commande will be :
    Code:
    if  xlBook.haspassword = true then etc...
    regards
    However you need to set xlbook first with;
    Code:
    xlBook = xlApp.Workbooks.Open(AttachPath)
    xlBook = xlApp.Workbooks.Open(AttachPath, Password:=xlPassword)
    I don't want to test if a password is present after the xlBook is open, I need to test before.

    Also that link is definitely in the "Office VBA Reference" section.

  7. #7
    Junior Member Delaney's Avatar
    Join Date
    Nov 2019
    Location
    Paris
    Posts
    20

    Re: Password protected Excel Files

    You always need to open (or try to open) the file first.

    here is another idea:

    use exception to detect it, because when you try to open excel which have password, the code fire the exception, you can catch the exception and do whatever you want
    see here


    an other example
    http://www.vbforums.com/showthread.p...ed-Excel-files
    be careful, it seems that in this last exemple the code is VBA and not VB but the idea is to detect the error at the opening.




    Quote Originally Posted by neodotnet View Post
    Also that link is definitely in the "Office VBA Reference" section.
    My appologize

    By the way it seems that like most or all VBA feature it is also a .net function : https://docs.microsoft.com/en-us/dot...view=vsto-2017

    Regards
    Last edited by Delaney; Dec 5th, 2019 at 09:04 AM.

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,123

    Re: Password protected Excel Files

    using the "HasPassword" Property will always involve opening the File, if you have 500 or more files in a Folder
    it will be a problem

    you can use a script with Powershell to detect if excel or any other Office document is password protected
    I created 3 xlsx Files with Password, 5 other Files without Password are also in the TestFolder
    Code:
    Clear-Host
    $i=0
    $Full = Get-ChildItem D:\TestFolder -Include *.doc*, *.ppt*, *.xls* -Recurse
    $List = select-string -pattern "<encryption" $Full 
    
    foreach ($file in $List) 
    {$file.path;$i++} 
    
    Write-Host $i " files found with Password"
    
    
    
    the Output:
    D:\TestFolder\Test1.xlsx
    D:\TestFolder\Test2.xlsx
    D:\TestFolder\Test3.xlsx
    3  files found with Password
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    Member
    Join Date
    Oct 2019
    Posts
    35

    Re: Password protected Excel Files

    Quote Originally Posted by ChrisE View Post
    using the "HasPassword" Property will always involve opening the File, if you have 500 or more files in a Folder
    it will be a problem

    you can use a script with Powershell to detect if excel or any other Office document is password protected
    I created 3 xlsx Files with Password, 5 other Files without Password are also in the TestFolder
    Code:
    Clear-Host
    $i=0
    $Full = Get-ChildItem D:\TestFolder -Include *.doc*, *.ppt*, *.xls* -Recurse
    $List = select-string -pattern "<encryption" $Full 
    
    foreach ($file in $List) 
    {$file.path;$i++} 
    
    Write-Host $i " files found with Password"
    
    
    
    the Output:
    D:\TestFolder\Test1.xlsx
    D:\TestFolder\Test2.xlsx
    D:\TestFolder\Test3.xlsx
    3  files found with Password
    Thanks, I've done something similar in python. I will have to test speeds to see if it is workable. I will only be opening a dozen or so at a time, so might be ok.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width