PDA

View Full Version : [SOLVED] Extracting Data from Folder -- Want to Select Higher Level Folder



mikeoly
10-26-2015, 11:36 AM
Hi All,
I have a macro that enters a selected folder, and extracts some specified data for each file within each folder. I'd like to move one level up so I don't have to constantly select folders for the macro to run, it's very time consuming.

Goal: Select high level folder, macro will enter & cycle through two levels of subfolders, extracting data from every macro-enabled spreadsheet (that I later export to a csv file).

Here's what I have now:

MsgBox "Please choose the folder."
Application.DisplayAlerts = False
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = "\\mydesktop\yadada (file://\\mydesktop\yadada)"
.AllowMultiSelect = False
If .Show <> -1 Then MsgBox "No folder selected! Exiting script.": Exit Sub
folderPath = .SelectedItems(1)
End With
If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set fld = FSO.getfolder(folderPath)
If FSO.folderExists(fld) Then
For Each fsoFol In FSO.getfolder(folderPath).subfolders
For Each fsoFile In fsoFol.Files
If Mid(fsoFile.Name, InStrRev(fsoFile.Name, ".") + 1) = "xlsm" Then
fileName = fsoFile.Name

Set wbkCS = Workbooks.Open(fsoFile.Path, UpdateLinks:=False)


I'd appreciate any help! Thank so much!

Leith Ross
10-26-2015, 03:20 PM
Hello mikeoly,

Generally speaking, moving up a level in folder will take you back to the parent folder of the folder you are in. Is this what you want to do?

mikeoly
10-26-2015, 04:24 PM
Hello mikeoly,

Generally speaking, moving up a level in folder will take you back to the parent folder of the folder you are in. Is this what you want to do?

Yes. Instead of ending after its done running through each folder/file, I want it to move onto the next.

Ei. Parent>level 1>level 2>files...
Currently,the macro prompts to select 'level 1' folder and cycles through level 2 folders & its files. Instead prompting for the level 1 folder I need the macro to cycle through them all.

mikeoly
10-29-2015, 08:31 AM
Yes. Instead of ending after its done running through each folder/file, I want it to move onto the next.

Ei. Parent>level 1>level 2>files...
Currently,the macro prompts to select 'level 1' folder and cycles through level 2 folders & its files. Instead prompting for the level 1 folder I need the macro to cycle through them all.
Does anyone have any idea/resources that can help me with this? Is it possible to cycle through multiple levels of folders? Many thanks!

Leith Ross
10-30-2015, 12:00 PM
Hello mikeoly,

These macro should get you going int the right direction. The macro ListFiles will let you select the search depth of the Subfolders. A value of 0 (zero) will return only the files in the selected folder. A positive number will search the Subfolders of Subfolders to the given level. A value of -1 will search all Subfolders of Subfolders.

You can also provide the file type to search for. If you don't specify a file type then all file types will be returned.

The output is to a Variant you defined in your Sub or Function or globally. It will create a scalar 1-D array which holds the file path and name of each matching file type.

I am sure you have questions about this because ListFiles uses the Shell object and the macro is recursive.

Add this code to a new VBA Module


' Written: October 30, 2015
' Author: Leith Ross
' Summary: Recursively lists files in a folder with the option to control the depth of searching
' subfolders. The default value is 0 (zero). This lists only the files in the given folder.
' A positive value will search to given depth. A value of -1 will search all subfolders.
' The output is a scalar 1-D array with each element containing the file path and name.


Private oShell As Object

Sub ListFiles(ByRef Output_Array As Variant, ByVal FolderPath As Variant, Optional ByVal FileType As String, Optional ByVal FolderDepth As Long)

Dim n As Long
Dim oFile As Object
Dim oFiles As Object
Dim oFolder As Variant
Dim oShell As Object

If oShell Is Nothing Then
Set oShell = CreateObject("Shell.Application")
End If

Set oFolder = oShell.Namespace(FolderPath)
If oFolder Is Nothing Then
MsgBox "The Folder was Not Found" & vbLf & vbLf & "Path = " & FolderPath, vbCritical
Exit Sub
End If

Set oFiles = oFolder.Items

If FileType = "" Then FileType = "*"

oFiles.Filter 64, "*." & FileType
For Each oFile In oFiles
n = UBound(Output_Array)
Output_Array(n) = oFile.Path
ReDim Preserve Output_Array(n + 1)
Next oFile

oFiles.Filter 32, "*"
If FolderDepth <> 0 Then
For Each oFolder In oFiles
Call ListFiles(Output_Array, oFolder, FileType, FolderDepth - 1)
Next oFolder
End If

End Sub


Example of using the macro


Sub GetFiles()

Dim ans As Integer
Dim File As Variant
Dim FileList As Variant

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count <> 0 Then
FolderPath = .SelectedItems(1)
Else
Exit Sub
End If
End With

ReDim FileList(0)

Call ListFiles(FileList, FolderPath, "xlsm", -1)

For Each File In FileList
If File <> "" Then
ans = MsgBox(File & vbLf & vbLf & "Continue?", vbYesNo)
If ans = vbNo Then Exit Sub
End If
Next File

End Sub

mikeoly
11-04-2015, 10:27 AM
Thank you, Leith! Using the information you provided, I was able to get the macro working that way I intended.

Leith Ross
11-04-2015, 11:13 AM
Hello mikeoly,

You're welcome. Great job on modifying the code! Glad I could help.