Solved Change all PDF filename in Excel from lowercase to UPPERCASE

September 30, 2015 at 04:33:41
Specs: Windows 7
I want to change every PDF in a folder to uppercase, but can only use something that windows comes ready for (such as VB or Command Line/Batch files).

See More: Change all PDF filename in Excel from lowercase to UPPERCASE

Report •

#1
September 30, 2015 at 05:07:19
Windows does not differentiate between lowercase and uppercase filenames. Perhaps if you provided the reason you'd like to do this...

Report •

#2
September 30, 2015 at 06:04:50
✔ Best Answer
Here is a VBA solution, you can paste this code into any sheets or module in Excel and run the code from Sub Start.

What this does is, ask you for the folder in which all your files are, it will them rename then all in uppsercase

note that as you specified that you only want to rename the pdf documents, this will only rename the pdf files.

Private Sub Start()
    Dim RootFolder
    RootFolder = BrowseForFolder
    
    Dim MyObj As Object, MySource As Object, file As Variant
    
    file = Dir(RootFolder & "\")
   
    While (file <> "")
        
        If Right(file, 4) = ".pdf" Then
        
            Name RootFolder & "\" & file As UCase(RootFolder & "\" & file)
        End If
        
        file = Dir()
    Wend
      
End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
     'Function purpose:  To Browser for a user selected folder.
     'If the "OpenAt" path is provided, open the browser at that directory
     'NOTE:  If invalid, it will open at the Desktop level

    Dim ShellApp As Object

     'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

     'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0

     'Destroy the Shell Application
    Set ShellApp = Nothing

     'Check for invalid or non-entries and send to the Invalid error
     'handler if found
     'Valid selections can begin L: (where L is a letter) or
     '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select

    Exit Function

Invalid:
     'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False
End Function


message edited by AlwaysWillingToLearn


Report •

#3
September 30, 2015 at 06:25:04
how can I run this code?
I have pasted this code into excel sheet. then how to run this code I don't know

Report •

Related Solutions

#4
September 30, 2015 at 06:28:52
Sorry, do the following

Open Excel
Hold Alt and Press F11
when the VBA IDE appears double click on sheet one
Paste the code there
then place the cursor in 'Sub start'
and press F5

This will run the code

message edited by AlwaysWillingToLearn


Report •

#5
September 30, 2015 at 06:37:45
thanks a lot!!!!!!!!!!!!
thank you very much!!! your answer Is too useful...

Report •

#6
September 30, 2015 at 06:38:52
Awesome glad it worked.... thanks for letting us know..

Report •

#7
September 30, 2015 at 06:42:01
ok how can I change other documents like excel, word , .dgn file names?

Report •

#8
September 30, 2015 at 06:46:02
i have modified the orginal code so that is now works on ALL files rather than just on pdf.

Private Sub Start()
    Dim RootFolder
    RootFolder = BrowseForFolder
    
    Dim MyObj As Object, MySource As Object, file As Variant
    
    file = Dir(RootFolder & "\")
   
    While (file <> "")
        
    '    If Right(file, 4) = ".pdf" Then
        
            Name RootFolder & "\" & file As UCase(RootFolder & "\" & file)
   '     End If
        
        file = Dir()
    Wend
      
End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
     'Function purpose:  To Browser for a user selected folder.
     'If the "OpenAt" path is provided, open the browser at that directory
     'NOTE:  If invalid, it will open at the Desktop level

    Dim ShellApp As Object

     'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

     'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0

     'Destroy the Shell Application
    Set ShellApp = Nothing

     'Check for invalid or non-entries and send to the Invalid error
     'handler if found
     'Valid selections can begin L: (where L is a letter) or
     '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select

    Exit Function

Invalid:
     'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False
End Function


Report •

#9
September 30, 2015 at 22:59:13
thanks a lot!!!!

I want to recover Permanently deleted files in windows 7 without using software.
I was saved that files in office server networking folder, how can I recover that files?
please help me?


Report •

#10
September 30, 2015 at 23:22:28
Start a new thread as this question isn't relevant to this thread and those people on here that can help will probably not see this question in this current post. Someone will be able to help you

Report •

#11
September 30, 2015 at 23:33:42
ok thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

:)

Report •

#12
January 5, 2016 at 10:13:00
is there a way to make this code run for every new PDF that is added to the specified folder? So every time I add a new PDF with a lowercase filename, it is automatically saved as uppercase? I don't want to have to run the code every time a new PDF is added to the folder--as there are many folders in which I have applied the code.

Thank you.


Report •

#13
January 6, 2016 at 00:19:53
britni,

it is always better to start a new thread with a link to the thread in question, this is so others who are not a part of this conversation are able to see your new question and perhaps help you out, in this case the only people who will get a notication of your question are, me (AlwaysWilling...,, ImranKhan and beachy).

to answe your question, as far as i know the only way to achieve what you are asking is by

1) having constant monitoring of your directories via code and run the code when a change is detected - i dont this this is efficient and from past experience with folder monitoring via VBA i can tell you it is not very easy or clean.

2) run a code after a specified time interval, for example automatically run the code every 10 minutes. Again not too efficient but perhaps better than the previous.

anyways these are my thoughts, it may be worth starting a new thread, add a link to this thread witin it, and ask you question, be sure to include as much detail as possible, i m sure someone will be able to help.


Report •

Ask Question