2019 VBA: How to move files based on first or first two characters

Hello to all Excel gurus...

I've written in the past with lots of Excel 2007 VBA questions and you folks were always spot on.

I finally took the plunge and upgraded to Office 2019. To my absolute shock, ALL of my '07 macros worked flawlessly with ZERO changes, save for a couple references I had to add. Anyway...

I have a mega boatload of fonts. After renaming and deleting duplicates, they sit in E:\Fonts\Fonts\RenamedToSort awaiting distribution.

I've used a batch file for decades to distribute them. However, the batch file can't handle the extended characters a font name may start with.

I created a userform with a Sort button and various labels for totals.

I have a directory structure as follows:

E:\Fonts\Fonts\0 (for fonts starting with 0-9)
E:\Fonts\Fonts\# (for fonts starting with a non-alphanumeric character)

E:\Fonts\Fonts\A\a0 (first character a, second character 0-9)
E:\Fonts\Fonts\A\a# (first character a, second character non-alphanumeric)
E:\Fonts\Fonts\A\aa
E:\Fonts\Fonts\A\ab
E:\Fonts\Fonts\A\ac...

So, once a file is read, I have to determine where the file is to be sent based on the first or first two characters. For the single character (non-alphanumeric), there about 145 "legal" characters that can be used. Would be REAL easy if it was a continuous range to use in a Select Case, but it's very broken up. So I used the ASCII value and LOTS of numbers in a Select Case statement. There's gotta be an easier way (short of putting each character in quotes separated by a comma. (My system started acting weird after the first two dozen started and skipping spaces, and making it hard to figure out where the carat actually was.) I may end up sticking with the ASCII values.

Also, the various cases that each extension can be caused issues: ttf, TTF, ttF, etc. I was going to put each combination of upper/lower case for each extension but I thought it would slow things down a lot.

Generally I was able to move files using fso.MoveFile Source, Target with no problem, except when trying to NOT overwrite an existing file. I think I am stupid when it comes to using If Exist.


So, basically, I'd like to:

- Read each file one at a time from E:\Fonts\Fonts\RenamedToSort
- Decide where it goes based on the first character (if non-alphanumeric), otherwise first two characters
- If the file exists, do not overwrite it
- Tally the quantity based on extension (or to satisfy my OCD, also by literal first character. I like lots of "blinking lights")

I always research the best I can and try things myself before asking for help.


The one ting I REALLY do not like about Excel 2019 is that all the help is online. Not cool.

Sorry this is a bit long, I tried to shorten the best I could - I'm not the best at trying to explain things. Please feel free to ask anything I may have left out.

[On a side note, has anyone used VBA to dig into a header of any font to rename it? That'll be another post, just thought I'd toss that in there...]

Thank you for ANY help or guidance.

Mike

What is the purpose of storing these fonts in separate directories ?

Quote:

---

Originally Posted by snb

What is the purpose of storing these fonts in separate directories ?

---

Simple, snb. There's a ridiculously massive amount of them and storing them in one folder would make Windows choke and die. :)

if Windows can't use them why storing them ?

Because I am a graphic designer and collector. By having them organized, whenever I need one, I can jump right to that folder.

I forgot to mention, when I said Windows chokes, I meant that in the folder itself - not installed fonts. :)

In that case I don't see any need to store in separate directories, as long as the windows font directory isn't involved.

When there are too many fonts (or files) in one folder it takes windows forever to read it and it chokes on it. By separating it as I do, the folders open relatively instantly. That's why I do it. I have over 650,000 fonts.