Open a file in a subfolder

Question

I have file named AT5321.xlsx (this name will keep on changing) from where I want to call the macro which will:

Go to the address: C:\Atul\Data
Search for folder containing the same name as that of the originating file (ex: AT5321). The actual name of folder is like: F-003-106-AT5321.M
Now, after the above subfolder is opened, it has a file named report.xls which has to be opened.
Stop the macro

As you must have got, I am a novice person. I am looking to ease my day to day Excel tasks.

Any slight help would be of great value to me.

Where is the code you have tried? And where is your question? Note that posting a requirement is not asking a question (see [ask]). You should at least find out how to list all folders containing a specific string. If you google or search here on the stack you will find examples for sure. Try to use them, write some code and if you got stuck or errors come back [edit] your question, add your code and ask a question to it. • [This](https://stackoverflow.com/questions/33893133/is-it-possible-to-list-all-the-files-and-folders-in-a-custom-directory-excel-v) should get you started. — Pᴇʜ, Feb 05 '19 at 07:49
If you are new to VBA, a good start point is to use the recorder, then rework the generated code. — iDevlop, Feb 05 '19 at 08:33
Possible duplicate of [Loop Through All Subfolders Using VBA](https://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba) — Tim Stack, Feb 05 '19 at 08:35

Tim Stack · Answer 1 · 2019-02-05T08:30:20.410

0

With the help of Cor_Blimey's post.. (Loop Through All Subfolders Using VBA)

This will loop through all subfolders and subfolders in the subfolders (in theory indefinitely)..

Public Sub NonRecursiveMethod()
Dim fso, oFolder, oSubfolder, oFile, queue As Collection
Dim FoundFolder as Boolean

Set fso = CreateObject("Scripting.FileSystemObject")
Set queue = New Collection
queue.Add fso.GetFolder("C:\Atul\Data")

Do While queue.Count > 0
    Set oFolder = queue(1)
    queue.Remove 1 'dequeue

    For Each oSubfolder In oFolder.SubFolders
        queue.Add oSubfolder
        If oSubfolder Like "*" & ThisWorkbook.Name & "*" Then 'Replace workbook name if necessary
            Workbooks.Open Filename:=oSubfolder & "\report.xls"
            FoundFolder = True
            Exit For
        End If
    Next oSubfolder
Loop

If FoundFolder = False Then MsgBox "Error: Folder '" & ThisWorkbook.Name & "' could not be found", vbExclamation, "Error"
End Sub

Alternatively, you can look in the subfolders from just the main folder

Sub SubFoldersinMainFolder()
Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("C:\Atul\Data")
Set subfolders = folder.subfolders

For Each subfolders In subfolders

    If subfolders Like "*" & ThisWorkbook.Name & "*" Then 'Replace workbook name if necessary
        Workbooks.Open Filename:= subfolders & "\report.xls"
        FoundFolder = True
        Exit For
    End If

Next subfolders
If FoundFolder = False Then MsgBox "Error: Folder '" & ThisWorkbook.Name & "' could not be found", vbExclamation, "Error"

End Sub

I must add that the word "folder" begins to look very weird now

edited Feb 05 '19 at 08:30

answered Feb 05 '19 at 07:51

Tim Stack

3,209
3
18
39

you are missing the subfolders enqueue. (either that or the whole queue thing is useless) – Vincent G Feb 05 '19 at 07:55
Which is not necessary as far as I am aware. Code works as it is – Tim Stack Feb 05 '19 at 08:00
So you could remove the (useless) queue, and just keep the For loop. I'm not telling that you answer is not working, just pointing on some possible improvements. You are free to take it into account or not. – Vincent G Feb 05 '19 at 08:03
You're correct. I've edited the post to give OP room to choose between both options – Tim Stack Feb 05 '19 at 08:15
1

So you posted an answer based on an answer of a question that is already marked as duplicate? Hmm... – FunThomas Feb 05 '19 at 08:22
I am extremely thankful to all who took out their valuable time to help me out. I am sorry that I posted the question probably violating the community guidelines. This was my first question. I will keep your feedbacks with me and won't repeat that. Thanks a lot Tim Stack for the code. – Atul Feb 06 '19 at 15:55
Tim Stack: My all the macros are in one master file. I am running the code from a different excel file, so by using the provided code it searches for the folder with name similar to that of my master file "macro.xlsb" containing the codes. But I have to search for folder that's name is similar to my excel file from where I am calling my macro.(ex: my file name is "AT5321.xlsx" and the folder name would be like "F-003-106-AT5321.M") – Atul Feb 06 '19 at 16:09
In that case you ought to change the `Workbooks.Open Filename:=oSubfolder & "\report.xls"` line to `Workbooks.Open Filename:=oSubfolder & "\" & ThisWorkbook.Name & ".xlsx"`. Please select the answer to your question for future reference. – Tim Stack Feb 07 '19 at 07:09
Both codes look for "macro.xlsb". Let me explain the problem once again: lets say that there are three excel files already open: AT2341.xlsx, ATR2362.xlsx and macro.xlsb (all macrofiles are saved in "macro.xlsb"). Now, say I go to "AT2341.xlsx" and then from there I want to run the macro which will go to the following address: "C:\Atul\Data" and then look for folder which has the word "AT2341" in it (excluding ".xlsx" term). The folder name would look something like this: "B-004-108-AT2341.M". Now I need to just open this searched folder and open the file named "report.xls" contained in it. – Atul Feb 09 '19 at 19:20
For that I would loop my provided code for each workbook currently open, excluding the file where the macros are located (`If not wb.Name = ThisWorkbook.Name Then...`). Then you can populate the `oSubFolder` variable with the current iteration: `If oSubfolder Like "*" & wb.Name & "*" Then` – Tim Stack Feb 11 '19 at 07:16
1

Dear @TimStack: I am extremely thankful to you for your kind response. I can not thank you & this platform (stackoverflow.com) enough for the support that I received. The code was not working because of my lack of knowledge. With little tweaking it's working now. – Atul Feb 12 '19 at 12:07
'If oSubfolder Like "*" & ThisWorkbook.Name & "*" Then Workbooks.Open Filename:=oSubfolder & "\report.xls"' was replaced by ' If oSubfolder Like "*" & Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1)) & "*" Then 'Replace workbook name if necessary Workbooks.Open Filename:=oSubfolder & "\report.xls"' – Atul Feb 12 '19 at 12:13

Open a file in a subfolder

1 Answers1