'Unable to read file' error

[picklefactory]

Hi folks.
I've started to get this error with one specific wb that we've been running for over 12 months trouble free. Online search has come up with numerous possible reasons for this, but none that seem to fit my circumstances, just wondering if anyone here may have some further knowledge? I'm not sure if it's more a Microsoft issue than Excel, but the fact that it only seems to affect one wb makes me wonder if there's something in that one contributing to the issue.
Also, I've run the wb on 4 different machines, 3 running Office 2016 all see the error, the one machine running Office 365 is error free, the wb is on a shared network, so it's the same wb being run by all.
OK, what the wb does is opens an .xls sheet via VBA, which is an exported .xls from our SAP system, it then opens a 2nd .xls which is purely a flowery display version of the final output data to run in a scrolling .ppt in the main office (The ppt kept glitching with the .xlsm, and would only work with an xls). It just juggles and re-formats the data from the SAP export file and then transfers it to the display xls in a nice, visual format. It also, based on the contents, sends a reminder email out to selected people that there are outstanding issues within the data.
So nothing particularly clever, and has worked perfectly for over 12 months.
I should emphasise though, that I get the 'Unable to read file' error every time, but when I hit the OK button on the error, the wb then carries on and completes all it's functions without further issue.
I'm wondering if there might be a compatibility issue within my code maybe, between the Office versions? Windows and Office seem to update almost weekly, so I'm also wondering if a recent update may have had some effect.
All code enclosed. I have a basic module just setting print area and the major stuff is all in an 'On workbook open' UDF
Any suggestions I could try please?

Sub setprintarea()

Dim myrange As String

myrange = Range("R2").Value

ActiveSheet.PageSetup.PrintArea = "$A$1:J" & myrange

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
    ActiveSheet.Unprotect ""
    
    If ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
    End If
    
    ActiveSheet.Protect "", AllowFiltering:=True
    
End Sub

Private Sub Workbook_Open()
    
    Application.ScreenUpdating = False
    
    ActiveSheet.Unprotect ""
    
Set wb = Application.Workbooks.Open("M:\COMPANY SHARED\POWERPOINT NOTICE BOARD\LASER BEND ORDERS.xls")
Set wb1 = Application.Workbooks.Open("M:\COMPANY SHARED\POWERPOINT NOTICE BOARD\OUTSTANDING ORDERS DISPLAY.xls")

Workbooks("CHASE OUTSTANDING ORDERS.xlsm").Activate

    Workbooks("CHASE OUTSTANDING ORDERS.xlsm").Worksheets("Sheet1").Range("A2:J30").Copy
    Workbooks("OUTSTANDING ORDERS DISPLAY.xls").Worksheets("Sheet1").Range("A2:J30").PasteSpecial Paste:=xlPasteValues


wb.Close
wb1.Close savechanges:=True

    
'GoTo Skip

Dim result As String
If (WorksheetFunction.CountIf(Range("'Sheet1'!I2:'Sheet1'!I20"), "Yes")) = 0 Then
    Exit Sub
    Else:
    
Dim OutApp As Object
 Dim OutMail As Object
 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)

With OutMail
.To = "user1@email.co.uk;"
.CC = "user2@email.co.uk;"
.Subject = "ITEMS ARE OVERDUE ON FABRICATION ORDER SCHEDULE"
.Body = "PLEASE CHECK PROGRESS SHEET FOR DETAILS M:\COMPANY SHARED\POWERPOINT NOTICE BOARD\CHASE OUTSTANDING ORDERS.xlsm"
.NoAging = True
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
SendKeys "{NUMLOCK}%{s}", True

End If


Skip:

ActiveSheet.Protect ""
Application.ScreenUpdating = True

End Sub

 

[sykes]

Couple of things...
1. Is it possible that on some machines, your log-on hasn't got your "M" drive mapped, or mapped correctly?

2. It looks as if you're opening a workbook ("wb" i.e. "Laser Bend orders"), not doing anything with it, then closing it down again:

Set wb = Application.Workbooks.Open("M:\COMPANY SHARED\POWERPOINT NOTICE BOARD\LASER BEND ORDERS.xls")
Set wb1 = Application.Workbooks.Open("M:\COMPANY SHARED\POWERPOINT NOTICE BOARD\OUTSTANDING ORDERS DISPLAY.xls")

