Convert all the excel files to all Capital letter. You know? 1

[VCat88]

Any method that can Convert all the excel files to all CAPITAL LETTER?

 

[GlennUK]

How many Excel files do you want this done for? How are you at writing VBA macro code?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[StuckInTheMiddle]

Hi VCat88

Here is a macro to do what you want. This will rename all the .XLS extension files in the directory d:\files (change this to your directory) and make their name all capitals.

Sub RenameFiles()
Dim OldFileName As String, NewFileName As String, FolderName As String

    FolderName = "D:\Files\"
    OldFileName = Dir(FolderName & "*.xls", vbDirectory)
    
    Do While OldFileName <> ""
        Name FolderName & OldFileName As FolderName & UCase(OldFileName)
        OldFileName = Dir
    Loop

End Sub

This will work on any file type not just XLS just change the "*.xls" to whatever file you wish to capitalize.

Hope this helps,

A,

 

[Mizzness]

VCat,

Why not use the UPPER function ?

Good luck.

 

[GlennUK]

Hi Vcat88,

I'd guess that you want the contents of cells to be converted to uppercase, and not the filenames ( as StuckInTheMiddle has guessed )? Is that right? You could use the worksheetfunction UPPER as Mizzness has suggested, or process every cell in a macro, and where the contents are not formulae and contain text use the VBA Ucase function.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[StuckInTheMiddle]

The question seemed pretty obvious to me, if my solution doesn't match it then please rephrase the question.

A,

 

[VCat88]

Thanks!

 

[VCat88]

Here another script...

Put all the scripts below into Excel macro.



Sub UpperTheSelection()
Dim Cell As Object 'Declare the Cell variable.
For Each Cell In Selection
' If the cell is blank or a text string, then
' skip to the next cell in the selection.
If Trim(Cell.Value) <> "" Then

Cell.Value = UCase(Cell.Value)
End If
Next
End Sub

Sub LowerTheSheet()
Dim Cell As Object 'Declare the Cell variable.
For Each Cell In Selection
' If the cell is blank or a text string, then
' skip to the next cell in the selection.
If Trim(Cell.Value) <> "" Then

Cell.Value = LCase(Cell.Value)
End If
Next
End Sub

After copy the script, select on the Excel'cell in which area need conversion.(Highlight) Then Play macro and choose the macro function to run.

It will able convert the capital to BIG or small easily.

 

[KenWright]

Noooooooo - You have to be VERY careful what kind of data you use that on. If it is just text then fine, but don't take that anywhere near a spreadsheet that has formulas in it, else you will lose all the formulas within your selected range if you run that code against it.

Far better to use a piece of code that copes no matter what you throw it at.

Some examples:-

Sub MakeUpperCase()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each cell In ActiveSheet.UsedRange
        If cell.HasFormula = False Then
            cell.Value = UCase(cell.Value)
        End If
    Next cell
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Sub MakeLowercase()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each cell In ActiveSheet.UsedRange
        If cell.HasFormula = False Then
            cell.Value = LCase(cell.Value)
        End If
    Next cell
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Sub MakeProperCase()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim myCell As Range
    Dim myrng As Range

    On Error Resume Next
    Set myrng = Intersect(Selection, _
                          Selection.Cells _
                          .SpecialCells(xlCellTypeConstants, xlTextValues))
    On Error GoTo 0

    If myrng Is Nothing Then
        MsgBox "Please select a range that contains text--no formulas!"
        Exit Sub
    End If

    For Each myCell In myrng.Cells
        myCell.Value = StrConv(myCell.Value, vbProperCase)
    Next myCell
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------