As of August 2024, the code in this post has been updated to use the FileSystemObject. The previous version of the code that used only VBA keywords and functions can be found below.
Working with files and folders (directories) from VBA code can be a hassle. The VBA language is not helpful with lots of niggles as to how it operates with files and folders and lots of odd keywords that don't work in an obvious way.
My solution to this is to have a Module in which I keep all my standard procedures to make working with files and folders simpler … called UtilsFiles. The procedures in this Module work in any VBA-enabled application.
When working on a project that includes any requirement to work with files and folders then the first thing I do is to import this into my Project (in case you don't know how to import a Module: in the Project Window, right-click on the Project name or on any Module within the Project, select 'Import File …'). However, you can just copy it from the below text.
This Module includes procedures that:
Manipulate file or folder paths (join them, clean them, get just the filename from a path etc)
Check if a file or folder exists
Create folders (all folders in a path)
Get all files in a folder
Delete files / folders
Get the Desktop / Temp folder
Read and write to plain text files (using Unicode/UTF-16, UTF-8, or Ansi/Ascii)
Get a local path from a OneDrive path
Get drive type information
Copies / moves / renames files
Allows the user to select one or multiple file(s) or select a folder
I'm not including a detailed explanation of each procedure here, however, each procedure has header comments so that you can understand what it does, what inputs it requires and what value (if any) it will return.
The current version of the code that uses the FileSystemObject
This version of the code has a number of dependencies (that were not required in the previous version):
The 'Microsoft Scripting Runtime' library for the Scripting.FileSystemObject
The 'Microsoft ActiveX Data Objects 6.1 Library' for the ADODB.Stream object
... both of these libraries should be present on every Windows device
... both of these libraries are used 'late bound' - as such, you do not need to add a Project Reference to them - but you may be interested that special binding was used here (see the <Ref> code comments).
If you were using the previous version of the code, you will want to learn about the following breaking changes from previous version of the code:
CreateFolders() … no longer has an outbAvailable parameter (but see the new IsFolderEditable() procedure)
WriteToTextFile() … parameters are different including an option for the Encoding (Unicode/UTF-16; UTF-8; Ansi/Ascii)
ReadFromTextFile() … same as WriteToTextFile()
And some non-breaking improvements from previous version of the code:
All functionality … now handles Unicode characters in file/folder paths/names ... this is really the core reason for the update to the code
PathJoin() … now handles any number of path elements (not just 2)
GetFileName() has an optional parameter to include/exclude the file extension (previously it was always included, which is now the default)
A number of new procedures have been added including:
GetDriveType() and GetDriveTypeAsString()
CopyFile(), MoveFile() and RenameFile()
TryGetFileFromUser(), TryGetFilesFromUser() and TryGetFolderFromUser()
To download the 'new' UtilsFile, click here:
Some things to note
The GetAppFolder() procedure includes a reference of 'gsAPP_NAME'. gsAPP_NAME is a constant (a global String constant, hence 'gs') that is the name of the application / Project that I am developing. The benefit of this is that, on occasion, a customer will ask for a different name and when I've used a single constant throughout a Project then changing it is simple. I'd recommend you do the same … but, if you don't want, you can either change gsAPP_NAME to a literal String or you can delete the GetAppFolder() procedure entirely.
The reason for including the procedure to get a local path from a OneDrive path is that, since (I think) Office 2016, various Workbook / Document / Presentation etc properties have returned web link paths, instead of local paths, if that Workbook / Document / Presentation is saved to a OneDrive folder. For example, I have a PowerPoint presentation open now and it is saved to this location: "C:\Users\<myusername>\OneDrive\Desktop\test files\Presentation1.pptm" (i.e. if I right-click on the file with Shift held down and select 'Copy as path' then that is the text that is copied to the clipboard), however, if I use VBA to get the value of ActivePresentation.FullName then I get "https://d.docs.live.net/7x31bxdc5f06xd51/Desktop/test files/Presentation1.pptm" … i.e. I get a OneDrive 'web link' path instead of a local path. If you try to use this OneDrive 'web link' path with almost any VBA file / folder handling code then you will get an error … unless you first convert that OneDrive 'web link' path to a local path … which is exactly what TryGetLocalPathFromOneDrivePath() does. Almost always. I say almost always because it has never failed for me or any of my customers … but it doesn't cover every situation that can come up with OneDrive installations … if you need the 100% solution, instead see (and please upvote … the effort these guys have gone to is incredible!) this StackOverflow answer https://stackoverflow.com/a/73577057/11318818
The previous version of the code that used only VBA keywords and functions
To download the 'old' UtilsFile, click here:
コメント