I am trying to go through a number of folders with files in side of them. Extract some data from the same range in each of the files and put it on the next blank row within my master spreadsheet. This is called Wb in the code. The code is looping through a folder with subfolders inside.
Sub LoopFolders()
' Declaring variables
Dim myFolder As String
Dim mySubFolder As String
Dim myFile As String
Dim collSubFolders As New Collection
Dim myItem As Variant ' This means that excel will decide what kind of variable it is
Dim wbk As Workbook
Dim Wb As Workbook
Set Wb = ThisWorkbook
' set parent folder with trailing backslash
myFolder = "F:\Documents\Ad-hoc\Loop\"
'Retrieve first sub-folder
' * is the wildcard character
mySubFolder = Dir(myFolder & "*", vbDirectory)
Application.ScreenUpdating = False
'Do While Not mySubFolder = ""
Do While mySubFolder <> ""
Select Case mySubFolder
Case ".", ".." ' . refers to current folder, .. refers to the parent folder
' ignore current folder or parent folder
Case Else
' Add to collection called collSubFolders
'collSubFolders.Add Item:=mySubFolder, Key:=mySubFolder
collSubFolders.Add Item:=mySubFolder
End Select
' Get next entry
mySubFolder = Dir
Loop
' Loop through the collection
For Each myItem In collSubFolders
' Loop through Excel workbooks (with any extension) in subfolder
myFile = Dir(myFolder & myItem & "\*.xls*")
Do While myFile <> ""
' Open workbook
DoEvents
Set wbk = Workbooks.Open(Filename:=myFolder & myItem & "\" & myFile)
' Copy data from the opened workbook
' starting from row 2 and column 1 to row 3 and column 2 and copying it
ActiveSheet.Range(Cells(2, 1), Cells(3, 2)).Copy
---here the code breaks and an error 400 is shown----
' Close opened workbook without saving any changes
wbk.Close SaveChanges:=False
Wb.Activate
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Cells(erow, 1).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Application.CutCopyMode = False
myFile = Dir
Loop
Next myItem
Application.ScreenUpdating = True
End Sub
I've spent some time trying to work out why this might be the case. One thing I found was that when I hover my mouse over the wbk variable it shows nothing even after I have f8'ed through it. The document does open but, after that when it is asked to locate the range on the new active file it cannot cope. Does anyone know why this might be the case?
Also note: the looping through the folders works fine and I can see that by going through it step by step. It is the part I mentioned above that although seems simple seems to not be working.
