none
Workbooks.Open inconsitent behaviour RRS feed

  • Question

  • Hello,

    I have a VBA code that opens a ".xlsm" file stored on our corporate Share Point, then saves it in a folder on users computer.

    The code that opens the file is:

    Workbooks.Open Filename:="https://<mySharePointFolderPath> & "/" & <myFileName>"

    There are 60+ users in the team and the code worked fine for everyone, except one person. Every user is part of a Security Group which has "Can Edit" access rights to the folder. Initially I thought it's to do with that user's network as they were working from home that day. But when they tried the same in the office the code would continue to fail on the above line.

    The error message says:

    "Run-time error '1004':Excel cannot access the file. There are several possible reasons: * The file name or path does not exist. * The file is being used by another program."

    I am quite sure none of these applies in our case.

    So I started testing this, and somehow, I made it fail for me as well. I can't remember exactly, but it either failed when the file was not in the SP folder or when it was already open locally and I tried to open it again. The most important thing is that it now fails for me as well, every time. It still works fine for everyone else though, (apart from that one user and me), so it makes me think it's a local machine/user issue rather than Share Point issue.

    Moreover, when I modify the code to:

    Workbooks.Open Filename:="\\<mySharePointFolder> & "\" & <myFileName>"

    it takes much longer to resolve, but it actually DOES open the file. However, for some reason it still produces the same error message, even though the file open OK.

    Can anyone advise please on why such behavior and how to fix this?

    Thank you,

    J.


    Tuesday, February 27, 2018 6:18 PM

All replies

  • Hello JK_2017,

    >> it either failed when the file was not in the SP folder or when it was already open locally

    What do you mean when the file was not in the SP folder?

    How do you open the file locally? Could you manually open the file in SP folder directly from Excel?

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 7:02 AM
  • Hi Terry,

    Sorry for not making it clear previously, let me explain better and then add a few things.

    I tested number of different scenarios trying to understand what may be causing the Workbooks.Open method to fail. Initially I could not reproduce the fail, but then I went wild with altering the conditions and somehow, managed to cause the fail. I wish I had been more patient and tested the different conditions one at the time. But I hadn't and now I can't remember exactly what caused the fail. It was either when I removed the file from my Share Point folder but still tried to open it with the Workbooks.Open line OR when I had the file already open on my machine, yet tried to open it again from SP. I am the author of the file and I have a copy of it locally on my laptop. Since that first fail for me, I am no longer able to open that particular file with Workbooks.Open method (same as that one other user).

    To add, I tested this now with the other affected user and we are both able to:

    1. Open the file from a local folder.

    2. Go to SP folder (via internet browser), download the file and open it.

    3. Open other ".xlsm" files in that folder with Workbooks.Open method.

    4. Open the ".xlsm" file in question with Workbooks.Open after it has been renamed (i.e. from file1.xlsm to file2.xlsm)

    I believe the above point 4 proves that the issue is not with the file itself, but with the fact that Excel somehow remembers that it once failed trying to open a file with this name via Workbooks.Open method and it fails every attempt since.

    Also, I believe it's not a user-specific issue because I just tested this on a different laptop and it worked fine for me.

    And finally, to answer your last question - opening the file on SP via Excel (so going to File/Open/SharePoint Sites, etc.) gives me and the affected user the following error message:

    "No items to show - We can't open this workbook. It's set to show only certain named items, but they aren't in the workbook. You might want to contact the author for more information."

    So yes, the workbook DOES have some named ranges which are hidden and other named ranges that are visible but refer to xlVeryHidden worksheets. But they all ARE in the workbook. If this was the cause of the issue, then it should not work for other users of after simply renaming the file, right.

    Hope this sheds some more light on the issue and you are able to suggest a solution?

    Thanks,

    J.

    Wednesday, February 28, 2018 2:47 PM