none
Problem not wanting to open a file with modify file password... RRS feed

  • Question

  • Hi

    I have created a loop that opens all the Excel files in a directory, in order to make modifications to the ones that don't have passwords attached.  I've been able to identify files with passwords when opening them and I am able to ignore them. However I have a problem with files with a 'modify file' password as the modify file dialog box appears.  Is there anything I can do to use VBA to stop this dialog appearing as send keys don't seem to work (as you have to press Cancel on the dialog box first which defeats the purpose).  Unfortunately Application.DisplayAlerts = False doesn't work.

    Many thanks

    James

    Andrew Binnz

    Tuesday, August 7, 2012 11:56 AM

All replies

  • Workbook has two password One password for opening and one is for writing in files.When you open a file you specify either open password or both.

    "the ones that don't have passwords attached.  "

    In above line which password you are referring ? If you want to modify with no password at all then can you share the code.I suppose you can wrap below way. 

    On Error Resume next

    'Your code

    On Error Goto 0


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, August 7, 2012 12:13 PM
    Answerer
  • To confirm: If a file has a password to open I can capture this and deal with it.  My problem is if a file does not have a password to open but has a password to modify.  This brings up a separate dialog which I don't want to see.

    Hope this helps

    James


    Andrew Binnz

    Tuesday, August 7, 2012 12:30 PM
  • On Error Resume Next
    Workbooks.Open "Copy of Book1", writerespassword:=""
    MsgBox "hi"

    Here I am forcing excel a wrong password,excel will try and raise error.I have asked excel to ignore error by On Error .....

    This will stop the dialog box as we have specified password but wrong one.

    In the lines after open you should use a conditional statement to check for the status of file opened or not.

    This is a direction only.If code is shared I can adjust/insert accordingly.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, August 7, 2012 12:50 PM
    Answerer
  • Sub AddData()

            Dim wb As Workbook
           
            Application.DisplayAlerts = False
            Application.EnableEvents = False
               
                On Error Resume Next

              
            Set wb = Workbooks.Open(Filename:="c:\test.xls", UpdateLinks:=False, IgnoreReadOnlyrecommended:=True, ReadOnly:=False)**
               
            If Err <> 0 Then
                'File has a password
                On Error GoTo 0
            Else
           
                'File doesn't have a password - add stuff to worksheets
            End If
                 
            Application.DisplayAlerts = True
            Application.EnableEvents = True

    End Sub

    ** When this line is run the 'enter password to modify file' dialog appears


    Andrew Binnz

    Tuesday, August 7, 2012 1:07 PM
  • 
    Sub AddData()
        
        Dim wb As Workbook
        
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        
        On Error Resume Next
        
        Set wb = Workbooks.Open(Filename:="Copy of Book1.xlsx", _
        UpdateLinks:=False, writerespassword:="", _
        IgnoreReadOnlyrecommended:=True, ReadOnly:=False)
        
        If Err <> 0 Then
            'File has a password
            On Error GoTo 0
        Else
            MsgBox "OK"
            'File doesn't have a password - add stuff to worksheets
        End If
        
        Application.DisplayAlerts = True
        Application.EnableEvents = True
    End Sub


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, August 7, 2012 1:48 PM
    Answerer
  • That's done it.  Many thanks for this

    James


    Andrew Binnz

    Tuesday, August 7, 2012 2:15 PM
  • Pls mark it as Answer if it solves the issue.

    This will help others to find the soution.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, August 8, 2012 6:55 AM
    Answerer