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
Get the Desktop / Temp folder
Read and write to plain text files
Get a local path from a OneDrive path
I'm not including a detailed explanation of each one 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.
Some things to point out are:
All of these procedures (with the exception of the one to get the Desktop folder) use built-in VBA keywords. The potential downside of this is that VBA does not work with file or folder names with Unicode characters in them. Beware of this … but I find it's a rare occurrence to come across a file or folder name that falls foul of this and it's not too challenging to train users to not do so. However, if you have a need to include Unicode characters in file or folder names then this code will not work for you!
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