CODEWITHSUNDEEP
vbams-word
Noir
Noir

Reputation: 171

Word VBA - Documents.Open FileName not working

I have this code (this code is a part of a larger stack, but this is clearly what is not working.

Sub clearup()

Documents.Open FileName:="D:\test"

End Sub

test is a .doc file. I know test.doc is there as I created it, to be where it is. But if I run the macro I get "Sorry, we couldn't find your file. Was it moved, renamed, or deleted?". I also tried to change the name to test.doc to no avail. Any idea what I am doing wrong?

Upvotes: 1

Views: 8085

Answers (3)

Fr4nc01s
Fr4nc01s

Reputation: 59

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.

Upvotes: 0

Noir
Noir

Reputation: 171

I have figured out the problem, the first part of the issue was that I didn't use .docx ending (I didn't notice as the doc had no ending after its name), very much in line with the suggestion of AJD and what also threw me off track, was that the document has opened but I did not notice, so I started receiving an invalid name error, not knowing why.

Thanks for your suggestions.

Upvotes: 0

Jagrut Sharma
Jagrut Sharma

Reputation: 4754

You just need to specify the full file name with extension.

Sub clearup()

Documents.Open FileName:="D:\test.doc"

End Sub

You may have to grant permissions, but then it should open.

Updated:

To help in debugging the issue, you can take a look at the additional parameters the Documents.Open method accepts.

For example, to use the OpenAndRepair parameter:

Documents.Open FileName:="D:\test.doc", OpenAndRepair:=True

Upvotes: 2

Related Questions