SixSigmaGuy
commented
6 years ago This is good stuff
Sent from my stupid Android phone using Microsoft Outlook which wasn't written by Microsoft but, rather, a third party and is very buggy. Please bring back the Windows Phone!! Best phone ever!!
On Thu, Aug 2, 2018 at 1:51 PM -0700, "Fr4nc01s" notifications@github.com wrote:
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.
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.
Fr4nc01s
lindalu-MSFT
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.