Using VBA can we check the FONT format all the files in a Specific Folder

Bindu Vardhini · Feb 25th 2015

Team,

Basically I am trying a find a code for the below requirement.

I have a Main file which calls set of files in a certain folder. When I click on Check format button I need to compare all the file mentioned in the Main file.

When comparing the Main sheet should create a new sheet with all the Failures of Format checking Results.

I mean which are NON-Arial. The newly created sheet should have 3 columns
1) File Name
2) Sheet Name
3) Cell Name - Cell Value - Arial Font is not matching

Main File Format of Sheet 1

https://www.dropbox.com/s/kzoh1brbaf7e55r/Format_Checker_Main%20Sheet.jpg?dl=0

http://"https://www.dropbox.com/s/kzoh1brbaf7e55r/Format_Checker_Main%20Sheet.jpg?dl=0"

Sheet 2

This Sheet need to be created by Macro for Capturing the Failed Non-Arial ones.

Many Thanks!
Bindu

ashu1990 · Feb 25th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

hi bindu,

welcome to the forum... as you should know that the task which you want usses loops and will check each cell in the file on every sheet so be patient while running the macro as its gona take a lot of time depending on your data size.....

Code

Option Explicit
Sub test()
Dim wbk As Workbook, path As String, i As Long, fname As String, cell As Variant
path = ""  '<--- Path of the folder
ThisWorkbook.Sheets.Add after:=ActiveSheet: ActiveSheet.Name = "Font Checker"
fname = Dir(path & "\", vbNormal)
Do While fname <> ""
If Mid(fname, InStr(fname, "."), 4) <> ".xls" Then GoTo SKP:
Set wbk = Workbooks.Open(path & "\" & fname)
For i = 1 To wbk.Worksheets.Count


    For Each cell In wbk.Worksheets(i).UsedRange
    
        If cell.Font.Name <> "Arial" Then
        
            With ThisWorkbook.Worksheets("Font Checker")
            
            .Range("A" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.FullName
            .Range("B" & Range("B:B").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.Worksheets(i).Name
            .Range("C" & Range("C:C").Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Address & "-" & cell.Value & "- Arial Font Is Not Matching"
            
            End With
        
        End If
    
    Next cell
    
Next i
    
wbk.Close False
SKP:
fname = Dir()
Loop
ThisWorkbook.Worksheets("Font Checker").Range("A1").Value = "File Name"
ThisWorkbook.Worksheets("Font Checker").Range("B1").Value = "Sheet Name"
ThisWorkbook.Worksheets("Font Checker").Range("C1").Value = "cell Value and address"
End Sub

Display More

Bindu Vardhini · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

Dear Ashu,

Superb logic. You are genius.

Four things I need it.

1) Every time I should have to delete the Font Checker Sheet. Is there any way we have to just clear the contents in Font Checker sheets and then records to be shown when we run the macro.

2) Path of the Folder we are Hard coding in the Macro. can we Pick the PATH From Cell B3.

3) Font Should be Picked from Cell C3.

4) In Column C it is shown as "$A$1-EMP No- Arial Font Is Not Matching", can we shown it without $ like "A1-EMP No- Arial Font Is Not Matching"

Thanks a lot again!

Kind Regards,
Bindu

ashu1990 · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

here's the altered code

Code

Option Explicit
Sub test()
    Dim wbk As Workbook, path As String, i As Long, fname As String, cell As Variant
    path = ActiveSheet.Range("B3").Value '<--- Path of the folder
    font_cri = ActiveSheet.Range("C3").Value
    Module1.delete_sheets ("Font Checker")
    
    ThisWorkbook.Sheets.Add after:=ActiveSheet: ActiveSheet.Name = "Font Checker"
    fname = Dir(path & "\", vbNormal)
    Do While fname <> ""
If Mid(fname, InStr(fname, "."), 4) <> ".xls" Then GoTo SKP:
        Set wbk = Workbooks.Open(path & "\" & fname)
        For i = 1 To wbk.Worksheets.Count
             
            For Each cell In wbk.Worksheets(i).UsedRange
                 
                If cell.Font.Name <> font_cri Then
                     
                    With ThisWorkbook.Worksheets("Font Checker")
                         
                        .Range("A" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.FullName
                        .Range("B" & Range("B:B").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.Worksheets(i).Name
                        .Range("C" & Range("C:C").Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Address(0, 0) & "-" & cell.Value & "- Arial Font Is Not Matching"
                         
                    End With
                     
                End If
                 
            Next cell
             
        Next i
         
        wbk.Close False
SKP:
        fname = Dir()
    Loop
    ThisWorkbook.Worksheets("Font Checker").Range("A1").Value = "File Name"
    ThisWorkbook.Worksheets("Font Checker").Range("B1").Value = "Sheet Name"
    ThisWorkbook.Worksheets("Font Checker").Range("C1").Value = "cell Value and address"
End Sub
Public Sub delete_sheets(ByVal shname As String)
On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(shname).Delete
Application.DisplayAlerts = True
End Sub

Display More

Bindu Vardhini · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

Hi Ashu,

Thanks for helping me!

I am getting error when I execute this code.

1) font_cri is not declared
2) When I hard coded the path it is working fine, where as when we are using like this in the code it is throwing an error.
font_cri = ActiveSheet.Range("C3").Value

3) Also I see every time it is created various sheets when I run this macro. I should have only Main sheet and Result sheet we are capturing using Macro.

Appreciate your help!

Thanks!
Bindu

ashu1990 · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

create a sheet named Font Checker as i have removed the code to create a sheet and try this new code

Code

Option Explicit 
Sub test() 
    Dim wbk As Workbook, path As String, i As Long, fname As String, cell As Variant,font_cri  as string
    path = ActiveSheet.Range("B3").Value '<--- Path of the folder
    font_cri = ActiveSheet.Range("C3").Value 
     
    ThisWorkbook.Worksheets("Font Checker").clearcontents


    fname = Dir(path & "\", vbNormal) 
    Do While fname <> "" 
If Mid(fname, InStr(fname, "."), 4) <> ".xls" Then Goto SKP: 
        Set wbk = Workbooks.Open(path & "\" & fname) 
        For i = 1 To wbk.Worksheets.Count 
             
            For Each cell In wbk.Worksheets(i).UsedRange 
                 
                If cell.Font.Name <> font_cri Then 
                     
                    With ThisWorkbook.Worksheets("Font Checker") 
                         
                        .Range("A" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.FullName 
                        .Range("B" & Range("B:B").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.Worksheets(i).Name 
                        .Range("C" & Range("C:C").Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Address(0, 0) & "-" & cell.Value & "- Arial Font Is Not Matching" 
                         
                    End With 
                     
                End If 
                 
            Next cell 
             
        Next i 
         
        wbk.Close False 
SKP: 
        fname = Dir() 
    Loop 
    ThisWorkbook.Worksheets("Font Checker").Range("A1").Value = "File Name" 
    ThisWorkbook.Worksheets("Font Checker").Range("B1").Value = "Sheet Name" 
    ThisWorkbook.Worksheets("Font Checker").Range("C1").Value = "cell Value and address" 
End Sub

Display More

Bindu Vardhini · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

Hi Ashu,

I have created the Font checker sheet and I got the error at this line.

ThisWorkbook.Worksheets("Font Checker").ClearContents

Thanks!
Bindu

ashu1990 · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

replace this

Code

ThisWorkbook.Worksheets("Font Checker").ClearContents

with this

Code

ThisWorkbook.Worksheets("Font Checker").usedrange.ClearContents

ashu1990 · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

hi bindu use this and only keep the excel files into the folder for which you want to get the status

Code

Option Explicit
Sub test()
    Dim wbk As Workbook, path As String, i As Long, fname As String, cell As Variant, font_cri As String
    path = ActiveSheet.Range("B3").Value '<--- Path of the folder
    font_cri = ActiveSheet.Range("C3").Value
     
    ThisWorkbook.Worksheets("Font Checker").usedrange.ClearContents
     
    fname = Dir(path & "\", vbNormal)
    
    Do While fname <> ""
    
        Set wbk = Workbooks.Open(path & "\" & fname)
        For i = 1 To wbk.Worksheets.Count
             
            For Each cell In wbk.Worksheets(i).UsedRange
                 
                If cell.Font.Name <> font_cri Then
                     
                    With ThisWorkbook.Worksheets("Font Checker")
                         
                        .Range("A" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.FullName
                        .Range("B" & Range("B:B").Rows.Count).End(xlUp).Offset(1, 0).Value = wbk.Worksheets(i).Name
                        .Range("C" & Range("C:C").Rows.Count).End(xlUp).Offset(1, 0).Value = cell.Address(0, 0) & "-" & cell.Value & "- Arial Font Is Not Matching"
                         
                    End With
                     
                End If
                 
            Next cell
             
        Next i
         
        wbk.Close False
        
        fname = Dir()
    Loop
    
    ThisWorkbook.Worksheets("Font Checker").Range("A1").Value = "File Name"
    ThisWorkbook.Worksheets("Font Checker").Range("B1").Value = "Sheet Name"
    ThisWorkbook.Worksheets("Font Checker").Range("C1").Value = "cell Value and address"
End Sub

Display More

Bindu Vardhini · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

Its working fine but when I ran the macro twice it is throwing me an error and it is not generating any results in "Font Checker"

Error Line
If Mid(fname, InStr(fname, "."), 4) <> ".xls" Then GoTo SKP:

ashu1990 · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

use the updated code from my latest post #9

Bindu Vardhini · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

Sorry it my mistake, I should be in Main Sheet and Should run the Macro, I am going to Font Checker Sheet and I am running the Macro.

Thanks Anshu very much. I really appreciate your help.

ashu1990 · Feb 26th 2015

Re: Using VBA can we check the FONT format all the files in a Specific Folder

Glad you got it working...

Using VBA can we check the FONT format all the files in a Specific Folder

Participate now!

Share