+ Reply to Thread
Results 1 to 2 of 2

Count .xls files and .txt files in a folder

Hybrid View

  1. #1
    Registered User
    Join Date
    04-12-2019
    Location
    Dallas
    MS-Off Ver
    2016
    Posts
    64

    Count .xls files and .txt files in a folder

    I am trying to count both .xls files and .txt files in a folder. This is my current code that will bring back .xls files and display the comment showing what is in the folder.

    Sub GetFilesAndCountRMS()
    
        Dim Cell    As Range
        Dim File    As Variant
        Dim Files   As Object
        Dim Folder  As Variant
        Dim n       As Long
        Dim Note    As Comment
        Dim Text    As String
        
            With CreateObject("Shell.Application")
                For Each Cell In Range("A1", Cells(Rows.count, "A").End(xlUp)) 'Change when move to Audit sheet
                    Set Folder = .Namespace("P:\Consolidated RM\RMS Exports\" & Cell.Value)
                    If Not Folder Is Nothing Then
                        Set Files = Folder.Items
                        Files.Filter 64, "*.xls"
                        Cell.Offset(0, 3).Value = Files.count - 1
                    Else
                        Cell.Offset(0, 3).Value = "Folder not found."
                    End If
                    
                    Text = ""
                    Set Note = Cell.Offset(0, 3).Comment
                        If Not Note Is Nothing Then Note.Delete
                    Set Note = Cell.Offset(0, 3).AddComment
                        Note.Shape.TextFrame.AutoSize = True
                        
                    For Each File In Files
                        Text = File & vbLf
                        n = Note.Shape.TextFrame.Characters.count + 1
                        Note.Shape.TextFrame.Characters(n, Len(Text)).Insert Text
                        n = n + Len(Text)
                    Next File
                    
                Next Cell
            End With
            
    End Sub

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Count .xls files and .txt files in a folder

    Hello KColgrove,

    Just change

    This...
    Files.Filter 64, "*.xls"
    To this...
    Files.Filter 64, "*.txt;*.xls"
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA Count the Files in a Folder
    By KColgrove in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2019, 09:08 AM
  2. Replies: 1
    Last Post: 01-28-2019, 06:55 PM
  3. [SOLVED] Creating new folder, copying files and renaming files in folder
    By LionelNZ in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-24-2015, 04:18 PM
  4. Replies: 2
    Last Post: 06-01-2012, 07:40 AM
  5. count files in a folder automatically??
    By benjii19 in forum Excel General
    Replies: 13
    Last Post: 03-01-2011, 11:32 AM
  6. count the number of files in a specified folder
    By bambino86 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-04-2009, 12:55 PM
  7. Count files in folder
    By gmunro in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-18-2005, 06:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1