VBA Issue. Opening a file on the network read only when it should be read-write

[BrianG86]

Hi,

I have a code that opens another workbook, pastes some info, then closes and saves.

The issue I have is that it's not opening it properly, so when it goes to save, it gives the user a save as option, as its only opened it in read only.

After a while it then comes up with the 'read-write' option.

It seems as though because of the network I am on, there is a delay, which then opens the file as read only initially.

My code is below, if this doesn't make sence, please let me know and I will try to explain it more.

Sub HeldOrdersForm()
Dim wb As Workbook, twb As Workbook
Dim ws As Worksheet, tws As Worksheet
    
'''''''''''''''''''''''''
If Sheets(2).Cells(1, 1) = "Blank" Then
MsgBox "Please ensure all fields are completed"
Exit Sub
Else
If Sheets(2).Cells(1, 2).Value = "No" Then
MsgBox "Please ensure that the Order Number is 8 digits long"
Exit Sub
Else
'''''''''''''''''''''''''
                    Application.ScreenUpdating = False

Select Case IsFileFree("S:\Quality Team\Clients\JLP\INSIGHT\Tier2Data.xlsx")
Case 0
MsgBox "Your entry was not submitted as the network was busy, please try again."
Exit Sub
Case 1
MsgBox "file not found"
Exit Sub
Case -1
Set wb = Workbooks.Open("S:\Quality Team\Clients\JLP\INSIGHT\Tier2Data.xlsx", UpdateLinks:=3, WriteResPassword:="Insight1", IgnoreReadonlyRecommended:=True)
Set ws = wb.Sheets(1)
Set twb = ThisWorkbook
Set tws = twb.Sheets(1)
tws.Cells(1, 5).Copy
ws.Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
tws.Cells(10, 5).Copy
ws.Range("C" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
tws.Cells(12, 5).Copy
ws.Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
ws.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = wb.Sheets(3).Cells(1, 1).Value

Application.Wait (Now + TimeValue("0:00:04"))
        Application.CutCopyMode = False
        wb.Close True
        
End Select
End If
End If
                    Application.ScreenUpdating = True
                    
MsgBox "Your entry has been submitted"

End Sub

 

[Johnny C]

May be a bit brute force, but you could set the attribute to read/write before you open it

    Set fsXLobject = CreateObject("Scripting.FileSystemObject")
    Set fXLFile = fsXLobject.GetFile(fsXLobject.GetFileName(strXLFileName))
    fXLFile.Attributes = 32

where strXLFileName is the path and XL filename
This sets the file as readwrite, and flags it for backup to pick up

 

[BrianG86]

Hi Johnny, where in my code would I put that?

 

[BrianG86]

I should mention that, most of the time, when opening a file on the network, it come up with the 'file in use' message box. The problem is, that there is no one in the files. It just comes up saying 'another user'. This is what I think the issue is.

 

[Johnny C]

Hi, you would put it before you opened the file. If you've got a slow network which is what it sounds like, that may force the network to catch up