Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Returning the no. of files in a Windows folder (VBA / O2K / SR1a)

    I want to be able to return the number of files in a specific Windows folder as an integer to a VBA macro.

    Does anyone know of a Method or Property I can use to do this?

    Ta in anticipation,

    Perc

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Returning the no. of files in a Windows folder (VBA / O2K / SR1a)

    You can use the File Scripting Object from the Microsoft Scripting Runtime library. You need to set a reference to this library. Microsoft Scripting Runtime should be in the list; if not, browse for scrrun.dll in the system folder (WindowsSystem or WinNTSystem32).

    Use code like the following:

    Dim fso As New FileSystemObject
    Dim fld As Folder
    Dim intFileCount As Integer
    Set fld = fso.GetFolder("pathname")
    intFileCount = fld.Files.Count
    Set fld = Nothing
    Set fso = Nothing

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning the no. of files in a Windows folder (VBA / O2K / SR1a)

    Thanks for the tip Hans.

    I tried your suggestion with the following code:
    Dim fso As New FileSystemObject
    Dim fld As Folder
    Dim intFileCount As Integer

    Set fld = fso.GetFolder("M:Business")

    but get a 'Type mismatch' error.
    I gave it the absolute path as well but got the same result. Any ideas as to what I might be doing wrong?

    Regards,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Returning the no. of files in a Windows folder (VBA / O2K / SR1a)

    Perhaps there is a confusion over Folder. Do you have another definition of Folder in your code, or another reference that might contain a Folder object? Try replacing

    Dim fld As Folder

    by

    Dim fld As Scripting.Folder

    Otherwise, I wouldn't know.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Returning the no. of files in a Windows folder (VBA / O2K / SR1a)

    Just in case your problem stems from setting thereference, try a late binding sample as follows

    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    MsgBox fso.GetFolder("pathname").Files.Count
    Set fso = Nothing

    You do not need to set a reference using this method.

    Andrew C

  6. #6
    New Lounger
    Join Date
    Jul 2001
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Returning the no. of files in a Windows folder (VBA / O2K / SR1a)

    Hello Hans,

    That extra qualification fixed it.

    My thanks! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  7. #7
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Returning the no. of files in a Windows folder (VBA / O2K / SR1a)

    Just a picky point here, but you're better off scrapping the Integers here in favor of Longs:

    - Long won't cause an error on the odd chance that you have more than 32,000 + files (not likely I suppose, still...)

    - Longs are more efficient than Integers in VB6 because they are the native number type in VB6. Integers have to be coerced into longs so using longs saves that step (or so I've read).

    Gary

Posting Permissions

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