VBA - Dir function not working

[MisterProzilla]

Hi there,

This is driving me nuts! In my current workbook I have a number of references to another workbook. In the Autpen sub, I want to check that the filepath for that external doc is still valid, and return an info message if not so the user can update the links if necessary. As I understand, I should be able to use the Dir function to look for the filepath - if it finds the file, the value returned will be non-blank, if it can't find a match, the value will be blank.

I've tried this code:

'set filepath as last known location in cell D54
Dim filepath as string
filepath = Sheet3.Range("D54").Value

If Dir(filepath) = "" Then
    MsgBox "File not found - check references"
    Exit Sub
End If

But it always returns a blank. I've tried every permutation I can think of: with and without square brackets around the filename; writing it manually into VBA rather than getting it from a cell value; tried nesting the Dir into a Len() function to check the string length instead; I've tried the Workbook.Open function using the same filename, both from a cell and written manually, and it works absolutely fine both ways. So it seems the filepath is valid - I don't understand what's going wrong with the Dir function.

Hope someone can help

 

[RoryA]

What exactly is in D54?

 

[MisterProzilla]

The filepath stored from the last time the links were successfully established. Currently that is:

C:\Users\mrperson\Desktop\WLP system v2.4 (Sharepoint ready)\WLP\Template WLP 18-19.xlsm

(changed my username obviously)

The filepath is definitely valid, as it's used to establish the links to the external file. Dir just doesn't like it for some reason I can't fathom.

 

[MisterProzilla]

I've managed to find a workaround: trying to open the target file and close without saving, and if the file can't be found in order to open, you can deal with that in the error handler to return the message:

Sub Auto_Open()

Application.ScreenUpdating = False
On Error Goto ErrHandler

'try to open & close WLP file object to check for valid filepath
Set TemplateWLP = Workbooks.Open(Sheet3.Range("D54").Value)
TemplateWLP.Close savechanges:=False




'if opened and closed without error, release the object and proceed with link refresh
Set TemplateWLP = Nothing
Call TemplateLinkProcess


Exit Sub


ErrHandler:


Set TemplateWLP = Nothing
MsgBox "Links to the Template WLP may be corrupt." & vbLf & vbLf & "Please re-establish the Template links using the button in the Staff Grid page before linking to any staff WLPs.", vbOKOnly


End Sub

It's a bit of a hack job but it works Sod Dir()