Check if file is already open...don't open if file true - https://

[DJDRU]

Hello everyone,

I have a workbook (UserFile) that when opened, updates based on a file stored on a Sharepoint site (DirectoryFile). This refresh macro can be called in the workbook by the user as well. There is also a survey in the UserFile that when submitted, writes to a table in the DirectoryFile, saves and closes it. The DirectoryFile, depending on the network is only accessed for a few seconds. If the DirectoryFile is already opened by another user, whether they are an admin updating the Directory File, or someone else is submitting a survey, I do not want the user (UserFile) to be given the option to "read only" or to see who is actively using the workbook. I searched around and found this function to accomplish the task when the file is in use, but I can only get it working on a file stored locally ("C:\Desktop\DirectoryFile.xlsx") and not the https:// path I am referencing.

The (DirectoryFile) can be read only on update/refresh but I want to make it completely unavailable if (Userfile) is submitting a survey which is a separate macro.

Any assistance is greatly appreciated.

-Curtis

I was getting an error 52 when plugging in the path "https://. . . /DirectoryFile.xlsx"

Please Login or Register  to view this content.

[Kenneth Hobson]

Does this not work for you?

Please Login or Register  to view this content.

[DJDRU]

Thanks for the response.

I am not having any luck with it though. Not sure where to call it. I’ve attached a portion of my macro that will produce the “file in use” prompt.

Please Login or Register  to view this content.

[bakerman2]

Try this way.

Please Login or Register  to view this content.

[LJMetzger]

There are two issues here which may or may not be the same:

a. Is Workbook Open? (code from Bakerman2 in post #4). The workbook will appear to be open only if the User is using one instance of Excel and the file is open in that instance of Excel. If using multiple instances of Excel on the same computer, each instance has no knowledge of other instances (unless code exists to access other Excel instances), and if the file is open in another instance of Excel, 'Is Workbook Open' will return False.

b. Is File Open? (code from DJDRU in post #1). The file will appear to be Open if any user on any computer has the file open (error 70 - permission denied).

Lewis

[DJDRU]

Try this way.

Please Login or Register  to view this content.

This is still returning the file in use prompt:

98C677E2-3705-4C05-8FE1-D23F866B2421.png

Goal is to suppress this prompt, and disable any access, read only
or not to this file if it’s being used elsewhere.

[Kenneth Hobson]

My use would be similar to bakerman2's only I would pass the full path. Unless it is a SharePoint site, I don't see how file use could be checked. Most web sites would open as ReadOnly like dropbox and onedrive.

I guess you can try an Application.DisplayAlerts=False but it would probably open as readonly if it worked at all.

Please Login or Register  to view this content.

[LJMetzger]

I have no experience with SharePoint, but I found the following code (untested) which may be applicable using:
a. CanCheckOut (seems to be unreliable). It is recommended to use .Checkout instead, and if .Checkout fails, then the file is open to someone else.
b. CheckOut
c. CanCheckIn
d. CheckIn

Please Login or Register  to view this content.

Lewis

[DJDRU]

I have no experience with SharePoint, but I found the following code (untested) which may be applicable using:
a. CanCheckOut (seems to be unreliable). It is recommended to use .Checkout instead, and if .Checkout fails, then the file is open to someone else.
b. CheckOut
c. CanCheckIn
d. CheckIn

Please Login or Register  to view this content.

Lewis

Working!!!! Thank you all for the help!

Curtis

Please Login or Register  to view this content.