Someone else is using the file. Please try again later.

[jkicker]

I'm getting runtime error 1004 because someone else has a shared file open. When I open the file manually, it asks if I want to open it read only. But when VBA opens the file, it says Someone else is using the file. Please try again later. Then I get the 1004 error when I return to VBA. Why can't I just open it as read only?

'heirarchy
    Dim hPath As String, hFile As String
    Dim hwb As Workbook
    Dim hws As Worksheet
    
    hPath = "K:\Account Hierarchy\2019-20\CFC\"
    hFile = hPath & "*A&S CFC Funding Hierarchy.xlsm"
    Set hwb = Workbooks.Open(hFile, ReadOnly:=True)
    Set hws = hwb.Worksheets(1)

I also tried IgnoreReadOnlyRecommended:=True and Notify:=False.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[DataBlake]

Offthelip posted generally what i was getting at
here is my own version of the solution
after creating a temporary file folder, copy/paste this into the "This Workbook" code of the "master file" that everyone is using
the last code is optional if you want to delete the temporary file.
or manually delete it if you need it later/want to make changes while others use the file by deleting the workbook_beforeclose code

if you use the auto delete code PLEASE FOLLOW THESE DIRECTIONS CAREFULLY
after pasting the entire code into "ThisWorkbook" save the workbook as XLSM
THEN SAVE THE FILE IN THE TEMP FOLDER
this code is designed to delete the activeworkbook so if you close the main workbook while this code is in effect it will delete it.
therefore when you save as the will delete the file you saved instead of the main one
hopefully that makes sense, i'm tired.

Private Sub Workbook_Open()
Dim pathA As String, fileA As String, extA As String
Dim fold As String

Application.DisplayAlerts = False
pathA = "C:\Users\username\Desktop\Temp\"
extA = ".xlsx"

fold = pathA & Sheets(1).Name & "-" & Format(Date, "MM-DD-YYYY") & extA

With ActiveWorkbook
    .SaveAs fold, FileFormat:=xlOpenXMLWorkbook
End With
Application.DisplayAlerts = True
End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Dim xFullName As String
xFullName = Application.ActiveWorkbook.FullName
ActiveWorkbook.Saved = True
Application.ActiveWorkbook.ChangeFileAccess xlReadOnly
On Error GoTo Err
Kill xFullName
Application.ActiveWorkbook.Close False
Application.DisplayAlerts = True
Err:
End Sub

if you want to exit excel complete use "Application.Quit" instead of "Application.ActiveWorkBook.Close false" at the end of workbook_beforeclose

edit: fluff strikes again, theirs is probably faster

 

[jkicker]

Offthelip posted generally what i was getting at
here is my own version of the solution
after creating a temporary file folder, copy/paste this into the "This Workbook" code of the "master file" that everyone is using
the last code is optional if you want to delete the temporary file.
or manually delete it if you need it later/want to make changes while others use the file by deleting the workbook_beforeclose code

if you use the auto delete code PLEASE FOLLOW THESE DIRECTIONS CAREFULLY
after pasting the entire code into "ThisWorkbook" save the workbook as XLSM
THEN SAVE THE FILE IN THE TEMP FOLDER
this code is designed to delete the activeworkbook so if you close the main workbook while this code is in effect it will delete it.
therefore when you save as the will delete the file you saved instead of the main one
hopefully that makes sense, i'm tired.

Private Sub Workbook_Open()
Dim pathA As String, fileA As String, extA As String
Dim fold As String

Application.DisplayAlerts = False
pathA = "C:\Users\username\Desktop\Temp\"
extA = ".xlsx"

fold = pathA & Sheets(1).Name & "-" & Format(Date, "MM-DD-YYYY") & extA

With ActiveWorkbook
    .SaveAs fold, FileFormat:=xlOpenXMLWorkbook
End With
Application.DisplayAlerts = True
End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Dim xFullName As String
xFullName = Application.ActiveWorkbook.FullName
ActiveWorkbook.Saved = True
Application.ActiveWorkbook.ChangeFileAccess xlReadOnly
On Error GoTo Err
Kill xFullName
Application.ActiveWorkbook.Close False
Application.DisplayAlerts = True
Err:
End Sub

