Below are just a couple of examples of the files (names) giving me problems. There are perhaps 3,000 or more files with “non-DOS” like names.
USPS_com[emoji768] - USPS Tracking[emoji768]_files (
THIS IS A DIRECTORY)
USPS_com[emoji768] - USPS Tracking[emoji768].htm
李玲 - 玉玲.jpg
Fwd_ Pay.div Payment Confirmation_ OSOI migrant Fee.eml
The above file names were copied from my “test” directory from Windows Explorer (see attached image, sorry about the quality, I had a hard time posting it.)
Before I begin, let it be known that I have very limited experience with VBA, my background is with PLC’s (Programmable Logic Controllers), so I appreciate keeping any answers as basic as possible and ideally with a short example of how I might solve my difficulty with file and directory names.
I’m writing a VBA program to populate various tables in MS Access to monitor and/or manipulate approximately 20,000 files on my hard drive and two thumb drives. Many of the files originated from overseas and/or a variety of websites and have many different characters, including symbols and foreign “alphabets”. This program does exactly what I want it to for traditional (aka “DOS”) file names but doesn’t like oddball stuff in the file names. I DO NOT want to do any renaming as it would make things very difficult in the future to track files down when someone else or myself references a file using its original name.
As an example, the VBA Access program would bomb out (run time error 52, bad file name) when it encounters a file with Chinese characters (e.g.; 李玲 - 玉玲) in its name.
After some research, I made the following change to Windows:
Go to "Control Panel", "Clock, Language, and Region", "Region and Language", "Administrative", "Change system locale..." and then select "Chinese (Simplified, PRC)" from the drop down menu and reboot after making the change.
The VBA Access program then worked for the Chinese-named files but now bombs out (error 52) when it runs across files or directories with other oddball characters like “[emoji768]”. Before making this change, files containing “[emoji768]” were not a problem.
Other than oddball file names, the program works, i.e.; it correctly writes the name of the file to the MS Access table I want it to.
For what it’s worth, WINDOWS has no problem with these names; I can copy the files, manipulate the file names, or do pretty much whatever I want within Windows for ANY of the files on my hard drive (or thumb drives) no matter what they are named and they display properly. Also, I don’t like the idea of changing the “system locale” because it only solves the problem for certain file names. E.g., I could get it (VBA Access) to recognize Chinese names but then it won’t recognize Japanese names or others.
Anybody have any ideas or see something in my code that prevents it recognizing all file names without crashing?
In the code below, “TrailingSlash” is a tiny chunk of code (not shown) used to add trailing slashes (/) to file paths when needed. “Q” is a constant that creates a double pair of quotes (“”). I left out all my comments because the code would be approximately 2 – 3 times longer. As I mentioned earlier, the code does what I want it to I’m just having problems getting VBA to recognize any file name that Windows does. Also, parts of this code have been copied from others and tweeked to fit my needs.
I’m using Windows 7 and Microsoft Visual Basic for Applications 7.0.
Code:
Private Function FillDirToTable()
Dim strFile As String, strSQL As String
Dim FileLength As Double
Dim strDateModified As String, xvDirectoryName As Variant
strPath = TrailingSlash(strPath) 'This adds a trailing slash to the directory name
strFile = CreateObject("scripting.filesystemobject").GetFileName(Dir(strPath & strFileSpec, 7))
Do While strFile <> vbNullString
strDateModified = FileDateTime(strPath & strFile)
FileLength = CreateObject("scripting.filesystemobject").GetFile(strPath & strFile).Size
strSQL = "INSERT INTO " & strFileTable & _
"(FileName, FilePath, FileDateModified, FileLength) SELECT " & _
Q & strFile & Q & ", " & _
Q & strPath & Q & ", " & _
Q & strDateModified & Q & ", " & _
Q & FileLength & Q & ";"
CurrentDb.Execute strSQL
strFile = Dir
Loop
strFile = Dir(strPath, vbDirectory)
Do While strFile <> vbNullString
If (strFile <> ".") And (strFile <> "..") Then
If (GetAttr(strPath & strFile) And vbDirectory) <> 0& Then
strSQL = "INSERT INTO " & strDirectoryTable & _
" (DirectoryName) SELECT" & _
Q & strPath & strFile & Q & ";"
CurrentDb.Execute strSQL
End If
End If
strFile = Dir
Loop
If Me.optIncludeAllSubdirectories.Value Then
Set rst = CurrentDb.OpenRecordset("C_Directories")
Do While CurrentDb.TableDefs(strDirectoryTable).RecordCount > y rst.Move y
strPath = rst!DirectoryName
y = y + 1
Call FillDirToTable
Loop
End If
End Function
Windows 8 
Access 2010 32bit
Reply With Quote
Originally Posted by rick44
