Solved Access VBA ways to obtain file system directory listing

[mdlueck]

Greetings,

I am seeking examples of Access VBA (running in MSO 365 Build 16.0.x 32-bit) of how to obtain file system directory listings.

Seems Application.FileSearch is now deprecated / removed. I have heard of using some Microsoft Office Object solution. There might be others that come with standard MS Office. Please kindly advise.

If there is need to utilize an add-on versus native VBA code, then I would like to include the constants in a VBA Include file and use Late-Binding rather than require an add-on be checked within each instance of Access being utilized.

I am thankful,

 

[theDBguy]

If you're looking for a way to list the files in a directory/folder, then have a look here.

List Files in a Folder and its Subfolders

While trying to answer a question in the MSDN Forums, I realized although there are already a lot of examples on how to list all the files i...

thedbguy.blogspot.com

 

[The_Doc_Man]

It would take a bit of studying, but there is always the "FileSystemObject" (written with no spaces when being referenced as an object). It comes from the Windows Scripting Library.

FileSystemObject object

Office VBA reference topic

learn.microsoft.com

Browse around with that reference as your starting point. You can get a file (as an object), explore folders via their collection of files, get file properties... great fun.

 

[mdlueck]

If you're looking for a way to list the files in a directory/folder, then have a look here.

List Files in a Folder and its Subfolders

While trying to answer a question in the MSDN Forums, I realized although there are already a lot of examples on how to list all the files i...

thedbguy.blogspot.com

Greetings theDBguy,

Wow is it complicated to get a directory listing in VBA! Many LOCs of VBA needed.

In the Open Object Rexx (ooRexx) language, it is a single call to SysFileTree.

I was able to get your example code working on my work computer, get the needed output of qualifying files in a directory.... so off to copy your example code and stitch it into my utility.

I am thankful,

 

[theDBguy]

Greetings theDBguy,

Wow is it complicated to get a directory listing in VBA! Many LOCs of VBA needed.

In the Open Object Rexx (ooRexx) language, it is a single call to SysFileTree.

I was able to get your example code working on my work computer, get the needed output of qualifying files in a directory.... so off to copy your example code and stitch it into my utility.

I am thankful,

Glad to hear you got it to work. Good luck with your project.

 

[mdlueck]

Greetings theDBguy,

Question about your sample code....

Why did you prototype a variable for the fsoFile object, but then not use that variable and instead stick it into a Variant object? I flipped it back the other way, and seems to work using the fsoFile defined as an Object? (I dislike any Variants in my VBA code, if at all possible.)

I searched the sample code for that fsoFile usage, and it appears only to be a defined and forgotten variable.

I am thankful,

 

[theDBguy]

Greetings theDBguy,

Question about your sample code....

Why did you prototype a variable for the fsoFile object, but then not use that variable and instead stick it into a Variant object? I flipped it back the other way, and seems to work using the fsoFile defined as an Object? (I dislike any Variants in my VBA code, if at all possible.)

I searched the sample code for that fsoFile usage, and it appears only to be a defined and forgotten variable.

I am thankful,

