What is the shortest way to loop through all files in a folder?

[prabha_friend]

I believe this is most recurring scenario for all. Any simple way like:

For each file in folder.files
Msgbox File.Name
Next File

For your information the above code doesn't work

 

[pr2-eugin]

You could use the CODE by Allen Browne, modify a bit I think? http://allenbrowne.com/ser-59.html

 

[prabha_friend]

Paul. Thanks for the reference. I want it less coded. Can someone please explain why the following code is giving the error 'Object variable or With block variable not set'

Private Sub Consolidate_Click()
Dim temp As Object
Set temp = temp.getfolder(CurrentProject.Path & "\Inputs\")
For Each file In temp.files
    MsgBox file.Name
Next file
End Sub

 

[Uncle Gizmo]

Try next on its own

 

[Uncle Gizmo]

And you've got temp.getfolder... That don't look right, try get folder(....

 

[pr2-eugin]

What object is temp? You declared it as Dim temp As Object then the next line you are setting the temp to temp.getfolder ???

You can also simplify the code to,

Private Sub Consolidate_Click()
    Dim temp As String
    temp = Dir(CurrentProject.Path & "\Inputs\")
    Do While temp <> vbNullString
        MsgBox CurrentProject.Path & "\Inputs\" & temp
        temp = Dir
    Loop
End Sub

 

[prabha_friend]

What object is temp? You declared it as Dim temp As Object then the next line you are setting the temp to temp.getfolder ???

You can also simplify the code to,

Private Sub Consolidate_Click()
    Dim temp As String
    temp = Dir(CurrentProject.Path & "\Inputs\")
    Do While temp <> vbNullString
        MsgBox CurrentProject.Path & "\Inputs\" & temp
        temp = Dir
    Loop
End Sub

Thanks Paul. I just always declare a temp variable for Non-Mandatory variables. Any idea why that getfolder method is not working?

 

[prabha_friend]

Try next on its own

I don't get you

 

[pr2-eugin]

Any idea why that getfolder method is not working?

Let us go back to the basic example. We have an entity Car. The car has its own methods and attributes. So we create an object for car, and we want to use the method driveTo(fromLocation, toLocation). So First we declare the "temp" object, then we Set the Object to car, then we use the method for Driving. In VBA terms you should be using.

Dim temp As Object
Set temp = CreateObject("Cars")
temp.driveTo "Chennai", "Tangore"

What you are doing is missing the Second step.

Dim temp As Object
Set temp = temp.driveTo("Chennai", "Tangore")

Can you see - you have not set the Temp object to anything and you are trying to use a method. To the compiler the Object is invalid let alone the method.

Did you try the code I gave you?

 

[prabha_friend]

Did you try the code I gave you?

Yes Paul. Its working Fine. Was just curious about the Getfiles method too. Thanks again.

 

[jdraw]

prabha,

I think your code in post #3 was trying to use filesystemObject.
Here's some sample code (from freeVBCode) that shows another technique using fso.

When I saw you Dim temp as Object and ..GetFolder, it made me think fso.

Private Function AllFiles(ByVal FullPath As String) _
  As String()
'***************************************************
'PURPOSE: Returns all files in a folder using
'the FileSystemObject

'PARAMETER: FullPath = FullPath to folder for
'which you want all files

'RETURN VALUE: An array containing a list of
'all file names in FullPath, or a 1-element
'array with an empty string if FullPath
'does not exist or it has no files

'REQUIRES: Reference to Micrsoft Scripting
'          Runtime

'EXAMPLE:

'Dim sFiles() as string
'dim lCtr as long
'sFiles = AllFiles("C:\Windows\System")
'For lCtr = 0 to Ubound(sFiles)
'  Debug.Print sfiles(lctr)
'Next

'REMARKS:  The FileSystemObject does not
'Allow for the use of wild cards (e.g.,
'*.txt.)  If this is what you need, see
'http://www.freevbcode.com/ShowCode.asp?ID=1331
'************************************************

Dim oFs As New FileSystemObject
Dim sAns() As String
Dim oFolder As Folder
Dim oFile As File
Dim lElement As Long

ReDim sAns(0) As String
If oFs.FolderExists(FullPath) Then
    Set oFolder = oFs.GetFolder(FullPath)
 
    For Each oFile In oFolder.Files
      lElement = IIf(sAns(0) = "", 0, lElement + 1)
      ReDim Preserve sAns(lElement) As String
      sAns(lElement) = oFile.Name
    Next
End If

AllFiles = sAns
ErrHandler:
    Set oFs = Nothing
    Set oFolder = Nothing
    Set oFile = Nothing
End Function