Workbooks("CHASE OUTSTANDING ORDERS.xlsm").Activate

    Workbooks("CHASE OUTSTANDING ORDERS.xlsm").Worksheets("Sheet1").Range("A2:J30").Copy
    Workbooks("OUTSTANDING ORDERS DISPLAY.xls").Worksheets("Sheet1").Range("A2:J30").PasteSpecial Paste:=xlPasteValues


wb.Close
wb1.Close savechanges:=True

I think you could ditch these lines.

 

[picklefactory]

Thanks Sykes.
M: is mapped, everything works and completes after I OK the error message. If the drive wasn't mapped it would fail to complete, as it wouldn't be able to access the file at all, but it does.
Re: opened wb's, sorry, I should have explained further, in the .xlsm it is primarily just links to ranges within the .xls, so they all just need to be open so the .xlsm can update the links, it just transfers the cell values from one to another then closes it down again.
Apologies for not stating that.

 

[steve the fish]

What does 'run the wb' mean? When is this error occuring? If you do the same thing manually, ie not with code, do you get an error?

 

[picklefactory]

Steve
'run the wb' means I've opened it on 4 different pc's in our office. The 3 pc's running Office 2016 all generate the error, but I click OK on the error box and the wb carries on and completes perfectly. The one pc we have running Office 365 does not generate the error at all and all just runs seamlessly, which would imply to me that the code is all OK, and it might be an Office issue.
I'll try without the code and feed back

 

[picklefactory]

That made a difference.
I manually opened both of the .xls sheets (Source and target) and then ran the .xlsm with those already open and got no error message.
Could it be a speed/synch issue, that maybe one or both are not opening quickly enough for the links to update?
Maybe I should copy/paste the data from the source .xls to the .xlsm via VBA rather than using cell formulas to update?

 

[sykes]

Hi
Just got back online, but it looks as if you're getting to the bottom of this, anyway. If you've not already solved it, I'd say that you're thinking that the .xls files aren't opening in time, before the rest of the code is trying to execute, is a good shout.
Instead of writing too much more VBA, you could troubleshoot it, by adding a "wait" command into your code, which may just give the other files time to come on line.
There's loads of code online for this, but here's one of the MS help pages, with the idea:
https://docs.microsoft.com/en-us/office/vba/api/excel.application.wait

 

[steve the fish]

I never noticed it was a workbook_open event. I never run code in an open event other than to call a macro. Place all your code in a macro in a standard module then call that macro from the open event.

 

[picklefactory]

sykes/steve
Thank you again both. I haven't solved it yet..... lost my temper with it and gave up for a bit
Both of your suggestions sound very promising, when I get a minute later, I'll try them both and feedback. Even if they work, I'm still baffled as to why it's worked perfectly for about 18 months, and suddenly started failing in the last few weeks.
Cheers

 

[picklefactory]

Bugger!!!!

Neither worked. Tried placing the wait function in various places, but the 'Can't read file' error seems to occurs before it even tries to open either of the .xls files. I placed it before the 'link' section, then between the two .xls open lines, and also right at the start before it opens anything.... no difference.

I then moved the lot to a standard module and ran that from the ' On workbook open' command....... exactly the same.

However.... it was still a valuable exercise, as I then went on and disabled the whole VBA shebang entirely and simply opened the .xlsm with nothing functioning...... still got the error, so it's not the VBA, The only thing I think it can be is that the .xlsm is simply a whole sheet of 'links' to the other two workbooks, so I'm guessing it's trying to update those links on open, but it can't as they are not open at that point, hence once I OK the error message, they ARE then open and it all works.

What I mean by links is simple formula in each cell. It just checks for blank cell and if not blank, = .

=IF('M:\COMPANY SHARED\POWERPOINT NOTICE BOARD\[LASER BEND ORDERS.xls]Sheet1'!C6="","",'M:\COMPANY SHARED\POWERPOINT NOTICE BOARD\[LASER BEND ORDERS.xls]Sheet1'!C6)

Now that issue makes perfect sense to me BUT how the heck has it worked perfectly for so long and why has it failed only recently????

I'll change the sheet to copy/paste the data rather than the formulaic links and see if that solves it, I think it will....... but....


PS. Just another thought..... could it just be that something has changed in terms of generating the error message. Previously if it couldn't update links..... no error message, and just does nothing until the links are available, but now something has changed so that it now does display an error if it can't update?