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
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