VBA problems with Dir function

[Mark Tabet]

Has anyone else had problems with the VBA Dir function?

I have some code which does the following from an open *unsaved* purchase order template:
1. reads in all the items in the PO
2. opens a shared Excel workbook, sitting on a shared (using workgroups) and containing a collection of thousands of product codes and notes
3. loops through the PO items and checks them against the database items and makes any changes or adjustments as necessary
4. re-formats the PO to fit into the company's style

The location of the shared database workbook was hard-wired in my code - and it worked fine. However we recently changed from the (slow) shared machine to a (fast) NAS Drive and the code stopped working obviously.

Now, I know I can simply hard-code the new location into the code and it will work fine. But I thought it would be better practice to actually check the file is in the expected location, and if not, to give the user a heads-up.

The code is (or should be) simple:

Dim processingDataFile As String
processingDataFile = "L:\processing data.*"

If Dir(processingDataFile) = "" Then
   MsgBox ("Database not found. Please check it hasn't been moved or renamed.")
Else
   Workbooks.Open processingDataFile
End If

The problems are as follows:
If I don't save the purchase order (remember it's an unsaved template at this point), the code throws the error message.
If I do save the PO, the code works fine.
If I open the processingDataFile from the desktop, the code throws the error message.
If I open the processingDataFile from the same instance of Excel as the PO, the code works fine.
If I use one of the two successful methods above to run the code, and then create another PO, but don't save it first, the code works fine!

I am using Excel 365 and the version of the template doesn't seem to make a difference to the outcome. I have also tried using FSO, with no success either.

I know I have three work-arounds - hard-code, save first or open database first - but it should work and it's really bugging me that it isn't working. I was wondering if anyone had any ideas for a solution.


Thanks in advance,

Mark.

 

[John_w]

I discovered many years ago that sometimes this returns an incorrect True value:

If Dir(processingDataFile) = "" Then

So now I always use:

If Dir(processingDataFile) = vbNullString Then

But you can't do Workbooks.Open processingDataFilebecause processingDataFile has a wildcard in it.

 

[Mark Tabet]

I discovered many years ago that sometimes this returns an incorrect True value:

If Dir(processingDataFile) = "" Then

So now I always use:

If Dir(processingDataFile) = vbNullString Then

But you can't do Workbooks.Open processingDataFilebecause processingDataFile has a wildcard in it.

John,

That's brilliant!

I'd already spotted the wildcard problem (although bizarrely, Excel seemed to know what I wanted!). But the

vbNullString

substitution worked a treat!!!

Thank you very much!


Mark.

 

[Mark Tabet]

I discovered many years ago that sometimes this returns an incorrect True value:

If Dir(processingDataFile) = "" Then

So now I always use:

If Dir(processingDataFile) = vbNullString Then

But you can't do Workbooks.Open processingDataFilebecause processingDataFile has a wildcard in it.

I spoke too soon!

    Dim processingDataFile As String
    
    processingDataFile = "L:\processing data.xlsm"
    
    If Dir(processingDataFile) = vbNullString Then
        MsgBox "Could not find the file " & processingDataFile & ". Please save the document and try again."
        Exit Sub
    End If

Doesn't work either