Code to locate directory and file

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Code to locate directory and file

Post by Rudi »

Hi,

I have a high level folder that stays static (for example: C:\Test\2013\ )
Within this folder are many sub directories by month JAN - DEC
Within each month folder are more directories with various names
I need to search in these sub directories for a specific directory name called SPA_Live
Once SPA_Live is located, I will go to the folder "FundA" which is a name defined by a string variable (which will later change to FundB, FundC...)
Inside this "FundA" folder are multiple files of which I need to open one specific file based on a string I store in another variable (for example file: X123.xlsx)
I need to open this file, filter content from it and copy it to a new blank file and close the file.

Then I need to repeat the process for FEB of 2013 (find the file X123.xlsx in folder "FundA", in FEB in C:\Test\2013\ (filter and copy to new workbook)
Then repeat for MAR of 2013....etc...

Does anyone have some source code that can iterate through each month folder of a 2013 folder and search for a specific folder called SPA_Live which could be within other sub folders, and once located, access a FundA folder and open file X123.xlsx (defined by another string variable).

Hope this is clear....
The biggest hurdle I have is the code to search for a folder?

TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to locate directory and file

Post by HansV »

The following is pure air code:

Code: Select all

Sub ScanFolders()
    Const strTopFolder = "C:\Test\2013"
    Const strFolder = "SPA_Live"
    Dim strSubFolder As String
    Dim strFileName As String
    Dim wbk As Workbook
    Dim fso As Object
    Dim fld As Object
    Dim sfl1 As Object
    Dim sfl2 As Object
    Dim fil As Object
    strSubFolder = "FundA"
    strFileName = "X123.xlsx"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(strTopFolder)
    For Each sfl1 In fld.Subfolders
        For Each sfl2 In sfl1.Subfolders
            If sfl2.Name = strSubFolder Then
                For Each fil In sfl2.Files
                    If fil.Name = strFileName Then
                        Set wbk = Workbooks.Open(fil.FullName)
                        ' ... (your code here)
                        wbk.Close SaveChanges:=False
                    End If
                Next fil
            End If
        Next sfl2
    Next sfl1
End Sub
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

I can take it for a test run when I'm back at work tomorrow morning as I'll need to test it on the actual folder system. I appreciate the code structure and I'll see if I can develop it i to a workable solution. :cheers:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

PJ_in_FL
5StarLounger
Posts: 1090
Joined: 21 Jan 2011, 16:51
Location: Florida

Re: Code to locate directory and file

Post by PJ_in_FL »

Rudi,

This code worked perfectly to locate a folder in a directory tree -- ONCE a reference is set for MS Scripting Runtime.

Based on the code from http://stackoverflow.com/questions/20687810/vba-macro-that-search-for-file-in-multiple-subfolders

Code: Select all

Public sFolderPath As String
Public sFind As String

Function RecurseFolder(sPath As String) As String

    Dim FSO As New FileSystemObject
    Dim myFolder As Folder
    Dim mySubFolder As Folder

    Set myFolder = FSO.GetFolder(sPath)

    For Each mySubFolder In myFolder.SubFolders
        If mySubFolder.Name = sFind Then
            Debug.Print mySubFolder.Name & " in " & mySubFolder.Path 'Or do whatever you want with the file
            sFolderPath = mySubFolder.Path
            Exit Function
        End If
        RecurseFolder = RecurseFolder(mySubFolder.Path)
    Next

End Function

Sub TestFolder()
    sFolderPath = ""
    sFind = "SPA_Live"
    Call RecurseFolder("C:\Test\2013\")
    If sFolderPath <> "" Then
        MsgBox sFolderPath
    Else
        MsgBox "Not Found"
    End If
End Sub
HTH
PJ in (usually sunny) FL

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

Feedback,

Hans...your code works well for those iterations where the target folder is two levels deep. Unfortunately the file structure is not always consistent and the target folder could be three and on rare occasions even four levels deep.
PJ...Thanks for your sample code too. This is useful as it uses a recurse process to search for the target folder and it locates the ones that are also three/four levels deep.

It is now a matter of me merging the two to work as a unit. IOW: To use Hans's loop and integrate the function into this, ultimately to find the SPA_LIVE folder and open a specific file in this folder to filter, extract and close. I'll repost if I need more help or guidance. Many TX to both of you....

PS:
I see that if I use this: Dim fso As Object: Set fso = CreateObject("Scripting.FileSystemObject")
I don't need a reference to the Scripting Runtime library
But this version: Dim fso As New FileSystemObject: Set myFolder = fso.GetFolder(sPath)
does need a reference to the Scripting Runtime library

Are they the same thing ultimately or is there a reason to use one as opposed to the other?
TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to locate directory and file

Post by HansV »

PJ_in_FL's code shows how to traverse folders recursively.

The code that I posted uses so-called late binding; PJ's uses early binding. See:
EARLY binding and LATE binding
Early vs. Late Binding

Early binding has the great advantages that you get IntelliSense, can use the Object Browser, and check your code for syntax errors.
But it can be problematic if the reference contains a version number. If you set a reference to the Microsoft Outlook 15.0 Object Library in Excel 2013, the code will fail on earlier versions of Excel. Late binding doesn't have this problem (provided you don't use features that were new in Outlook 2013).
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

TX for the links.
Some studying to do... :smile:
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

PJ_in_FL
5StarLounger
Posts: 1090
Joined: 21 Jan 2011, 16:51
Location: Florida

Re: Code to locate directory and file

Post by PJ_in_FL »

HansV wrote:PJ_in_FL's code shows how to traverse folders recursively.

The code that I posted uses so-called late binding; PJ's uses early binding. See:
EARLY binding and LATE binding
Early vs. Late Binding

Early binding has the great advantages that you get IntelliSense, can use the Object Browser, and check your code for syntax errors.
But it can be problematic if the reference contains a version number. If you set a reference to the Microsoft Outlook 15.0 Object Library in Excel 2013, the code will fail on earlier versions of Excel. Late binding doesn't have this problem (provided you don't use features that were new in Outlook 2013).
I have SO MUCH to learn!!! :flee:
PJ in (usually sunny) FL

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

