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]

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!!

Sub Macro1()

    Dim hPath As String, hFile As String, hFile2 As String
    Dim hwb As Workbook
    hPath = "K:\Account Hierarchy\2019-20\CFC\"
    hFile = hPath & "*A&S CFC Funding Hierarchy.xlsm"
    hFile2 = hPath & "temphiercopy.xlsm"
    
    FileCopy hFile, hFile2
    Set hwb = Workbooks.Open(hFile2)
    hwb.Close savechanges:=False
    Kill (hFile2)
    
End Sub

i didn't see "Microsoft Forms" in the reference list, but when i typed FileCopy the yellow dialogue box came up depicting the arguments needed. However I'm getting "bad file name" when the sub gets to the FileCopy line. The name of file changes with the date that it was updated, hence the asterisk (hFile = hPath & "*A&S CFC Funding Hierarchy.xlsm"). When i hover the mouse over hFile, the path it shows is the same as when I use the .open(hFile), and hFile opens just fine.

 

[jkicker]

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

What part of this makes a copy of the file? Is it "tte =" ? We don't dim?

 

[offthelip]

it doesn't do a file copy I asumed you could work that out from my previous post, this is what you need , you do need to set "yoursourcepath" to whatever your source file path is;

ut = Application.UserName
tte = "C:\Users\" & ut & "\Documents\Tempfolder\tempexcel.xls"
Filecopy  (yoursourcepath,tte)/CODE]

I don't usually "dim" any variables unless I need to e.g with the fso object in the delete routine.
This is because most of the variables I use are clear as to what they are and using a dim statement doesn't clarify the code it just clutters up the top of a module, with lots of lines of code.
Note the file path I have used is valid for win 7, I haven't checked it on my win 10 machine.

 

[jkicker]

it doesn't do a file copy I asumed you could work that out from my previous post, this is what you need , you do need to set "yoursourcepath" to whatever your source file path is;

ut = Application.UserName
tte = "C:\Users\" & ut & "\Documents\Tempfolder\tempexcel.xls"
Filecopy  (yoursourcepath,tte)/CODE][/QUOTE]

Ok good, i did put that together I just wanted to make sure I didn't miss something. So what you suggested here is essentially what I have. I'm the only user so I don't need to make a temp file in the user profile, I can just do it in the same folder with a different file name, which is what i have here:
[COLOR=#333333]Sub Macro1()[/COLOR]
    Dim hPath As String, hFile As String, hFile2 As String
    Dim hwb As Workbook
    hPath = "K:\Account Hierarchy\2019-20\CFC\"
    hFile = hPath & "*A&S CFC Funding Hierarchy.xlsm"
    hFile2 = hPath & "temphiercopy.xlsm"
    
    FileCopy hFile, hFile2
    Set hwb = Workbooks.Open(hFile2)
    hwb.Close savechanges:=False
    Kill (hFile2)
     [COLOR=#333333]End Sub

As i mentioned, I'm getting a "bad file name" and i suspect it is because of the asterisk. Do you know if that may be true?
[/COLOR]

 

[offthelip]

asterisks are invalid see:
https://docs.microsoft.com/en-us/windows/win32/fileio/naming-a-file

 

[jkicker]

asterisks are invalid see:
https://docs.microsoft.com/en-us/windows/win32/fileio/naming-a-file

Thank you. So this method won't work until i get the full file name in another variable. how do i do that without opening the file? It is the only excel file in the hPath destination folder (there is another folder inside however with the archive of all past files).

 

[Fluff]

How about

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

 

[jkicker]

How about

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

This worked. I'm in awe. Could you explain what happened?

 

[Fluff]

This line

hFile = Dir(hPath & "*A&S CFC Funding Hierarchy.xlsm")

gets the actual file name, so you are not trying to use a wildcard, which maybe what solved the problem.
You could try changing

    Workbooks.Open hPath & hFile
    Set hwb = Workbooks(hFile)

to

    Set hwb =Workbooks.Open( hPath & hFile)

not sure if it will make any difference and I have no way to check.
The code I posted is what I used a long time ago using xl2003, so I thought it might still work.

 

[jkicker]

This line

hFile = Dir(hPath & "*A&S CFC Funding Hierarchy.xlsm")

gets the actual file name, so you are not trying to use a wildcard, which maybe what solved the problem.
You could try changing

    Workbooks.Open hPath & hFile
    Set hwb = Workbooks(hFile)

to

    Set hwb =Workbooks.Open( hPath & hFile)

not sure if it will make any difference and I have no way to check.
The code I posted is what I used a long time ago using xl2003, so I thought it might still work.

You're probably right. Now how do we get this solution to the front page of google? I searched so many pages related to the title of this post, didn't find one instance of Dir

Thanks for your help everyone.