Using VBA can we check the FONT format all the sheets.

It seems that you "problem" specification change while I was testing a possible solution so at the moment I don't know if this could be of help to you but have a look.

Please Login or Register  to view this content.

If only font is Arial then answer will be "Font ok" if sheet contains mix of fonts (Arial and others) answer will be "Font is wrong.

Alf

Hi Alf,

Thank you so much for your Reply.

This is what my requirement, but I want to Enhance the Requirement a little more.

I have some set of files in a Path. All the Files need to be compared in One Shot.

Can we Create a Separate File with Two Sheets

1) First Sheet

a) Serial No
b) Path of the Files ( Excel Files to check the Format)

2) Second Sheet

If the Font is not Arial. We have to track the File Name (that we should get it from First Sheet Path Column) , Cell Number (ex: C3), Cell Value.

Thanks!
Kiran

Let me see if I understands you problem properly:

Do you wish to loop through all files in a specific folder and add the file's serial number (I assume this is a part of the file name) and path

Then you wish to check if font of the file is not Arial. Do each file contain a number of sheets that needs to be checked if font is not Arial?

What do you mean when you say "not Arial" i.e. if 99,9 % of the sheet is set with "Times Roman" and one cell is Arial is this a "non Arial" or an Arial?

Alf

Well this looks like a more complex problem than I first thought. Not so sure I'm able to solve it but perhaps/probably another forum member will be able to do so.

Will have a go at it of course but I feel you need to upload one or two files that I can use for testing purposes (don’t forget to remove sensitive information from file).

As I have no idea why you need to find where you have "non Arial font" in a sheet is there something else you are looking for i.e. something that could be used as well when looking for specific cells?

From you image I see that the files you wish to examine are csv files but csv files can not contain formatting, as they simply are flat text files with the items delimited by a comma so I'm a bit puzzled as to where your problem is. Would you care to explain how the csv files are "treated" i.e. imported to excel or ???

Alf

Thank you so much for looking into this!

1) OBIEE Tool generate Report Output in CSV Format
2) We have around 300 Report Outputs will be storing in a Particular folder for ex: C:\ABC\
3) So, as I said earlier I want to maintain a sheet with the path of these 300 Report Output and our macro should check once by one file.
4) The Macro should also create a sheet where exactly we don't have Arial Font.

If we do not do this automation, we have to open the Each of the 300 files and check manually

Also .CSV we will open and save in .XLS format. In Main sheet we can call .XLS files.

I am attaching the Sample files, as of now I have inserted dummy 3 records.

Thanks!
Kiran

I'm still not sure what your problem is i.e. you say you wish for a record "where exactly we don't have Arial Font"

As I see it a macro can set the font to whater kind you wish for.

Put this macro in a module (Alt + F11 and inset module + copy macro to module) in the "Main Sheet".

When you run it, it will loop through all cells in the B column and opening ever file looping through all sheets and setting font to whatever you wish be it "arial", "calibri" or "times roman".

At the moment the font will be set to "arial" for all data in every sheet of every file defined in the range "B2:B??" as far as you have entered data. So this is not what you really aked for i.e. a report where you don't have "Arial" font. Checking the files you uploaded only two cells in every sheet with "Arial" font, the other 10 cells has "Calibri" fonts so I'm a bit puzzled as I would have assumed the majority of cells would be "Arial"

Please Login or Register  to view this content.

To set font to "calibri" chang line this to:

Please Login or Register  to view this content.

I guess my problem with understanding you problem is becuse I don't know what you are going to do with the "non arial" cells found.

As your OBIEE Tool generate report output in CSV format one could write a macro that loops through all csv file in that particular folder, imports every file to excel and then saves the result as an xls file in another folder setting font right all in one go.

Alf

Hi,

Actually we are doing User acceptance testing. we will not use OBIEE tool.

The reports which are generated by OBIEE Tool we need to test it Manually

As we have 300 reports it is taking time to test each CSV / Excel file. So we need to Automate this Formatting work.

We should not set the Font to Arial, rather we need to tell to the Developer this Cell Text is not having the Arial Font.

I know it is bit Confusing, Simply I can say we need track the Failed Scenarios in all the Files in that Folder.

Thanks for baring me.

Many Thanks!
Kiran

Ok this macro works on your uploaded files according to your 1st specification i.e. it finds a specified font (at moment set to Arial) and writes the result to file "Main Sheet.xls" sheet2. the file name as given in column B, the sheet name and the address where the cell was found. Not my best effort as code is clumsy and don't know how long it will take to check all cells on every sheet.

To test run macro "ChkFont"

Please Login or Register  to view this content.

Alf