none
Workbooks.Open (Readonly:= true) gives password or read only popup at random times RRS feed

  • Question

  • Dear Excel Developers,

    I have a problem with the Workbooks.Open command in my code, it gives a Popup that let me choose to open it Readonly or not. But this popup pops up at random times.

    i copy some date from one workbook to another every 5 minutes, here is my code:

    Public FileLocation As String
    Public FileName As String
    
    
    Sub AutoRefresh()
        Application.OnTime Now + TimeValue("00:05:00"), "Main"
    End Sub
    
    Sub Main()
       
        Call Import
        Call AutoRefresh 'loop
        
    End Sub
    
    
    Sub Import()
    FileLocation = Sheet1.Range("E3")
    FileName = Sheet1.Range("E4")
    
    'On Error Resume Next
    
    Application.ScreenUpdating = False ' turns off screenupdating
    Application.DisplayAlerts = False
    
    Dim MyWB As Workbook
    
    Set MyWB = ActiveWorkbook
        
            Workbooks.Open (FileLocation & "\" & Filename), ReadOnly:=True
            Workbooks(FileName).Activate
            
            Sheets(1).Cells.Columns("A:H").Copy
            MyWB.Activate
            MyWB.Sheets(2).Range("a1").PasteSpecial
            MyWB.Sheets(2).Range("a1").Select
                
            Workbooks(FileName).Close (False) 'doesn't save changes
    
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True ' turns on screenupdating
    End Sub
    

    It looks like the popup comes up when somebody is in the file or just used it.

    But i don't understand why it doesn't just open the file in read only mode at all times...

    Tuesday, October 13, 2015 11:28 AM

All replies

  • Hi Cepediana,

    >>it fails at random times.. Don't know what i can do about that popup.<<

    What kind of popup did you mean? Would you mind sharing the detail error message with us so that we can narrow down this issue?

    >>Workbooks.Open (Readonly:= true) gives password or read only popup at random times                                  <<

    Did you want to open a document which was protected? If I understood correctly, we can specific password in workbooks.open method.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 2, 2015 5:20 AM
    Moderator
  • Hi Cepediana,

    I am still not able to reproduce this issue. After I specific the correct password, the workbook was opened successfully every time.

    Please try to create a new workbook and reproduce this issue with the new workbook to see whether the issue is relative to the specific workbook.

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, October 14, 2015 5:39 AM
    Moderator
  • Hello Fei Xue, thank you for your replies.

    The workbook I want to copy from is only Password protected to modify, so read only should be possible without a password. But in my case even when I specific the correct password it comes with the popup to enter the password after some time.

    I'm following your troubleshoot now. I created a new password protected worksheet with some data to copy from in read only mode.

    I think this will work just fine cause no one els is using this worksheet.  It looks like I get the popup when somebody  overwrite the file i need the data from.

    Wednesday, October 14, 2015 6:47 AM
  • Hi Cepediana,  

    >>I think this will work just fine cause no one els is using this worksheet.  It looks like I get the popup when somebody  overwrite the file i need the data from.<<

    When the workbook is used by other program, this prompt will not show if we specific the readonly to true.

    What's version of Excel are you developing with? I suggest that you also try to repair/update the Office application to see whether it is helpful.

    >>I created a new password protected worksheet with some data to copy from in read only mode. <<
    Can you reproduce this issue? If not, I also suggest that you try to save the workbook with ReadOnlyRecommended=false. You can refer to figure below save the workbook without displaying a message when the file is opened, recommending that the file be opened as read-only:

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 15, 2015 5:58 AM
    Moderator