PDA

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



Spielberg
01-10-2021, 09:23 PM
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

snb
01-11-2021, 03:20 AM
What is the purpose of storing these fonts in separate directories ?

Spielberg
01-11-2021, 04:27 AM
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. :)

snb
01-11-2021, 09:45 AM
if Windows can't use them why storing them ?

Spielberg
01-11-2021, 09:55 AM
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. :)

snb
01-11-2021, 12:56 PM
In that case I don't see any need to store in separate directories, as long as the windows font directory isn't involved.

Spielberg
01-11-2021, 01:28 PM
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.