I want to loop through some Excel workbooks in a sub-folder - all workbooks begin with 'F' - open each one - take the data from a specific column (column C) - in a specific worksheet ('Export') and paste it into the next available column in a worksheet called 'Summary' in the workbook I am running the Macro from.

I will then see all the column Cs lined up side-by-side.

The first row (in the summary worksheet) I have made the name of the workbook
The second row will be the date I imported the data
All of the other rows will be the data in Column C of the relevant Export sheet

When I run the macro Macro again - I simply want to overwrite the data.

It all sounded quite simple to me when I agreed to do it but I have hit the buffers. Can anybody help?

Sub LoopThroughFiles()
Dim MyObj As Object, MySource As Object, file As Variant, r As Long
file = Dir("E:\Excel\Data\F*")
r = 1
While (file <> "")
Worksheets("Summary").Cells(1, r) = file
Worksheets("Summary").Cells(2, r) = Date
Workbooks.Open (file)

???

Workbooks.Close (file)
file = Dir
r = r + 1
Wend
End Sub


Also - I want to change the Open to be read only ...

Many thanks