I'm ALMOST there..... :smile:
Need further help on folder loop and saving....
The zip file to send is 500KB? Can I email or upload somehow since its over limit?

Please inform...TX
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to locate directory and file

Post by HansV »

You could upload it to a site such as Microsoft OneDrive, Google Drive, DropBox or FileDropper, then share it and post a link to the shared file here.

Or send it to me - you have my e-mail address...
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

TX. I will send to you. (Trust you don't mind!) TX

Haven't used Dropbox for a while, so hope this link works...
https://www.dropbox.com/s/d9kzzng639ycxr6/AllData.zip

Attached is a zip.
I hope it will unpackage properly, but see image below if needed.
On the desktop it will add a DEST, SRC and two files

Please open TestGetData
Run macro
It will prompt for DEST folder first
Then SRC (please select 2013 folder
The it prompts for the Files and Filters file.
When it opens select range B2:D9

The resulting run will save files to the DEST folder
(1) It errors on the path for the JAN files
(2)FEB is actually Test since there is a simulated extra level in that folder. (Any way to try get this to Feb name will be great too)
MAR saves great
1.jpg
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to locate directory and file

Post by HansV »

The macro ran without error once I removed the line

ChDir "C:\Users\rudis\Desktop"

which of course won't work on any PC except yours. For what it's worth, this is the result:
Result.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

OK...will give it a try without that line and see...
TX, hope the fix IS that easy...
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to locate directory and file

Post by HansV »

I assume that that line wasn't the cause of the problem on YOUR computer...
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

Hi Hans,

No...it runs with or without it.

Hmmm...this is weird. It is running OK on my end too...except that it is ONLY processing the one file: 1PDCAG (the filter for PELSSF)
The loop should run for PELSUT and have two filtered sets.

I'll have to step through and see why it is not doing this....
Will post again if I cannot come right... Cheers
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

I should be ending up with this...
1.jpg
But it is only ONE set...?
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78241
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code to locate directory and file

Post by HansV »

You clear the collection of subfolders (queue) while processing the first column. So by the time you get to the second column, there are no folders left.
So you must either build the collection again for each column, or create a duplicate of the collection.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

TX. I'll look into this tomorrow.
I''m off the bed now.
As always...appreciate your advice and help.

:cheers: and :chocciebar: for the effort!!!
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

Hi,

I have cleaned up the code and moved the queue.Add fso.GetFolder(sTopPath) INTO the loop. It now works perfectly except for the last month it processes, it clears the collection after the first filter and the other two filtered workbooks do not get a name as the code debugs on this part:
ActiveWorkbook.SaveAs sDestPath & sYear & " - " & queue.Item(1).ParentFolder.Name & " - " & rC.Value & ".xlsx", FileFormat:=51
Spoiler
Commented out Error Line.jpg
With Error Statements.jpg
I then created the sQue variable and moved it to various locations within the nested loops. Each move gives a different result, but all fail is some way or another. I just can't win.. :groan:

The two scenarios I'm battling with:
1: MAIN ISSUE - How to have the last file iteration get proper files names for the exported csv (eg: 2013-JAN-SVALUE.csv)
2: SECONDARY ISSUE - In stead of 2013-Test-SVALUE.csv, it would be nice to have 2013-FEB-Test-SVALUE.csv. IOW, if it is in a one more level deep folder to at least have the month name in the filename. Any ideas or a function to test?

Code: Select all

Sub GetFiles()

'Get destination location to save files to...
Dim sDest As Object, sDestPath As String
    Set sDest = Application.FileDialog(msoFileDialogFolderPicker)
    With sDest
        .InitialFileName = "C:\Users\rudis\Desktop"
        .Title = "Location to save fund files..."
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        sDestPath = .SelectedItems(1) & "\"
    End With
    'Get destination location to save files to...
Dim sTopFolder As Object, sTopPath As String, sYear As String
    Set sTopFolder = Application.FileDialog(msoFileDialogFolderPicker)
    With sTopFolder
        .InitialFileName = "C:\Users\rudis\Desktop\SRC"
        .Title = "Select YEAR to process..."
        .AllowMultiSelect = False
        If .Show <> -1 Then
            Exit Sub
        End If
        sYear = Split(.SelectedItems(1), "\")(UBound(Split(.SelectedItems(1), "\")))
        sTopPath = .SelectedItems(1) & "\"
    End With
    'Open workbook and select range defining which files and funds to process
Dim sSRCFile As String, sSRCWB As String, rSRCData As Range
    'ChDir "C:\"
    sSRCFile = Application.GetOpenFilename("Excel Files (*.xls*),xls*", , "Browse for the source file listing funds...")
    If sSRCFile = "False" Then Exit Sub
    Workbooks.Open sSRCFile, UpdateLinks:=False
    sSRCWB = ActiveWorkbook.Name
    On Error Resume Next
    Set rSRCData = Application.InputBox("Select the file/fund range to process...", "Define File/Fund Range", Type:=8)
    On Error GoTo 0
    If rSRCData Is Nothing Then Exit Sub

'Set up variables and queue
Dim fso, oFolder, oSubfolder, oFile, queue As Collection
Dim sFolder As String, sFile As String, sFileName As String
Dim wbText As Workbook, shText As Worksheet
Dim rCol As Range, rC As Range

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set queue = New Collection

    'Process and save each file to new workbook
    Application.ScreenUpdating = False
    For Each rCol In rSRCData.Columns
        queue.Add fso.GetFolder(sTopPath) '<---MOVED THE QUEUE INTO THE LOOP (It was originally below > Set queue = New Collection)
        If InStr(1, rCol.Cells(1).Value, "ALL") Then
            sFolder = Right(rCol.Cells(1).Value, 2) & "_" & Left(rCol.Cells(1).Value, 3)
        Else
            sFolder = Right(rCol.Cells(1).Value, 2) & "_" & rCol.Cells(1).Value
        End If
        Do While queue.Count > 0
            Set oFolder = queue(1)
            Debug.Print oFolder.Name
            queue.Remove 1    'de-queue
            For Each oSubfolder In oFolder.SubFolders
                Debug.Print oSubfolder.Name
                queue.Add oSubfolder    'en-queue
            Next oSubfolder

            For Each oFile In oFolder.Files
                With rCol.Cells(1)
                    sFileName = Split(oFile, "\")(UBound(Split(oFile, "\")))
                    If sFileName = .Value & ".csv" Then    'Or sFileName = .Value & ".MVH" Then
                        Workbooks.OpenText oFile, DataType:=xlDelimited, Comma:=True
                        Set wbText = ActiveWorkbook
                        Set shText = wbText.Sheets(1)
                        For Each rC In rCol.Offset(1).SpecialCells(xlCellTypeConstants, 2)
                            shText.Rows(1).Insert
                            shText.Range("A2").CurrentRegion.Offset(-1).Rows(1).Cells(1).Value = "Data1"
                            shText.Range("A1").CurrentRegion.Rows(1).DataSeries RowCol:=xlRows, Type:=xlAutoFill
                            shText.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=rC.Value
                            Worksheets.Add(Before:=shText).Name = rC.Value
                            shText.Range("A1").CurrentRegion.Offset(1).EntireRow.Copy Worksheets(rC.Value).Range("A1")
                            shText.AutoFilterMode = False
                            shText.Rows(1).Delete
                            shText.Range(shText.Range("A" & Rows.Count).End(xlUp).Offset(1), shText.Range("A" & Rows.Count)).EntireRow.Delete
                            Worksheets(rC.Value).Move
                            On Error Resume Next
                            ActiveWorkbook.SaveAs sDestPath & sYear & " - " & queue.Item(1).ParentFolder.Name & " - " & rC.Value & ".xlsx", FileFormat:=51
                            On Error GoTo 0
                            ActiveWorkbook.Close False
                        Next rC
                        wbText.Close False
                    End If
                End With
            Next oFile
        Loop
    Next rCol
    Application.ScreenUpdating = True
    MsgBox "Finished creating files.", vbInformation
End Sub
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code to locate directory and file

Post by Rudi »

Previous post edited to add images... I was in the process of updating...

The sQue variable looks like this:
sQue = queue.Item(1).ParentFolder.Name
ActiveWorkbook.SaveAs sDestPath & sYear & " - " & sQue & " - " & rC.Value & ".xlsx", FileFormat:=51

I see it was not in the code above...
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.