Code to locate directory and file
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Code to locate directory and file
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Code to locate directory and file
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
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.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 1102
- Joined: 21 Jan 2011, 16:51
- Location: Florida
Re: Code to locate directory and file
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
HTH
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
PJ in (usually sunny) FL
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Code to locate directory and file
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).
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
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
TX for the links.
Some studying to do...
Some studying to do...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 5StarLounger
- Posts: 1102
- Joined: 21 Jan 2011, 16:51
- Location: Florida
Re: Code to locate directory and file
I have SO MUCH to learn!!!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).
PJ in (usually sunny) FL
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
I'm ALMOST there.....
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Code to locate directory and file
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...
Or send it to me - you have my e-mail address...
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
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
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Code to locate directory and file
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:
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:
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
OK...will give it a try without that line and see...
TX, hope the fix IS that easy...
TX, hope the fix IS that easy...
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Code to locate directory and file
I assume that that line wasn't the cause of the problem on YOUR computer...
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
I should be ending up with this...
But it is only ONE set...?
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78585
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Code to locate directory and file
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.
So you must either build the collection again for each column, or create a duplicate of the collection.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
TX. I'll look into this tomorrow.
I''m off the bed now.
As always...appreciate your advice and help.
and for the effort!!!
I''m off the bed now.
As always...appreciate your advice and help.
and for the effort!!!
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
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
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..
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?
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
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Code to locate directory and file
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...
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.
Rudi
If your absence does not affect them, your presence didn't matter.