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.

 

[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.

I guess my question is: how do I open this file when someone else is in it?

 

[jkicker]

Any reason I'm not getting a response? 54 views. Usually someone responds. Does this need more detail or clarity?

 

[offthelip]

Have you tried:

application.displayalerts=False
    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)
    application.displayalerts=True

 

[jkicker]

Have you tried:

application.displayalerts=False
    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)
    application.displayalerts=True

Thanks offthelip. Tried it now. Did not work. All it did was prevent the "someone else" message and took me straight to error 1004.
Basically I get 2 different messages: one when i open the workbook manually, and one when i open it with VBA. if i could get the manual message to show up when opening with vba, then the read only command will take effect. but when VBA opens the file, it is in use and doesn't open.

Any other suggestions/ideas? i'm watching this thread obsessively.

 

[DataBlake]

i think in this instance it would be best to open a copy of the workbook if you have multiple users using the same files.
I.E make a macro/button to open copies of files, and if someone is making changes to the file they can save over the original or whatever you want to do.
its kind of a plug n play approach to file sharing

or use workbook.open read only if no changes are being made
https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open

 

[offthelip]

My only other suggestion is to create a copy of the file in VBa using filecopy before opening it:

Filecopy  (sourcepath,destpath)

I think you will need to set a reference to Microsoft forms to use the function.
Don't forget to delete it when you have finished!!

 

[jkicker]

i think in this instance it would be best to open a copy of the workbook if you have multiple users using the same files.
I.E make a macro/button to open copies of files, and if someone is making changes to the file they can save over the original or whatever you want to do.
its kind of a plug n play approach to file sharing

or use workbook.open read only if no changes are being made
https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open

So i don't need to make changes to the workbook, I just need to copy data from it. I have a copy on my desktop that I am using as a temporary solution, but it gets updated pretty regularly (it's an account heirarchy). I've tried wb.open read only, and when i open the file manually i get the option to open it read only, but when VBA tries to open the file it just says someone else is using it and to try again later. Opening a copy of the file would work fine. Can you explain how/what you mean?

 

[jkicker]

My only other suggestion is to create a copy of the file in VBa using filecopy before opening it:

Filecopy  (sourcepath,destpath)

I think you will need to set a reference to Microsoft forms to use the function.
Don't forget to delete it when you have finished!!

Delete the reference or the extra file? Can you show me the delete code? i don't want a whole bunch of copies of this file every time i run this sub (thousands of times).

 

[offthelip]

I would copy the file to a temp location on the users machine. Using code like this for the pathname :
( note I am using win 7)

ut = Application.UserName
tte = "C:\Users\" & ut & "\Documents\Tempfolder\tempexcel.xls"
MsgBox tte

The delete the file at the end when you close the workbook using code like this:

 Dim FSO  '  Dim sFile As String
        
    'Source File Location
    ut = Application.UserName
    tte = "C:\Users\" & ut & "\Documents\Tempfolder\tempexcel.xls"
    
    'Set Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    'Check File Exists or Not
    If FSO.FileExists(tte) Then
    
        'If file exists, It will delete the file from source location
        
       FSO.DeleteFile tte, True
        success = True
        
    Else
    
        'If file does not exists, It will display following message
        'MsgBox "File Not Found", vbInformation, "Not Found!"
        success = False
        
    End If