Excel 2016 for Mac Compatibility Issue with Dir()

Question

When the following statements are executed in a protected Excel 2016 for Mac VBA environment, the Dir function works correctly if the file is found (i.e. the variable test contains the file name).  However if the file is not found, the Dir function raises a Run Time 53 (File Not Found) error rather than returning a zero length string which is the documented action:

   fName = ActiveWorkbook.Path & Application.PathSeparator & "timYY.CSV"

   test = dir(fName)

The function works correctly on PC.

Can this be fixed?

Dir Function

Office 2013 and later

Other Versions

• Office 2010

	Contribute to this content<br><br><br>Use GitHub to suggest and submit changes. See our guidelines for contributing to VBA documentation.

Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

Syntax

Dir[(pathname[, attributes])]

The Dir function syntax has these parts:

Part	Description
pathname	Optional. String expression that specifies a file name — may include directory or folder, and drive. A zero-length string ("") is returned if<br>pathname is not found.
attributes	Optional. Constant or numeric expression, whose sum specifies file attributes. If omitted, returns files that match<br>pathname but have no attributes.

Answer 1

There are a lot of problems with Dir, i create a custum Dir for mac excel.

see this webpage http://www.rondebruin.nl/mac/section3.htm

maybe it is usful

Answer 2

Hi Ron,

I really appreciate the work you have done on work arounds in this area, Ron, but I think this is a clear error in the implementation of Excel 2016 for Mac and should be fixed as quickly as possible.

Is this the correct way to report such a problem?

Tim

Answer 3

I report the problems and I am sure others also in Office 2011 directly to the Excel team. We hoped that when 2016 was released it was corrected but no change like many other VBA stuff that is broken. So I have no hope that they fix anymore.

You can report it by clicking on the smiley icon in the top right of your Excel Window.

Mac Excel will never be Win Excel