Resources
  • Home
  • December-Chrono
  • Psalm136Personalized
  • ExcelResources
    • 99PublicFunctions
    • aDelimitedCodeTemplate
    • aCodeTemplate
    • aBetter_Code_Template
    • ApplyFormulaValue
    • ArrayPublic
    • ArrayMatchCopyRow
    • Capitalization
    • Colors
    • DataTypes
    • DeleteRows
    • Filter
    • FormatStandard
    • Forms
    • Grid
    • Headers
    • ImageScrape
    • InputBox
  • ExcelResources2
    • Like
    • List Review
    • MatchAndReturn
    • mod_Import
    • Numbers
    • PDF_Parse
    • RandomGenerator
    • iRe1
    • Rept
    • RelatedItemsLIst
    • RomanNumerals
    • SheetSplitter
    • Sort
    • Split Method
    • String_DataType
    • TabDelimited
    • Tab_InsertNew_or_ClearExisting
    • Unique_List
      • UniqueLIsts_FilterMethod
  • Excelresources_Files
    • FileLists
    • FileTextParse
    • Folders
  • excelresources_WebScrape
    • CreateList_1-Href_2-Jpg
    • TableData_Load_PlugPlay
    • CreateList_MultiPage_Image_Link
      • ImageDownload_WebLink_List
    • TableData_Load_ClassDiv
    • TableData_Load_ClassSpan
  • Houseboat Vacation
  • At the Foot of the King
  • Devotions
  • Mentoring
  • Inspirational Stories
    • Vacuum Lifting
  • Job Help
  • Resources
    • MS
    • Florida History
  • Contact
  • Heroscape
    • Fish
    • Playstation
  • You Tube Videos
  • House
    • Tiny Houses
    • Backyards
  • CampingResources
    • CampingLand
    • LED Throwies
  • Interesting
  • Recipes
  • Xbox
  • Skydiving
  • ReadingPlan-5Year
  • Desoto
  • BibleStudy
  • HurricaneIrma
  • Home
  • December-Chrono
  • Psalm136Personalized
  • ExcelResources
    • 99PublicFunctions
    • aDelimitedCodeTemplate
    • aCodeTemplate
    • aBetter_Code_Template
    • ApplyFormulaValue
    • ArrayPublic
    • ArrayMatchCopyRow
    • Capitalization
    • Colors
    • DataTypes
    • DeleteRows
    • Filter
    • FormatStandard
    • Forms
    • Grid
    • Headers
    • ImageScrape
    • InputBox
  • ExcelResources2
    • Like
    • List Review
    • MatchAndReturn
    • mod_Import
    • Numbers
    • PDF_Parse
    • RandomGenerator
    • iRe1
    • Rept
    • RelatedItemsLIst
    • RomanNumerals
    • SheetSplitter
    • Sort
    • Split Method
    • String_DataType
    • TabDelimited
    • Tab_InsertNew_or_ClearExisting
    • Unique_List
      • UniqueLIsts_FilterMethod
  • Excelresources_Files
    • FileLists
    • FileTextParse
    • Folders
  • excelresources_WebScrape
    • CreateList_1-Href_2-Jpg
    • TableData_Load_PlugPlay
    • CreateList_MultiPage_Image_Link
      • ImageDownload_WebLink_List
    • TableData_Load_ClassDiv
    • TableData_Load_ClassSpan
  • Houseboat Vacation
  • At the Foot of the King
  • Devotions
  • Mentoring
  • Inspirational Stories
    • Vacuum Lifting
  • Job Help
  • Resources
    • MS
    • Florida History
  • Contact
  • Heroscape
    • Fish
    • Playstation
  • You Tube Videos
  • House
    • Tiny Houses
    • Backyards
  • CampingResources
    • CampingLand
    • LED Throwies
  • Interesting
  • Recipes
  • Xbox
  • Skydiving
  • ReadingPlan-5Year
  • Desoto
  • BibleStudy
  • HurricaneIrma
Connect on Facebook

Files: List / Rename

Create List of Files and dump in Column A:

Function MeListFiles(ByVal sPath As String)
'Source: https://stackoverflow.com/questions/31414106/get-list-of-excel-files-in-a-folder-using-vba
 
'Call Function Example: [MeListFiles ("P:\Folder\SubFolder1\Subfolder2\Subfolder3\Subfolder4\")]
    
    Dim iLoop As Long
    Dim oFile As Object, oFSO As Object, oFolder    As Object, oFiles      As Object
 
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(sPath)
    Set oFiles = oFolder.Files
 
    iLoop = 2
    For Each oFile In oFiles
        ActiveSheet.Cells(iLoop, 1) = oFile.Name
        iLoop = iLoop + 1
    Next
 
