none
SaveCopyAs with password give error 1004 RRS feed

  • Question

  • Using Windows 10 and Office 2016

    I have a file "Password_Test.xlsm" that requires a password to open it.  The password is "test".  Once it is opened I run the following code

    Sub SaveSecondCopy()

    ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\PasswordTest2.xlsm"

    End Sub

    It works as expected.  However, if I then immediately run it again, I get the Runtime Error 1004 error message saying that it cannot access the file. HOWEVER, if I click Debug and hit F8, it then saves OK.  Alternatively, if I click End and then run it again, it works fine.  Next time I run it I get the same sequence of events - i.e. error message, then successful save.  So it appears to give an error 1004 every second time it is run

    If I put in an error handler to say Resume if error 1004 occurs, then I can get round the problem.  But that is rather untidy programming, so does anyone know what is going on?

    Incidentally, the problem does not occur with files that do not require a password to open them.

    Andy C




    Tuesday, April 26, 2016 11:40 AM

Answers

  • Yes, some cache effect. Add code to first delete possible file PasswordTest2.xlsm

    Best regards, George

    • Marked as answer by AndyColRomsey Tuesday, April 26, 2016 3:54 PM
    Tuesday, April 26, 2016 3:37 PM

All replies

  • You say "Immediately". Does it work if you wait a minute or so?
    I suspect the MS Cache hysteria (to speed things up, nope!)

    Kudos for not resorting to an error handler


    Best regards, George


    Tuesday, April 26, 2016 3:03 PM
  • George, thanks for looking at this.  Time does not seem to make a difference, but in checking that, I watched more closely what was happening in File Explorer to see the time / date on the file.  If the file PasswordTest2.xlsm exists already, then I get the error message, but the PasswordTest2.xlsm file also disappears from File Explorer at the same time.  So when I then hit F8 or I run the procedure again, there is no file for it to overwrite and it creates the file.  Clearly it is related to the existence of a password, since this does not happen for files that do not require a password to open them.

    Andy C

    Tuesday, April 26, 2016 3:32 PM
  • Yes, some cache effect. Add code to first delete possible file PasswordTest2.xlsm

    Best regards, George

    • Marked as answer by AndyColRomsey Tuesday, April 26, 2016 3:54 PM
    Tuesday, April 26, 2016 3:37 PM
  • Thanks George - that seems logical.  I will do it that way.

    Andy C

    Tuesday, April 26, 2016 3:53 PM
  • For anyone with the same problem, I found a commendably concise function to check whether the file exists at the Excelguru Help Site.  This is the code that I finally used:

    Sub SaveSecondCopy()

    If FileFolderExists(ThisWorkbook.Path & "\PasswordTest2.xlsm") Then
        Kill ThisWorkbook.Path & "\PasswordTest2.xlsm"
    End If
    ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\PasswordTest2.xlsm"


    End Sub


    Public Function FileFolderExists(strFullPath As String) As Boolean
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Check if a file or folder exists
        On Error GoTo EarlyExit
        If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
       
    EarlyExit:
        On Error GoTo 0
    End Function

    Andy C

    Wednesday, April 27, 2016 7:10 AM