asked on

VBA errors with GetAttr() and fso.GetFile()

I created an Excel spreadsheet to help recursively analyze our huge directory structures, recording directory info (number of files, file types, sizes, date modified, full pathname length, etc.). The file attributes are successfully obtained by either of the subject functions. The output will help us reorganize and migrate network shared drives into the cloud. Therefore, each directory and file must be fully included in the analysis.

However, when the VBA program encounters a filename with foreign characters, both functions fail with a "file not found" error (see attachments). If I rename the file, changing the accented e to a plain E, the program works fine. I can ignore the error with error handling, but then I don't have the necessary attributes in the analysis. We are an international organization, so there are *plenty* of special characters.

I cannot find a way to include these files in the analysis. Any help would be greatly appreciated. The environment is Win 10 Office365 Excel 2016 with VBA 7.1.

Dave

Bill Prew

How are you getting the list of files? If you are using DIR() then I can believe it is causing problems. But if you are using FSO I thought that handled international characters okay. Typically a recursive approach works well, let me know if you need examples, or post your code here and we can suggest adjustments.

»bp

Dave Credicott

ASKER

Thanks, Bill !!!
I did some additional testing. Unless my mind is too groggy, here are the results:

With the Dir() and fso.GetFile() statements, the program works until it encounters the problem file.
I disabled the fso.GetFile() statement and the program navigated through the entire directory structure via Dir() with no problems, even recognizing the problem file.
I stripped the program down to just the essential lines. I removed the Dir() statements, and the fso.GetFile() used a hard-coded address of the problem file. Surprisingly, it worked fine.
Then I added the Dir() statement back in, prior to the GetFile() statement with the hard-coded address. The program failed with the same error.

This seems to indicate a combination that makes no sense to me. I don't understand the interplay between these two supposedly independent functions.

The Dir() and fso.GetFile() statements work together properly if there are NO filenames with international characters.
When both Dir() and fso.GetFile() statements are present, the fso.GetFile() works on conventional filenames but fails on a filename with international characters.

This seems to confirm your proposition. So I guess I need to find an fso equivalent to replace the Dir() mechanism. Below is my logic, stripped to the essentials. The collection drives the recursion down through all subdirectories. Each recursion level creates its own DirQueue collection. It works its way down to the bottom, and then back up again. If you have something that has worked for you, I'd appreciate your suggestion! Merci

private function ProcessDir(dirName)
   dim DirQueue as new collection
   ' Iterate through the current directory contents
   thisDir = Dir(dirName, vbDirectory)
   Do Until thisDir = vbNullString
      if thisDir is a subdirectory add it to the DirQueue collection
      if thisDir is a file, use fso.GetFile() to access the attributes
      thisDir = Dir()
   loop
   ' After all directory contents are examined, recurse through each subdirectory
   for each DirQueue in the collection
      ProcessDir(DirQueue)
   next

Open in new window

ste5an

Just a comment:

When doing recursion, ensure that all FSO relevant stuff is done and properly cleaned (set nothing) in your method before executing the recursion call.
Dangling references may cause memory problems, which can also lead to problems, when you have "huge directory structures".

Bill Prew

Okay, I'll dig up a sample...

»bp

ASKER CERTIFIED SOLUTION

Bill Prew

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Dave Credicott

ASKER

Bill,
I changed my program to use the fso.GetFolder function, and it looks very similar to your code. Most importantly, it works fine and has no problem handling international characters in the filename. I'm marking your post as the solution, and I greatly appreciate your help!

Bill Prew

Welcome, glad that was helpful.

»bp