error 5174 Sorry, we couldn't find your file. Was it moved, renamed, or deleted?

This discussion is for those writing macros using VBA with Word, Excel, etc and have faced this error when executing your macro:

Documents.Open (..)

error  5174 Sorry, we couldn't find your file. Was it moved, renamed, or deleted?
 ("drive letter:\...\filename...")

I have seen a lot of posts around this error. There are 2 main scenarios, and the easiest one is the only one that is getting discussed.

Scenario 1

You are trying to open a "path\file" name that has actually moved between the time your program started and came up with the "path\file" name value, and the time it actually tried to open the file. That is easy to fix.

Scenario 2

THE MOST IMPORTANT ONE AND MOST DIFFICULT TO DETECT

The value you are using to designate the "path\file" name to open using your macro code such as:

            Documents.Open (....

...said value contains non printable characters (I am not talking about non alphanum like "." or "&, or characters not allowed, I am talking about ASCII 13 most likely being the last character at the end of the value for your filename. But when you use Debug.Print, that character is not printed.

Steps To Confirm:

When you are in debug mode, Debug.Print "the value of the path\file you are trying to open".

This will print as expect with no visible issues, you can then confirm the file exists by copy pasting the printed value in windows explorer and the file should open automatically. If it does open manually, then you are likely dealing with scenario #2

THEREFORE

while in debug mode, using the variable that contains the path\file vallue, write a FOR statement to iterate through each character of the string using Debug.Print Asc(<>), where <> is the variable in the FOR iteration that gets assigned 1 character from the path\file string at a time. Most likely you will see the last character will print as 13

The VBA function

            Trim(....

does not take care of this problem. you need to use the 

             Replace (...

function with "Chr(13)" as the value sought to be replaced with vbNullString

There are some hacks that consist in adding ASCII char 13 (carriage return, without a line fee) to file names and then appending the name, etc

There are utilities that clean those issues up, maybe not completely?... this might be the cause.

Another possible cause in my radar is the DOS prompt adding an extra char 13 when redirecting the output of a DIR command using ">>"  into a file to create a list of files from the DIR command. It does not happen for all files in the results of the DIR command, it could be for files having special characters that are allowed. I do not have the time to investigate that further.

 

Discussion Info


Last updated November 30, 2019 Views 605 Applies to: