Results 1 to 7 of 7
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Post How to Get a List of Fonts for Word Mail Merge

    Hi,

    I'm trying to get a list of fonts installed on my PC, so my Access application, which is automating Word Mail Merges, can set the font for the desired content.
    Currently, the application is working fine, and will change the font size of particular text without issues. Let's say the word document contains text such as:

    Code:
    Now is the time for all <f:20>good</f> men to come to the aid of their party.
    The Access application can successfully find the "<f:20>good</f>" text in the Word document, and convert it to 20 points while removing the surrounding code.

    What I now want to do is also change the font and size by having text like this:

    Code:
    Now is the time for all <f:Courier New:20>good</f> men to come to the aid of their party.
    My research shows it's complex to get a list of the font names. I have a form which pops up containing a combo box for the font names, and the user chooses which one, then the form code builds the above text.

    Currently I have code which scans files in the C:\Windows\Fonts folder and loads the names into a "fonts" table. Now I have items in the table such as "verdana.ttf" (I've stripped the ".fon" from the end of the file name).



    While this is a good way to get a list of fonts, I only have the file name, not the font name as Word would need when I create a Range object and tell it that it's .Font.Name property is "verdana.ttf". This totally doesn't work.

    So finally, the question: Is there any way to get the actual Font object out of the font file that I found? Alternatively, if installed font names are listed in the registry, I have code which can enumerate the proper keys and get the "real" names, then load them into my "Fonts" table. Not sure of the proper method.

    Does anybody have experience with this issue? Thanks...

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Consider:
    Code:
    Sub listWordFonts()
        Dim wd As Word.Application, fontID As Variant
        Set wd = CreateObject("Word.Application")
        For Each fontID In wd.FontNames
            If fontID Like "V*" Then Debug.Print fontID
        Next
        wd.Quit
        Set wd = Nothing
    End Sub
    Want to provide your code that scans the files?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,946
    Attached is a modified version of an app by Stephen Lebans which calls the Windows Font Chooser dialog.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	18 
Size:	30.3 KB 
ID:	45182

    That part works though there are errors in the forecolor & backcolor button code that I never got around to fixing

    I updated it for VBA7 several years ago but cannot guarantee it will work in 64-bit Access.

    Hope that helps.


    NOTE: I also have code by Dev Ashish which provides a list of all installed fonts for populating a combo.
    However that will take a bit more work to extract it from one of my own apps
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,946
    Here is a MDB version of Stephen Lebans app from FontColorDialog (lebans.com).
    It was originally an A97 file but I've updated it to A2003 MDB format.
    In this version, all features work correctly
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks for all of the answers.

    Colin, I appreciate the files, and I will definitely analyze them and use the code for a future font picker dialog.
    I don't want the entire font dialog for this particular application, so I think June7's suggestion is pretty good.

    I may have mis-stated the problem. In the text codes mentioned in the OP, those codes need to be put into the text box in Access containing the verbage which is to be merged into Word. Because Access rich text has no relationship to Word formatted text, I had to use codes such as the ones above. My mail-merging code will find those codes in the Word document after merging, and apply them as directed.

    I've created a pop-up form in Access, which has a combo for fonts and another combo for sizes. So the issue is to populate the font combo with installed fonts in Access. The user will pick a font from the combo and when they click OK, the font code will be embedded into the text in Access and later merged into a Word document.

    I have code to read font lists out of the registry, but that no longer works, even though I relaxed the permissions on the appropriate registry key containing the font names. So my current work-around is to open the registry and export the Fonts key to a text file, then use that text file to load an Access table containing the font names. My Fonts combo box is bound to that table. This is very cumbersome and prone to errors, so June7's suggestion will much more easily allow me to get a list of fonts to load into my FontNames table. That small bit of code will easily load the table if it is missing or empty.

    Thanks again for the answers. If anyone wants to see the final solution, please reply and I will post it.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,946
    There is an even simpler method you can do. Just use a value list for your combo/listbox with a selection of fonts
    This is a screenshot from Form 5 of my example app: Move Forms & Controls - Mendip Data Systems

    Click image for larger version. 

Name:	Capture.PNG 
Views:	11 
Size:	28.4 KB 
ID:	45190

    The row source is:
    Arial;Calibri;Cambria;Comic Sans MS;Courier New;Georgia;Lucida Sans;Segoe UI;Tahoma;Times New Roman;Trebuchet;Verdana;Wide Latin

    In this example, I deliberately only wanted a limited number of fonts available
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    I like your thinking, Colin!
    Unfortunately, that would be yet another meeting with client and a long involved discussion about exactly what fonts they would want listed.
    They're a non-profit, so I don't charge for my time.
    Also, there may be a string size limit. There are a LOT of fonts to list in the combo.
    Thanks anyway...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access VBA to run Mail Merge \ Word Doc \ PDF
    By Kody_Devl in forum Programming
    Replies: 3
    Last Post: 09-29-2020, 10:20 AM
  2. Replies: 3
    Last Post: 01-29-2016, 08:06 AM
  3. Replies: 1
    Last Post: 10-25-2015, 12:02 PM
  4. Mail merge from Access to Word
    By Matthew7 in forum Access
    Replies: 1
    Last Post: 02-18-2015, 07:46 AM
  5. Mail merge with word
    By alexc333 in forum Access
    Replies: 0
    Last Post: 07-26-2011, 12:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums