none
Change fonts for all Word, Excel, PowePoint, Visio documents in a folder RRS feed

  • Question

  • Hi, we have a lot of documents: Word, Excel, PowerPoint, Visio (23-64 documents for each document type, and sometimes there are more than one sheet in Excel, and more than one tab in Visio), and a lot of fonts are used in them. This is not professional. 

    I want to use VBA to change all the fonts in these documents to "Arial".

    Is it possible?


    Thursday, September 6, 2012 11:54 AM

All replies

  • Yes, it is.

    But you use different object model in each type of documents.

    For example in PP.

    Sub zmiana_czcionki_w_PPS()
    'MVP OShon from VBATools.pl
    Dim shp As Shape, sl&
    For sl = 1 To ActivePresentation.Slides.Count
     For Each shp In ActivePresentation.Slides(sl).Shapes
     On Error Resume Next
     shp.TextFrame.TextRange.Font.name = "Arial"
     Next
    Next sl
    End Sub

    in Excel:

    Sub change_on_arial_in_Excel()
    Dim wks As Worksheet
    Application.ScreenUpdating = False
    For Each wks In ActiveWorkbook.Worksheets
        wks.Cells.Font.Name = "Arial"
    Next
    Application.ScreenUpdating = True
    End Sub
    

    in Word:

    Sub Zmiena_czcionki_w_wordzie()
    'MVP OShon from VBATools.pl
     ActiveDocument.Content.Font.Name = "Arial"
    End Sub

    etc..


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Thursday, September 6, 2012 12:34 PM
    Answerer
  • I wrote something more. You can see that on Gallery page

    If you think that is a useful solution - reach for the stars


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Thursday, September 6, 2012 6:16 PM
    Answerer
  • Hi Oskar Shon, thank you very much.

    I have installed the Excel add-in, and it is able to update the font for all Excel documents.

    But it cannot update the fonts of Word documents, although the process indicator says it was processing the Word documents...

    How to use it for Word documents?

    By the way, I have a suggestion, which I believe is very important. Please don't use variables or names that don't make much sense to the ordinary people, e.g. zmiana_czcionki_w_PPS, sciezka$, etc, as this will make your code very difficult to read.

    Sunday, September 9, 2012 12:37 PM
  • I make the test and addin making change in XLSx and Word DOCx too.

    I cant share this method to Visio and PowerPoint because there objects in late binding does not work well, but above you can find code for PPS to use in PowerPoint only. With Visio you can use the same model (I do not have instaled so.. that can be your job)

    BTW You have google translator do ya?

    Regards.


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Sunday, September 9, 2012 7:21 PM
    Answerer
  • Let me test it again tonight, and I will let you know the result.

    Yes, I have Google translator in my computer.

    Monday, September 10, 2012 2:34 AM
  • I deactivated the add-in and then reactivated the add-in, and then it was able to update the fonts in the Word documents.

    One more question: how to remove the add-in?

    I followed the instructions on the following page:

    http://office.microsoft.com/en-us/excel-help/add-or-remove-add-ins-HP010342658.aspx

    But I cannot remove it from the "Add-Ins available" list.

    This is the first time that I've used an Excel add-in. :-) By the way, your add-in is great!

    Monday, September 10, 2012 12:59 PM
  • This is Excel's file addinn not COM from .NET/VB6, you do not have to uninstall. In new instance you'll not see him in VBA Project.

    You would if inside code 'll add line:

    Dim myadd As AddIn
    If Not myadd.Installed Then
    Set myadd = AddIns.Add(sDestDir & Me.AddInName, True)
            myadd.Installed = True
    '...

    but I do not. ;]


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Wednesday, September 12, 2012 6:17 AM
    Answerer