I am trying to build a VB script which will loop through a folder and open up all the files within it (all .xlsm). The code will then run a macro within each of the files.

It was working when the script was built as a list, but this was for a quick test, in real life it will deal with hundreds so a loop is needed.

the code i currently have is:

Option Explicit
Dim FSO, FLD, FIL, objExcel
Dim strFolder

Set objExcel = CreateObject("Excel.application")

strFolder = "H:\My Documents"

Set FSO = CreateObject ("Scripting.FileSystemObject")
Set FLD = FSO.GetFolder (strFolder)

For Each FIL in FLD.Files
objExcel.workbooks.open FIL.Name
objExcel.Run "Macro"


The script is running and then failing saying Error: 'Filename.xlsm' Could not be found. Which is strange, as it is there, and it has obviously found it as it is the first file in the folder.

I am unsure of where to go and what to do next.

Any help would be greatly appreciated.