Looping through file items

[DRH192]

Hi everyone,

I am looking for the code to satisfy the following pseudo code....

For all files in folder,
if file is an excel file (have a function that can do this), see if it has already been imported (checks a table called imports against the file path)
if not imported, import spreadsheet (it will then be analysed and a new file produced based on the contents)
end

so really all i'm looking for is the code to loop through folder items

Cheers

 

[PHV]

You may consider the Dir VBA function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

Take a look at

Application.FileSearch

set a reference to the FileSearch object by clicking References on the Tools menu while in module Design view. Then set a reference to the Microsoft Office 9.0 Object Library in the References dialog box by selecting the appropriate check box.

 

[Page410]

Here is a snippet that should help. Just make sure you add the Microsoft Scripting Runtime reference.


Function LoopThroughFiles()
On Error GoTo ErrorHandler
Dim sysObject As FileSystemObject
Dim sysFolder As folder
Dim sysFile As File

Set sysObject = CreateObject("Scripting.FileSystemObject")
Set sysFolder = sysObject.GetFolder("Put Your Directory Here")

For Each sysFile In sysFolder.Files
If Right(sysFile.Name, 4) = ".xls" Then
'Put your processing here
End If
Next sysFile

ExitHere:
On Error Resume Next
Set sysFolder = Nothing
Set sysObject = Nothing
Exit Function
ErrorHandler:
Resume ExitHere
End Function