Good catch! That must have been a remnant from previous versions (you're using version 1.3, right?) when I made some changes and updates to the code.

 

[mdlueck]

Good catch! That must have been a remnant from previous versions (you're using version 1.3, right?) when I made some changes and updates to the code.

No..... the link you provided above appears to have had me download your v1.1 version of the example. Dated 2018-03-29. At least that is what Form_frmMain states as the version and date. Very odd.

I am thankful,

 

[theDBguy]

No..... the link you provided above appears to have had me download your v1.1 version of the example. Dated 2018-03-29. At least that is what Form_frmMain states as the version and date. Very odd.

I am thankful,

Ah, interesting. In any case, I'm glad you found and fixed it for me. Thanks!

 

[mdlueck]

Ah, interesting. In any case, I'm glad you found and fixed it for me. Thanks!

So was fsoFile or Variant your preferred data type? If Variant, why so?

I am thankful,

 

[theDBguy]

So was fsoFile or Variant your preferred data type? If Variant, why so?

I am thankful,

If I was pulling a specific file, I would use fsoFile.

Set fsoFile = fso.GetFile("c:\folder\filename.ext")

However, when using a For Each, I usually just use var.

For Each var in SomeCollection

Hope that makes sense...

 

[mdlueck]

If I was pulling a specific file, I would use fsoFile.

Set fsoFile = fso.GetFile("c:\folder\filename.ext")

However, when using a For Each, I usually just use var.

For Each var in SomeCollection

Hope that makes sense...

Interesting.... why the inconsistency? Why the use at all of the dreaded Variant data type?

Personally I only use Variant when I need a consistent variable which can itself hold a variety of different data types. Seems like this use case is only able to end up with a fso object, so why not type the variable as an Object?

I am thankful,

 

[theDBguy]

Interesting.... why the inconsistency? Why the use at all of the dreaded Variant data type?

Personally I only use Variant when I need a consistent variable which can itself hold a variety of different data types. Seems like this use case is only able to end up with a fso object, so why not type the variable as an Object?

I am thankful,

Well, you may expect to have the same items inside SomeCollection, but there's a slight possibility that it may contain other things, I guess. Either that or I was just lazy.

 

[mdlueck]

Well, you may expect to have the same items inside SomeCollection, but there's a slight possibility that it may contain other things, I guess. Either that or I was just lazy.

Oh, meaning you typically use Variants for that type of work rather than Variants in some cases, Objects in other cases.... what ever is most appropriate for that section of code?

I am thankful,

 

[The_Doc_Man]

I would use Variant any time there was a chance that you could reasonably expect to come across a null - because the Variant is the only data type that can hold a null response AS a null. Non-variant variables cannot hold a null. You can test a Variant for containing a null, but if your variable is a type that won't accept nulls, your test must be to check after-the-fact whether you threw a run-time error "Invalid use of Null". Which means you have to have an error handler that would trap that error for you in a way you could use it.

Otherwise, your point of using the correct data type is preferable, in particular if you are going to diddle with properties of that data type. That is because Intellisense can find the properties if the data type is correctly identified.

 

[mdlueck]

I would use Variant any time there was a chance that you could reasonably expect to come across a null - because the Variant is the only data type that can hold a null response AS a null.

Ah, very good point! Perhaps I should become less anti-Variant variable data type.

I guess when I code, even error handlers, I anticipate things working well enough that things return still the expected data types.

You do raise a valid point that should things go very poorly, there is the chance of ending up with receiving a null. The old "Second Tuesday of the week" scenario. I happen to have all of my "Second Tuesday's of the week" open and available.

I am thankful,

 

[The_Doc_Man]

I happen to have all of my "Second Tuesday's of the week" open and available.

When I was still a Navy contractor, I used to hate Wednesday mornings after the 2nd and 4th Tuesdays. We NEVER knew how many systems would need rollbacks. Darned place looked like a frickin' Walmart sometimes.

 

[Isaac]

I'll chime in with my 2 cents. I think in cases where you can use DIR (perhaps with recursion, perhaps not), it will be many orders of magnitude faster than FSO. Don't get me wrong, FSO is actually my personal preference and I like it a lot as it's very easy to understand what is happening. But I've had >1 cases where DIR was far faster - seconds as opposed to minutes.

 

[mdlueck]

But I've had >1 cases where DIR was far faster - seconds as opposed to minutes.

???? How would you suggest getting the output of command line DIR back into Access? There is no queue to VBA interface that I know of.... unlike ooRexx which has Rxqueue that you can pipe to and that interfaces directly to the ooRexx memory space.

I am thankful,

 

[mdlueck]

Well I finished off the Access VBA tool today. I got the File system searching perfected yesterday, stubbed off code that would get called on a per-file basis. Today's task was merely doing the VBA code via String manipulation to heal the out of spec XML files.

Again, drawing from my ooRexx experience, I coded the VBA equivalent to how we successfully processed XML instructions, code I worked on during 2008 - 2009. Memories of how to successfully modify XML via string manipulation was that well locked into my memory.

When the code was finished, it updated hundreds of individual XML files needing the same edits literately instantly. "Click"... no hour glass, no nothing... Check the "done" directory I made to write the output to.... hundreds of pretty XML files waiting. "Love it when a plan comes together!"

I am thankful,