Solved: Using all Spreadsheets in a folder to create table

I am looking to create a summary table in one spreadsheet, by copying information from all spreadsheets in a file.

So I am looking to open all spreadsheets (the titles are random and therefore I can't specify) from a folder g:\Iain, and then copy particular cells into my summary table.

E.g open spreadsheet in G:\Iain, copy cell B3 and paste into cell A1 of summary table spreadsheet in folder F:\Table.

I want to go through all spreadsheets in G:\Iain and copy the same cells, but paste below the previous results in results table spreadsheet (e.g. the second spreadsheet opened, I want to copy cell B3 as before, but paste into cell A2 in my table spreadsheet and so on.

I am pretty new to VBA so please bear with me.

I think I may be able to use loops but am unsure as to the "unknown" element of the titles and pasting into next line.

Any help would be appreciated!

haddy27

I have been attempting this and so far have the following, which seems to be almost there but not quite!

Any thoughts/ideas on my code please feel free, this is proving rather tricky!

Code so far

[VBA]
Sub FileList()
Dim FileSystem As Object, MyFolder As Object
Dim MyFiles As Object, MyFile As Object
Dim a As Integer

Set FileSystem = CreateObject("Scripting.FileSystemObject")
Set MyFolder = FileSystem.getfolder("G:Temp\Iain")
Set MyFiles = MyFolder.Files

For Each MyFile In MyFiles

Workbooks.Open MyFile
Range("B3:E3").Select
Selection.Copy
LoopCells
Selection.PasteSpecial Paste:=xlPasteValues

Next
End Sub


Sub LoopCells()
Dim rCurr As Range
Dim rNext As Range
Set rCurr = ThisWorkbook.Worksheets("Results").Range("A2")
Set rNext = rCurr.Offset(1, 0)
Do
If rNext.Value = rCurr.Value Then
Set rNext = rNext.Offset(1, 0)
End If

Loop Until rNext.Value <> rCurr.Value


End Sub

[/VBA]

Help!

From the above it is opening all files in the folder, but then only pasting the contents of cell B3 in the first spreadsheet into A2 in my results table, and then falling down?

haddy27

Hi :hi:

Sorry for not going down your way of thinking,
I suggest another approach.

[vba]Sub test()
Dim Path As String
Dim FN As String
Dim c As Range, Rng As Range

Set Rng = ActiveSheet.Range("A:A")
Path = "G:\Iain\"
FN = Dir(Path & "*.xls", vbNormal)

For Each c In Rng.Cells
If FN = "" Then Exit For
Workbooks.Open FN
c = ActiveSheet.Range("B3")
ActiveWorkbook.Close False
FN = Dir()
Next c
MsgBox "No more files"
End Sub[/vba]
In theory, this piece of code lists B3-s into column A of the active sheet.
It only takes B3 from the particular sheet, that is active when a workbook is opened. E.g. if G:\Iain\something.xls has 3 worksheets, and it opens with Sheet1 as activesheet, the code above takes Sheet1!B3, but Sheet2 and Sheet3 are ignored.

Jimmy

JimmyTheHand

Thanks for that, appears closer to the mark than I am, however...

When I put in the code I am experiencing a run-time error that says:

'Iain.xls1 cannot be found', however the file is still located here. When I hit debug it highlights the line:

[VBA]
Workbooks.Open FN
[/VBA]

Unsure as to why this is happening, I have tried moving this file out of the folder and it comes up with the same warning for the next file?

Any ideas on what could be causing this?

Thanks for your help!

haddy27

I think it's about paths and the Dir function. It returns only filename, without path. So try replacing
[vba]Workbooks.Open FN[/vba] with
[vba]Workbooks.Open Path & FN[/vba]
I think this will work.

JimmyTheHand.

That works, thank you so much for your help, this has been driving me round the bend!

I'm pretty new to VBA so you may well see my name pop up a fair bit on here!

Thanks again

haddy27

You are welcome :)
Please, using Thread Tools above the first post, mark the thread as "Solved" (if you think it is).
You don't have to do so in case you plan to post relevant follow-up questions soon.

Jimmy