if you want to exit excel complete use "Application.Quit" instead of "Application.ActiveWorkBook.Close false" at the end of workbook_beforeclose

edit: fluff strikes again, theirs is probably faster

You have a bit of code there, "With ActiveWorkbook". I think it would fall apart there because I wouldn't be able to get the workbook active as my primary issue was opening the workbook, not making a copy of it. That was a work-around.

But Fluff attacked the problem directly. Using Dir to remove the * from inside the variable has stopped the message from popping up and preventing the workbook to open. It's worth noting that I am not asked if I want to open a read-only copy, even thought someone else is in the workbook and no where in the code was it instructed to open read-only; it just opens in read-only automatically. Someone else is in the file right now, I'm trying to test if it opens read-only in all scenarios. I'll update when i can.

 

[DataBlake]

"With ActiveWorkbook". I think it would fall apart there because I wouldn't be able to get the workbook active as my primary issue was opening the workbook, not making a copy of it.

well it would make it so if anyone opened it they are opening a "copy" of it. This is NOT read only so you can still interact with the data etc
the only time you would have an issue is if two people tried to open it at the same exact second which i feel is highly unlikely.

i should point out that they too would need the temp folder and username would have to be swapped for a dynamic username such as "application.username"

 

[jkicker]

well it would make it so if anyone opened it they are opening a "copy" of it. This is NOT read only so you can still interact with the data etc
the only time you would have an issue is if two people tried to open it at the same exact second which i feel is highly unlikely.

i should point out that they too would need the temp folder and username would have to be swapped for a dynamic username such as "application.username"

But at what point did the workbook become active? My sub resides in a different workbook and is trying to copy info from this shared workbook that wouldn't open before.

 

[DataBlake]

But at what point did the workbook become active? My sub resides in a different workbook and is trying to copy info from this shared workbook that wouldn't open before.

yes but your sub still opens a workbook.
my sub would go in the
A&S CFC Funding Hierarchy.xlsm

so when you open the workbook (by macro or otherwise) its opening a copy. this would go for others that are also using the file with their own temp folder
so lets say employee A opens the workbook without a macro
Employee B open the workbook using a macro
and Employee C went on lunch with the workbook open on their desktop

Everyone can still access the original file because Employees A,B,C have "copies" of the workbook open.
but like i said if Fluff's solution works for you then thats that, but if you were wanting a different solution (like mine) you would have to have a temp folder on everyone's PC who is using the file at the same time and change "username" to "application.username"

OR

create a dynamic save as name with a shared temp folder

 

[jkicker]

yes but your sub still opens a workbook.
my sub would go in the
A&S CFC Funding Hierarchy.xlsm

so when you open the workbook (by macro or otherwise) its opening a copy. this would go for others that are also using the file with their own temp folder
so lets say employee A opens the workbook without a macro
Employee B open the workbook using a macro
and Employee C went on lunch with the workbook open on their desktop

Everyone can still access the original file because Employees A,B,C have "copies" of the workbook open.
but like i said if Fluff's solution works for you then thats that, but if you were wanting a different solution (like mine) you would have to have a temp folder on everyone's PC who is using the file at the same time and change "username" to "application.username"

OR

create a dynamic save as name with a shared temp folder

So i can put your sub in any workbook and anyone who opens it will only open a copy. Neat. I will save that for later. Another question though: how?
How does the macro run automatically upon opening the workbook, so that only a copy is opened?

 

[DataBlake]

So i can put your sub in any workbook and anyone who opens it will only open a copy. Neat. I will save that for later. Another question though: how?
How does the macro run automatically upon opening the workbook, so that only a copy is opened?

using an event like workbook_open
So if you paste the event code in "This Workbook" rather than "Module 1" it will do whatever you want with the workbook rather than regular subs/macros.
here is a link with info

https://www.myonlinetraininghub.com/workbook-worksheet-events-excel-vba

if you ever need help with understanding how to change my code to dynamically save just @ me