Loop Through Files In A Folder Using VBA

**DocAElstein** · 09-06-2021, 02:24 PM

_.. continued from a post beforepost

This is post number 15732
Links to here
https://excelfox.com/forum/showthrea...ll=1#post15732
https://excelfox.com/forum/showthread.php/1324-Loop-Through-Files-In-A-Folder-Using-VBA?p=15633&viewfull=1#post15732
https://excelfox.com/forum/showthrea...ge2 #post15732
https://excelfox.com/forum/showthread.php/1324-Loop-Through-Files-In-A-Folder-Using-VBA/page2 #post15732

_... continued from last post
Another example
Just a practical example. .. I am curious to see if some files containing information about my drivers on a computer change when I install a simple HP Printer, ( HP 1050 ).
The difficulty in doing this is that I get a lot of files in various sub folders. A couple of things help me
_ The first is getting a tree like or “explorer” type view of the files. That is what my recursion macro does.

So, here we go again.
I make a new worksheet and make sure its active when I run the macro, so I still use Set Ws = ActiveSheet
I change the path appropriately to where I have copied the 2 folders which usually have a lot of files related to drivers ( By the way you usually find those two folders at somewhere like C:\Windows\System32 )

The next screen shot shows where I copied these 2 folders to. So the path I need to hard code into my macro is
F:\DriversDruckerFromPoohFirstPetraRotWin7Starter\ Aug2021Vorhere
Poohs-Drivers-BeforePath.jpg

( I don’t need to concern myself about those extra 3 pics I have at that location, since my macro ignores files in the first instance, and only looks at folders there. ( As it goes into those 2 folders and any sub folders, then it will look for both folders and files ) )

Looking at the results, I see an enormous amount of files, and the results go over to about column G
PoohsDriversBeforeResults.JPG

We note that the tree like / explore like structure output starts at cell B3, as determined by this code line

Code:

Dim celTL As Range: Set celTL = Ws.Range("B3") 'Top left of where Listing should go

For this example I will use the same sheet, but now modify that code line to start at H3. Then on changing the path to F:\DriversDruckerFromPoohFirstPetraRotWin7Starter\ After Disc Install, I run the macro again to get the new results alongside the Before results. ( I also 'disabled the code line Ws.Range("B3:F30").ClearContents ' This line only needed for demo code
)

Results
At F:\DriversDruckerFromPoohFirstPetraRotWin7Starter\ After Disc Install\drivers I have an extra
usbscan.sys

I then go a long way down in DriverStore to find this lot

F:\DriversDruckerFromPoohFirstPetraRotWin7Starter\ After Disc Install\DriverStore\FileRepository\hpscanminidrv_dj1050_j 410.inf_x86_neutral_f8801e0bb3ab9eb4	hpscanminidrv_dj1050_j410.inf_x86_neutral_f8801e0b b3ab9eb4
	HPScanMiniDrv_DJ1050_J410.INF
	hpscanminidrv_dj1050_j410.PNF
	HPScanMiniDrv_DJ1050_J410x32.CAT

F:\DriversDruckerFromPoohFirstPetraRotWin7Starter\ After Disc Install\DriverStore\FileRepository\hpscanminidrv_dj1050_j 410.inf_x86_neutral_f8801e0bb3ab9eb4\drivers		drivers

F:\DriversDruckerFromPoohFirstPetraRotWin7Starter\ After Disc Install\DriverStore\FileRepository\hpscanminidrv_dj1050_j 410.inf_x86_neutral_f8801e0bb3ab9eb4\drivers\scann er

F:\DriversDruckerFromPoohFirstPetraRotWin7Starter\ After Disc Install\DriverStore\FileRepository\hpscanminidrv_dj1050_j 410.inf_x86_neutral_f8801e0bb3ab9eb4\drivers\scann er\x32


F:\DriversDruckerFromPoohFirstPetraRotWin7Starter\ After Disc Install\DriverStore\FileRepository\hpvpl04.inf_x86_neutra l_4dd0ab13ea43b845	hpvpl04.inf_x86_neutral_4dd0ab13ea43b845
	HPMACRONAMES.gpd
	hpvpl04.cat
	hpvpl04.inf
	hpvpl04.ini
	hpvpl04.PNF
	hp_deskjet_2050_04.gpd
	locale.gpd
	stdnames.gpd

F:\DriversDruckerFromPoohFirstPetraRotWin7Starter\ After Disc Install\DriverStore\FileRepository\hpvpl04.inf_x86_neutra l_4dd0ab13ea43b845\i386		i386
		hpfime50.dll
		hpinkcoi8911.dll
		hpinksts8911.dll
		hpinksts8911LM.dll
		hpvpldrv04.dll
		hpvplres04.dll
		hpvplui04.dll
		unidrv.dll
		unidrv.hlp
		unidrvui.dll
		unires.dll

Worksheet: FirstPetraPoohWin7

‘wbCodesDriversDruckerFromPoohFirstPetraRotWin7S tarter.xlsm’ https://app.box.com/s/k9hqq3mq7972kcucn428ghcd4gciquhn

Transgression Vamp
I am wondering at this point if I might like to see all the files in some way in a forum post. A table is going to be very big. Putting it in a code window will help to get the scroll bars, but I expect it will still be way to big.
A simple text file is probably as best you can get. This looses no ease of visibility in the vertical because a line feed is a line feed.
The horizontal is a problem. Lets look at ways to get over that
Here are some interesting ideas to get over the problem of a typical forum editor “eating” spaces more than 1
http://www.eileenslounge.com/viewtopic.php?f=26&t=22603 - But that in itself produces a lot of extra text.
A nice coincidence associated with how Excel uses the clipboard gives us another possibility:
( Once again the vertical is not an issue. Mostly in computing we use the traditional “invisible” characters in a text string which historically were needed to tell a printer to feed another line ( Line Feed , vbLf ) and to make the printing carriage return to the left ( Carriage Return, vbCr ) )
When Excel copies to the clipboard, an Excel Clipboard appears to store links allowing a subsequent paste to transfer all formatting from a cell, but also, interesting to us, a windows clipboard holds
_ the values,
_the two characters to indicate a new line ( vbCr & vbLf )
_ and, most useful to us, a “cell wall” or cell separator is indicated by another “invisible” character , the tab ( vbTab )

............................................._