Weird Characters
By Richard Rost
3 years ago
Weird Characters in Filenames in Access VBA
In this Microsoft Access tutorial, I'm going to teach you how to deal with a problem that may come up when you're working with filenames that have non-standard ASCII characters in them.
Pre-Requisites
Recommended Courses
Notes
- Yes, I know "Richard" is 7 characters. Oops.
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, access vba, vba movefile, vba copyfile, name as, can't rename a file, can't move a file, DOS filename 8.3, chr(160), asc 160
Intro In this video, we talk about a problem that can occur when moving or renaming files using Visual Basic for Applications (VBA), specifically in Microsoft Access, due to weird or non-standard characters in file names. I share my experience troubleshooting a file with a no-break space (ASCII 160) that caused VBA's MoveFile command to fail, explain how to identify problematic characters in file names, and show the workaround of using the DOS 8.3 naming convention to successfully rename the file. This is a developer-level tip for those working with VBA and file handling in Microsoft Access.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
Today's Fast Tip is a developer tip, and it's not so much even a tip. I ran into a problem with a longstanding piece of code. I'm talking about a piece of code I've had working for at least a year without changing anything, and all of a sudden it stops working. It deals with moving and renaming files in Visual Basic, so this is a developer level video.
The problem is I ran into a file name with a non-standard character in it, and VBA just refused to move or rename the file. I'll explain why in this video.
If all that sounds interesting to you, keep watching. If not, I get it. This isn't really as much an Access tip or trick as it is me sharing with you guys who work with VBA and moving files and stuff around what my fix was for this problem, because it took me an hour or two to pull my hair out and figure this out. So watch if you're curious.
You guys said in the last poll you wanted more developer tips like this, so here you go.
So, the root of the problem stems from the routine that I use to import my user list from YouTube. Yes, they have an API for it, but I haven't taken the time to sit down and code for it properly to have my database pull it in automatically. Every business day, Monday through Friday, I go on their website. It's literally two clicks and I download this file. It's a simple CSV file, a comma separated file that's got the usernames, their user IDs, what membership level they're at, and then I import this into my database.
In my database, I tell it where my downloads folder is; I tell it what the file name looks like. It's usually shaped like this: "Your Members" and then some stuff and then CSV. That's the format that it's always been in for the past year, maybe two years since I've been doing memberships.
What I do to make things easier is I simply rename that file to "YouTube.csv" because then I've got that linked into my database as a linked table. There's a linked table that goes right to "C:\\Users\\Amicron\\Downloads\\YouTube.csv." So this routine just renames the file "YouTube.csv" and it makes it much easier to import because now my import routine is just dealing with a linked table. So I can treat it like any other table in the database.
The problem, and it just started a couple days ago, is this: I hit my import routine and I get "File Not Found." I know it's there. I'm looking at it. I can see the file is there. If I debug, here's the actual line of code that renames the file: first, we delete the destination file if it exists, then we check to see if the source file exists, using that criteria and a "Dir" command. "Dir" will basically tell you if a file exists and you can use wildcards. If it doesn't find anything, it says, "Can't find it, you have to go download the file." If it does exist, it slaps together the download folder and FN. Now, the "Dir" command finds the file, there it is, you can see it, and it looks perfectly normal.
But when I try to move that file - which basically is, to rename a file, you use the "MoveFile" command, you move it from one file to another - it throws up "File Not Found." So, "MoveFile" cannot work with this file, and once again I remind you, this code has worked flawlessly for at least a year. Every day for a year, and I haven't changed anything.
Immediately, my thinking is, well, something has changed. Something probably on YouTube's end - they must have changed something.
So let's examine that file name in more depth. Let's take a closer look at that file. First, I tried removing the commas and the underscore character. Still didn't work. Then I tried chopping off "Computer Learning Zone," thinking maybe the file name's too long - still didn't work. Then I chopped off the date and time, and then it worked. So then I brought it back and I tried chopping off just the time and it still worked. So there's something in or around this time value causing the problem.
Now it's time to loop through all of the characters in this file name and take a closer look at them and their ASCII key code values.
I wrote this little bit of code that I inserted. Basically, we've got X's along SSS's string. We're going to go from X equals one to the length of the file name. In other words, we're going to loop down that file name, start at the first character, second character, third character, and so on, and we're going to build a string showing the character at that position and its ASCII key code value at CHR value. For some reason on my computer the "Mid" function shows up like that. I don't know why; it always has.
Then we're going to take a look and see what that looks like when we run it. So now, when I run it, click the button, that's what we get. It's got Y (89), O (111), "Your Members," etc. I remind you what the file name looks like - there it is, "Your Members February 19th 2023..." That looks fine. Let's see, M, AM... it's a little hard to read this way, but let's see. There's the O, there's the underscore which is a 95, O which is character 48. This is on the ASCII key character chart, by the way. Eight... so we got 10 underscore 08, then we got - what's this - 160? That guy doesn't belong. 160. In fact, let me make this a little bit easier to read. Let me put the ASCII code inside of parentheses like this, make it easier for you to read it. Okay, that's easier to read.
There's the AM. Right in front of the AM there's a 160 in there, and that's not a standard character. You can see, like, capital Y is 89; you can Google the ASCII key chart, you'll see what I'm talking about. Every character has its ASCII value, a numeric value, and 160 is not a normal character.
So then I Googled it: What is CHR(160)? Well, that's a no-break space. So somehow, somewhere in YouTube's end, they replaced a normal space character with a 160. I think they did this just to mess with me.
There are a couple things you could do. You could try to use other techniques to rename that file. I thought of maybe running a little batch file to rename it, but then I thought to myself, "If we go old school DOS," remember the 8.3 file name convention? At old school DOS, you could only have eight characters for your file name and then three characters for your extension.
If you created a text file, next document, you could have filename.txt, that was it. If it got longer than that, when they switched to Windows 95, they allowed long file names for the first time, but if you looked at it in DOS, if this was "Ricks Richard's File," that's too long. So what you'd get was, you'd get the first six characters without spaces and then tilde1. And that's how it would look. Underneath, even today with Windows 11, at the operating system level, every file name still looks like that and you can still work with that.
What we have to do is, and this assumes you've only got one of these download files in this folder, if you've got multiple files this isn't going to work, but what you can do is chop off everything but the left six characters and put a tilde 1 after it, and then try to rename that. Again, you have to get rid of spaces.
I gave that a shot because I don't want to have to manually go in here and rename these files every time I download them. So let's get rid of this code because I know what the problem is now.
Then I wrote this lovely bit of code, and yes, make sure you do your commenting because five years from now, I'm going to forget all about this.
First thing we do is replace any spaces in that file name with blank. You've got to ignore spaces. Then we're going to take the left six characters and after it put on a tilde 1 dot CSV. We know the file exists. Now we have to rename it to a DOS 8.3 friendly format. Of course, this also assumes that that character 160 is in a right-side place of that file name; it's not going to be in the first eight characters or first six characters. If it is, then you're just out of luck.
So now, FullFileName equals the downloads folder and whatever file name that is, and now I'm going to put a breakpoint right there to stop the code. When I run it, click, it gets to my breakpoint now, and if I look at the folder, look at that - it did the rename. Because with the DOS 8.3 convention, it was able to find the file and rename it for me. Now I have a solution and I can relax.
This happened when I was on vacation. A couple days ago, the fiance and I took a little road trip up to Disney, and I'm trying to import the day's file because every morning I do my daily stuff, I import my membership, do all that stuff, and it wasn't working. I manually renamed the file and it worked, and I'm like, something's not right. So I got home and I played with it, and that was the solution.
If you want to learn more about this magic file IO stuff, I cover it starting in Access Developer 30. We cover classic VB file IO, reading and writing text files. 31 covers file and folder navigation, copying files, automatically compacting back ends. That's cool stuff. What else do we have? FTPing files... Okay, yeah, I spent a lot of time on this stuff. The File System Object - that's the more advanced stuff that you saw with the MoveFile, and I want to say, yep, File System Object lessons 3 to 4. I have like six classes on working with files. You might think you might not need it, as Access is a self-contained database, but if you do any work with other people's data, whether it's Excel or any other files that they want to give you that you want to import into your database, these lessons are, in my opinion, amazing.
Of course, that's my opinion.
I hope that helps. So, there you go.
I've seen this same thing happen with other types of file names too. If you get file names with characters that aren't allowed in file names, like question marks and such, you can use a similar technique to just use the DOS 8.3 naming convention to work with that file. Again, assuming you only have one of them. If you have two of them, then it could be tilde 2.
If any of you have a better solution for this let me know. Post something in the comments down below. I thought of possibly trying to do an import, importing the lines using VBA read/writes, but I tried this first and it turned out it was easier and it worked.
I have a feeling, though, that file IO reading the file line by line isn't going to work either, because if FSO MoveFile can't work with that file name with the 160 character in it, then I'll bet that trying to read and write that file is going to make problems too. But I didn't waste the time doing it, so go ahead and feel free to try it on your own.
That's it. There's a Fast Tip for today, my developer Access friends. Live long and prosper, and I'll see you next time.Quiz Q1. What specific problem did Richard encounter with moving or renaming files using VBA?
A. The file was too large to move
B. The file had a non-standard character in its name
C. The destination folder did not exist
D. The file was encrypted
Q2. What file format does Richard download daily from YouTube for his user import routine?
A. Excel (.xlsx)
B. PDF
C. Comma-separated values (.csv)
D. Text (.txt)
Q3. Why did the previously working code suddenly fail according to Richard?
A. Windows updated and broke file operations
B. Richard changed the code recently
C. YouTube introduced a non-standard character in the file name
D. The Downloads folder was deleted
Q4. What command does Richard use in VBA to check if a file exists?
A. FileExist()
B. Dir
C. ExistsCheck()
D. CheckFile()
Q5. What was the ASCII character code that caused problems in the file name?
A. 127
B. 32
C. 160
D. 255
Q6. What does ASCII character 160 represent?
A. Carriage return
B. Tab
C. No-break space
D. Null character
Q7. What technique did Richard use as a workaround to successfully rename the problematic file?
A. Shortening the file extension
B. Using the DOS 8.3 file name convention
C. Removing file permissions
D. Compressing the file before renaming
Q8. What is the 8.3 file name convention?
A. Eight folders deep, three-character extensions
B. Eight file copies and three backups
C. Eight characters for the file name and three for the extension
D. Eight lines per file and three column limit
Q9. In Richard's workaround, what did he do with spaces in the file name?
A. Replaced spaces with underscores
B. Left them as is
C. Replaced spaces with tildes
D. Removed spaces completely
Q10. According to Richard, what should you check if there are multiple similar files in a folder using the 8.3 naming workaround?
A. Only the oldest file can be renamed
B. Use tilde followed by 1 or 2 depending on file order
C. All files will be renamed at once
D. Only the most recent file can be renamed
Q11. Which VBA function did Richard use to loop through each character in the file name and look at its ASCII code?
A. Left()
B. Mid()
C. Split()
D. InStr()
Q12. If you encounter file names with characters like question marks, what does Richard suggest?
A. Rename the file to uppercase
B. Use wildcards to rename
C. Use the DOS 8.3 convention if only one matching file exists
D. Delete the file manually
Q13. What is the main reason working with files and file names is still relevant for Access developers, according to Richard?
A. Access only handles text files
B. Many tasks involve importing external data from other systems
C. VBA natively supports all file types
D. Access always stores data as files
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-C; 7-B; 8-C; 9-D; 10-B; 11-B; 12-C; 13-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on a developer-level tip involving file handling in Microsoft Access using VBA. Recently, I encountered an unexpected problem with a piece of code I had been using reliably for more than a year. The code is designed to import a user list from YouTube by downloading a CSV file, renaming it, and then linking it to my database for easy importing.
Normally, each business day, I simply download this CSV file, which contains usernames, user IDs, and membership levels. I manually rename the downloaded file to "YouTube.csv" so that my Access database can work with it as a linked table, streamlining the import process.
However, the issue began when I suddenly started receiving a "File Not Found" error from my import routine, even though the file was clearly present in my downloads folder. The renaming process, which relies on checking if the file exists and then moving the file using VBA's MoveFile function, just stopped working entirely, despite no changes to the code itself.
This led me to suspect an external change, possibly something on YouTube's end. I started dissecting the file name, removing special characters and segments one at a time, but the issue persisted until I removed the date and time portion. Narrowing it down further, I realized something around the time value was causing the MoveFile function to fail.
To investigate, I wrote code to loop through each character in the file name and display its ASCII value. This process revealed that one of the characters, with an ASCII value of 160, was a no-break space—unlike the standard space character (ASCII 32). Apparently, YouTube had introduced this subtle change in their file naming.
To remedy the problem, I considered several approaches. One would be running a batch file to rename the file, but I decided to use an old DOS trick instead. Windows, even today, supports the legacy 8.3 file name format (eight-character name, three-character extension), which circumvents problematic characters by shortening the file name and replacing the ending with a tilde and a number. By stripping spaces and using the first six characters followed by tilde 1 and the CSV extension, I was able to successfully rename the file using the DOS 8.3 convention.
It is important to note this method assumes only one downloaded file in the folder, as duplicates would increment the tilde number. For most users, especially if you're only working with one file at a time, this workaround is effective.
After implementing this solution, the import routine worked flawlessly again, saving me from manually renaming files and restoring the convenience I had enjoyed with this process for years. This change highlighted how even a small, seemingly invisible character can disrupt file operations in VBA.
For those interested in learning more about file handling in Access, I delve deeper into topics like classic VB file IO, reading and writing text files, file and folder navigation, automatic backend compacting, FTP functions, and advanced File System Object operations in my Access Developer courses, beginning with Developer 30. These lessons are invaluable if you find yourself regularly working with external files and integrating them into your Access databases.
If you've faced similar issues with unusual characters in file names, especially those that disrupt file operations, using the DOS 8.3 naming convention is a reliable workaround for single file scenarios. If you have a different or better solution, feel free to share your thoughts in the discussion or comments.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Identifying hidden non-ASCII characters in file names
Using ASCII codes to diagnose file name issues
Explaining the cause of VBA File Not Found errors
Looping through string characters to reveal ASCII values
Understanding the impact of CHR(160) (non-breaking space)
Replacing spaces and problematic characters in file names
Applying DOS 8.3 naming conventions as a workaround
Renaming files using left six characters plus tilde in VBA
Automating the renaming process to handle invalid filenames
Demonstrating file renaming with VBA and File System Object |
