open files in folder sequencially

[mikeymay]

I have a folder that can contain any number of files.

I want to open each one pull some data from it onto a master spreadsheet, save it in a sub folder and then close the saved file and delete the original file.

I am confident I can write the code saving file in subfolder and deleting original, where I am struggling is the code for opening non specified files until there are no more.


Thanks in advance.

[Webtest]

Mikeymay:
This is one way to scan a directory:

Sub Macro1()
Dim tStr As String

' Fetch the first filename, if any
tStr = Dir("C:\Table-X\*.*")  'See Dir in the HelpHeap
While Len(tStr) > 0
    'Do something with the file HERE
    Debug.Print tStr  'Show the Filename in the Immdeiate window
    '
    tStr = Dir    'Fetch the next file
Wend

End Sub

[zaza]

Here is an alternative:

Set fs = CreateObject("Scripting.Filesystemobject")
Set f = fs.GetFolder("c:\")
counter = 1
For Each i In f.Files
    Cells(counter, 1) = i
    counter = counter + 1
Next

Obviously you don't need the counter and can replace the Cells line with whatever you wish to do with the file. Set the path in the second line, or use a dialogbox to let the user select it.

zaza

[Webtest]

zaza took a lot of shorthand shortcuts ... here is the full version:

Option Explicit
Sub Macro1()
Dim counter As Integer
'Requires "Microsoft Scripting Runtime" to be checked in Tools > References
Dim fso As Scripting.FileSystemObject
Dim aFolder As Scripting.Folder
Dim aFile As Scripting.File

Set fso = CreateObject("Scripting.Filesystemobject")
Set aFolder = fso.GetFolder("c:\")
counter = 1
For Each aFile In aFolder.Files
    Cells(counter, 1).Value = aFile.Name
    counter = counter + 1
Next aFile

End Sub

[mikeymay]

Think I have enought there to help me along, thanks!