List files of folder and sub folders without Loop

Hi,

Any way to list files of a folder and its sub folder if they start with ....(say "ImranBhatti*.xlsm") without looping.

Why I want such a way:

Because my main folder contains minimum 35 subfolders and each of them contains nearly 110 files and it takes too much time to loop.
Note: I am also reading at least 4 properties of these files via "Shell.Application" inside the loop.


Help would be greatly appreciated.

Imran Bhatti

Any one please?

AFAIK, a loop is needed.
But, loops can be made more efficient. Perhaps if we saw your current code, we could speed up your process.

Thanks @sir mikerickson

Currently I don't have access to my office PC.
Will post it later.

Another option that I am currently experiment on is

I have created a .search-ms folder via vba which is showing all files in that folder.

Now I am thinking to read folder but the problem is ".search-ms" folder is not actually a folder (its XML file rather , that looks list folder from inside but its icon and extension is not that of a yellow folder.

Originally Posted by mikerickson

if we saw your current code, we could speed up your process.

Please Login or Register  to view this content.

As you can see a lot of modification is required to test it on PC other than mine. you will be suggesting theoretically how this sub routine can be made more robust.

Any idea please?

A few suggestions for you to consider:

1. From personal experience & testing, FSO is a slow method for returning file names and should be avoided where possible if speed is important.
2. Avoid the use of Like if speed is important. For example, replace Like with Left function e.g.
   

   Please Login or Register  to view this content.

3. Avoid repeated use of same calculation in your code e.g. Environ("Username"). Calculate this value once, set it to a variable and use this variable instead.
4. Avoid repeated calculations in the Excel sheet values (e.g. the replace function). Make the calculations in the array before pasting to the sheet.

P.S. I am guessing you have had a bad experience with loops in the past. Loops are not necessarily a bad thing (although they can be if badly coded). Sometimes a loop is the most efficient and practical solution to a problem.

Thanks mc4excel
I tried changing Like to Left and Environ to my username in actual but seems no effect.

If you time the macro before and after these two changes, there should be a minor improvement (how noticable this is will depend on the sample size being tested)

However the most noticable time savings would be from the other two tips:

• using Dir over FSO to return files and/or folders (tip 1) was over 3x faster (tested on my own projects) and
• doing calculations in array instead of workbook (tip 4) a lot faster (Theoretically up to 60x faster from others feedback)

Disclaimer: Your mileage may vary. The savings you achieve probably wont be as high as the figures I quoted. I say that because your code will still need to use Shell and with Tip 4 you don't have a lot of workbook calculations and you already switched to Manual Calculation before doing these.

I will look into DIR function

Can you please give me your thoughts on the last 3 lines of my post#4 in this thread?

I have created a .search-ms folder via vba which is showing all files in that folder.

Now I am thinking to read folder but the problem is ".search-ms" folder is not actually a folder (its XML file rather , that looks list folder from inside but its icon and extension is not that of a yellow folder.

Have you actually timed the parts of your code to see where the bottlenecks are?

This 2 parts take too much time.

Please Login or Register  to view this content.

That's not really specific enough. Comment out the GetDetailsOf lines and see how long it takes without them compared to with them.

these 2 loops took 45 seconds for while the rest of the code took 15 seconds. This duration is for 27 files in total from mainfolder and its sub folders.

How long do those two loops take without the GetDetailsOf lines, and how long with them?

With GetDetailsOf = 45 seconds
without them = 1 seconds
but what should I use as a substitute for GetDetailsOf

I commented out the code like this

Please Login or Register  to view this content.

Well, if I remember the numbers correctly (you should really put the details in the code as you probably won't remember later what 18 means!) the first tow are the file name and modified date? If so, you don't need GetDetailsOf for either of those as you can get them from the File directly.

Here are the names of properties that I am getting with GetDetailsOf

Please Login or Register  to view this content.

Originally Posted by rorya

you don't need GetDetailsOf for either of those as you can get them from the File directly.

means with FSO?

Yes - like this:

Please Login or Register  to view this content.

Originally Posted by rorya

Yes - like this:

Please Login or Register  to view this content.

Great piece of advice @rorya. Thanks.That reduce the time from 15 seconds to 6 seconds for 28 files

Can this method also get the following properties as well

Please Login or Register  to view this content.

Hi Imram,

If you use Power Query and select a folder, it will give you all the files in that folder with all the file attributes. You can then easily filter down to what you want. See:
https://www.mssqltips.com/sqlservert...g-power-query/

No, I'm afraid not (otherwise I'd have added it ).

Alright rory thanks a lot for your nugget of wisdom. I hope it will reduce time for 100's of files as well as it reduced 9 seconds for 28 files.


@sir MarvinP

I don't see the "Tags" and "Title" properties in the list of attributes in the tutorial. Can get these properties? with PQ?

Do the Tags and Titles show in a directory or file list when you use PQ? If they do then you can filter by them before doing your next step.

Explain what Tags and Titles are and I'll look further.

Tags and title screenshot

Sorry Imram,

I don't know how to pull those out of a directory listing. I do use Directory Opus and see I can display them, but don't know how Power Query would pull them out.

Thanks @sir MarvinP for trying so hard to help out at least I came to know that some of them can be retrieved by PQ.

Originally Posted by Ravisca2003

Hi Imran,

While I understand you are looking for code , let me share a macro based file manager which I got and use it personally. It is quite robust as per my usage and does the job pretty well. You may also refer the macro written the same.

It can help you to build index or any folder with each file in that folder and subfolder within to last node available.

Thanks Ravisca for this tool.
But as you know I was after some of the properties of the closed books, which are not taken care of in this tool

But it can be handy when it comes to listing files