Dir() loop changing filenames

Question

I am trying to debug the following code:

Fname2 = Dir(Folder2, MacID("XLSX"))

Do While Fname2 <> ""

Debug.Print "Do While: " & Fname2

Fname2 = Dir()

Debug.Print "Do While: " & Fname2

Loop

The display on the Immediate Window shows the below result.

Do While: AJProjBillv149_donotuse_0712.xlsx

Dir() :APProjBillv149_donot#F9BA0.xlsx

As shown above, the first loop returns the correct file name (AJProjBillv149_donotuse_0712.xlsx). However, after setting Fname2 = Dir(), the filename of the 2nd file was renamed automatically.

How do I resolve this to let it retrieve the right file and not to rename it?

Answer 1

Yes, I had the same problem.  See http://answers.microsoft.com/en-us/mac/forum/macoffice2011-macexcel/help-xl-2011s-dir-function-truncates-filename/e72fbf5d-749c-4a55-a77c-e2def6db24d9

I ended-up using AppleScript to get around the issue for now.  I created a function that returns an array of files found in the path.  I have a separate one to return folders.  This could use some error handling, but I just needed to get it working.

You could add some language to the set list to every file to filter for XLSX files only.  Google Applescript & "every file" and there are example of how to use something like "whose name contains .xlsx".  There may even be a filter for "has the extension" or something like that.  Good luck.

The argument passed to the function needs to be the full path in Apple format ... HardDrive:Users:....

Public Function myFilesystem_GetArrayOfFolderFiles(theFullPath)

thisPathFileArray = MacScript("set list_of_file_names to {}" & Chr(13) & _

"tell application ""Finder""" & Chr(13) & _

"set the source_folder to """ & theFullPath & """ as alias" & Chr(13) & _

"set file_list to (every file in source_folder) as alias list" & Chr(13) & _

"repeat with current_file in file_list" & Chr(13) & _

"set current_file_name to (the name of current_file)" & Chr(13) & _

"copy current_file_name to the end of list_of_file_names" & Chr(13) & _

"end repeat" & Chr(13) & _

"return list_of_file_names" & Chr(13) & _

"end tell")

MyArray = Split(thisPathFileArray, ",")

myFilesystem_GetArrayOfFolderFiles = MyArray

End Function

Answer 2

Excellent beginning to a workaround - I'm going to have to play with this for some of my clients' applications. Fortunately, they don't seem to have been caught by using long file names...  Thanks!

Answer 3

Oh, one more thing .. the filenames in the array tend to have whitespace at the beginning (maybe end too).  I didn't find a clean Applescript function to trim() it, so make sure you trim() the array elements as you use them.

Answer 4

I duplicate this also. Looks like a bug. I'll report it, but there is no telling when or even if it will be fixed.

Answer 5

It's a bug, though your file wasn't actually renamed, was it? Dir()'s result  in XL2011  truncates filenames over 23 characters long. 

Let MS know you want it fixed ASAP using Help/Send Feedback

An alternative is to use Applescript (via the MacScript() method).