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

The file wasn't actually renamed. But the strange thing is, it opened as read-only when the macro was working on the renamed file, and prompted if i want to over-write the existing copy when I don't see the 'existing' file in my folder. The result was all the updates were not saved.