Cycling through files in a folder

[carmenlisa]

I need to do two things in VBA that I do not really have a clue how to do and am hoping for specific advice.

Fist, I need to use the DIR function to loop through a number of Excel files, perform actions from them one by one, then end the process when each file has been processed.

Second, when the above is completed, I need to move all files in that defined directory to a different one.

I am comfortable in a lot of areas of VBA but I am no expert in it. These two tasks are ones I have never encountered before.

 

[dhookom]

Large scope for your question. How far have you gotten or what have you tried?

To get you started, consider vba code like:

Public Function GetFileNames(pstrFolder As String)
    Dim strFilename As String
    strFilename = Dir(pstrFolder)
    Debug.Print strFilename
    Do Until strFilename = ""
        strFilename = Dir() & ""
        Debug.Print strFilename
    Loop
End Function

Duane
Hook'D on Access
MS Access MVP

 

[oharab]

You may also want to consider using the FileSystemObject which has a richer set of functionality for working with folders and files IMHO.
For your code, I suggest you split things up into individual functions so that your code works like:

[i][b]PSEUDOCODE!!!![/b][/i]

for each xlFile in ListOfXLFiles
  if PerformActionsAndSave(xlFile, "C:\Completed\Files\")=True then
    Kill xlFile
  end if
next xlFile

function PerformActionsAndSave(sFileName as String) as boolean
  workbook.open sFileName
  do some stuff
  ...
  workbook.save
  workbook.close
  if no errors occurred then
    PerformActionsAndSave=true
  else
    PerformActionsAndSave=false
  end if
end sub

hth

Ben

----------------------------------------------
Ben O'Hara

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.