End Function




Rename the files in Column A using the value in Column B:

Sub RenameFilesInList()
'Rename files in Column A using the value in Column B
'Source: https://www.mrexcel.com/forum/excel-questions/423149-using-excel-vba-rename-files-directory.html
 
'--------------------------------------------------------------------------------
    Dim sCurrentFileName As String, sNewFileName As String
    Dim iLoop As Long, iRe1 As Long
   
    iRe1 = ActiveSheet.UsedRange.Rows.Count
'--------------------------------------------------------------------------------
    For iLoop = 2 To iRe1
    DoEvents
    If Right(iLoop, 2) = "00" Then Application.StatusBar = iLoop & " & of " & iRe1
       
       '-----------------------------------------
        'Set File Name Strings (From/To)
        sCurrentFileName = Cells(iLoop, 1).Value
        sNewFileName = Cells(iLoop, 2).Value
       '-----------------------------------------
       
       '-----------------------------------------
       'Rename each file
        Dim sOldPathName As String
        sOldPathName = sCurrentFileName
        On Error Resume Next
        Name sOldPathName As sNewFileName
        '-----------------------------------------
       
    Next iLoop
'--------------------------------------------------------------------------------
    
    Application.StatusBar = ""
    MsgBox "Finished"
   
End Sub

Alternate File Read Method: Untested

Excel Trick shows the explanation / includes the original code:

Public FSC as New FileSystemObject
'(Don't forget to set the reference to: Microsoft Scripting Runtime)

    'Creating a FileSystemObject
    Public FSO As New FileSystemObject
    
    Sub CreateFile()
    Dim TxtStr As TextStream
    Dim FileName As String
    Dim FileContent  As String
    Dim File As File
    FileName = "C:\TestDirectory\File.txt" 'File to be created
    
    'Creating a file and writing content to it
    FileContent = InputBox("Enter the File Content")
    If Len(FileContent) > 0 Then
      Set TxtStr = FSO.CreateTextFile(FileName, True, True)
      TxtStr.Write FileContent
      TxtStr.Close
    End If
    
    ' Reading from the file that we have just created
    If FSO.FileExists(FileName) Then
      Set File = FSO.GetFile(FileName)
      Set TxtStr = File.OpenAsTextStream(ForReading, TristateUseDefault)
      MsgBox TxtStr.ReadAll
      TxtStr.Close
                ' Finally Deleting the File
                'File.Delete (True)
    End If
    End Sub
Powered by Create your own unique website with customizable templates.
  • Home
  • December-Chrono
  • Psalm136Personalized
  • ExcelResources
    • 99PublicFunctions
    • aDelimitedCodeTemplate
    • aCodeTemplate
    • aBetter_Code_Template
    • ApplyFormulaValue
    • ArrayPublic
    • ArrayMatchCopyRow
    • Capitalization
    • Colors
    • DataTypes
    • DeleteRows
    • Filter
    • FormatStandard
    • Forms
    • Grid
    • Headers
    • ImageScrape
    • InputBox
  • ExcelResources2
    • Like
    • List Review
    • MatchAndReturn
    • mod_Import
    • Numbers
    • PDF_Parse
    • RandomGenerator
    • iRe1
    • Rept
    • RelatedItemsLIst
    • RomanNumerals
    • SheetSplitter
    • Sort
    • Split Method
    • String_DataType
    • TabDelimited
    • Tab_InsertNew_or_ClearExisting
    • Unique_List
      • UniqueLIsts_FilterMethod
  • Excelresources_Files
    • FileLists
    • FileTextParse
    • Folders
  • excelresources_WebScrape
    • CreateList_1-Href_2-Jpg
    • TableData_Load_PlugPlay
    • CreateList_MultiPage_Image_Link
      • ImageDownload_WebLink_List
    • TableData_Load_ClassDiv
    • TableData_Load_ClassSpan
  • Houseboat Vacation
  • At the Foot of the King
  • Devotions
  • Mentoring
  • Inspirational Stories
    • Vacuum Lifting
  • Job Help
  • Resources
    • MS
    • Florida History
  • Contact
  • Heroscape
    • Fish
    • Playstation
  • You Tube Videos
  • House
    • Tiny Houses
    • Backyards
  • CampingResources
    • CampingLand
    • LED Throwies
  • Interesting
  • Recipes
  • Xbox
  • Skydiving
  • ReadingPlan-5Year
  • Desoto
  • BibleStudy
  • HurricaneIrma