Hello (the rest of the) World – reading data in other languages

Have you ever opened a text file in Excel and found that the characters looked weird or corrupted? The Wikipedia entry for mojibake says ‘In German, Buchstabensalat (“letter salad”) is a common term for this phenomenon, and in Spanish, deformación (literally deformation).’

This is one of a series of blog posts on file handling in VBA to cope with Unicode file names and contents. Part 1 was posted on Sep 28 2020, about reading text from Unicode files. The articles should provide all the Excel VBA code you need for working with Unicode folder and file names in the Windows file system, reading unstructured Unicode text, and structured text file data queries.

This post is the code for using the Filesystem object to replace the old VB functions like Dir(), Filelen(), FileDateTime(), etc, that do not work with Unicode file names. FileSystem.bas is the module which you can import into your project, and the CSV file is a sample downloaded from opengov.gr.

Code module: FileSystem.bas   Sample file: Σχόλιο UTF-8 BOM.csv

Old VBFunction
CurdirGetCurDir
 ChPath
ChDirSetCurDir
 NewFSO
 GetFSOFile
DirFileExists
Dir( file,vbDirectory)FolderExists
 GetPathAndName
FileLenFileSize
FileDateTimeFileDateModified
FileCopyCopyFile
Name..AsRenameFile
KillDeleteFile
 GetTempName
 GetTempFullName
 GetFSOFolder
MkDirCreateFolder
RmDirDeleteFolder
AttrGetFileAttributes
SetAttrSetFileAttributes
 FileAttrString
 GetOpenFilenameStr

Let me know of any improvements you can suggest.

About Patrick O'Beirne, spreadsheet auditor

Patrick provides consultancy and training in spreadsheet development, auditing / testing and model review; and the Excel addin XLtest
This entry was posted in Excel/VBA and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s