hi everyone, I have a folder with subfolders that have excel files in them. I am trying to have a vba loop go through each of the sub folders, extract the value from Cell F1 from a specific sheet and then paste that value into another workbook. a couple of roadblocks:
1.) each folder has a specific name
2.) i only want it to open files that end with a specific name (like if im donig a july report, only open reports ending in "july" i.e. 'john Smith July report"0
3.) the number of folders keeps changing, it either lessens or adds, so I want to do something dynamic.
here is what I have so far:
I run into a weird error telling me that my file could not be found, check the spelling etc. in my case it says the file 'John smith July.xlsx' could not be found. it clearly exists cause it pulled the name.
i am at my wit's end here, i would really appreciate some help
thanks
1.) each folder has a specific name
2.) i only want it to open files that end with a specific name (like if im donig a july report, only open reports ending in "july" i.e. 'john Smith July report"0
3.) the number of folders keeps changing, it either lessens or adds, so I want to do something dynamic.
here is what I have so far:
Code:
Sub loopAllSubFolderSelectStartDirector()
'Another Macro must call LoopAllSubFolders Macro to start to procedure
Call LoopAllSubFolders("C:\Users\dshafiq\Desktop\MS Zia\")
End Sub
'List all files in sub folders
Sub LoopAllSubFolders(ByVal folderPath As String)
Dim fileName As String
Dim fullFilePath As String
Dim numFolders As Long
Dim folders() As String
Dim i As Long
If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
fileName = Dir(folderPath & "*.*", vbDirectory)
While Len(fileName) <> 0
If Left(fileName, 1) <> "." Then
fullFilePath = folderPath & fileName
If (GetAttr(fullFilePath) And vbDirectory) = vbDirectory Then
ReDim Preserve folders(0 To numFolders) As String
folders(numFolders) = fullFilePath
numFolders = numFolders + 1
Else
'Insert the actions to be performed on each file
'This example will print the full file path to the immediate window
Workbooks.Open (fileName)
Set Rng = Worksheets("Week 1").Cells("F1")
Windows("Temp.xlsx").Activate
Cells(1, 1).Select
Rng.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' Debug.Print folderPath & fileName
End If
End If
fileName = Dir()
Wend
For i = 0 To numFolders - 1
LoopAllSubFolders folders(i)
Next i
End Sub
I run into a weird error telling me that my file could not be found, check the spelling etc. in my case it says the file 'John smith July.xlsx' could not be found. it clearly exists cause it pulled the name.
i am at my wit's end here, i would really appreciate some help
thanks