0

In Excel 2010 VBA, I'm using the FileExists property of the FileSystemObject to check whether a file exists. On my computer it works fine. But on another Excel 2010 computer, it reports that the file is not there when in fact we see in Windows Explorer that the file is there. In both cases, the file being checked is on the local hard drive.

I'm checking for the file right after using Shell to unzip the file. I use DoEvents after the unzip. The file is being extracted to the same folder that the zip file is in.

Here's the code:

Dim oShell As Object, oZippedFile As Object, oUnzipTargetFolder As Object
Dim FSO As Object, oFile As Object, oFolder As Object
Dim strZipfileFullpath As Variant, strTargetFolderpath As Variant, strTargetFullpath As Variant 'NOT AS STRINGS!! (Shell error if strings)
Dim bFileCheckFsoFileExist As Boolean

Set oShell = CreateObject("shell.application")
Set FSO = CreateObject("scripting.filesystemobject")

strZipfileFullpath = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", MultiSelect:=False)

Set oFile = FSO.GetFile(strZipfileFullpath)
Set oFolder = oFile.ParentFolder
strTargetFolderpath = oFolder.Path & Application.PathSeparator
strTargetFullpath = strTargetFolderpath & oShell.Namespace(strZipfileFullpath).Items.Item(0).Name

oShell.Namespace(strTargetFolderpath).CopyHere oShell.Namespace(strZipfileFullpath).Items.Item(0) 'this zip has only one file.
DoEvents

bFileCheckFsoFileExist = FSO.FileExists(strTargetFullpath)

Any ideas why this works fine on one computer, but on another computer reports that the file is not there even though it we see that it clearly is?

UPDATE:

I thought I'd add this note in case it may be helpful for others running into the same snag.

The basic underlying issue was that when using Shell to extract a zipped file, the Name property of the zipped file object does not include the extension If Windows Explorer is hiding extensions and the file has a registered extension. For example:

Dim strGofnZipfileFullpath As Variant
Dim oShell As Object, oShellZipfile As Object, oShellZippedFileInZipfile As Object
Dim strShellZippedFileInZipfileFilename As Variant 'NOT AS STRINGS!! (Shell error if strings)

strGofnZipfileFullpath = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", MultiSelect:=False)

Set oShell = CreateObject("shell.application")
Set oShellZipfile = oShell.Namespace(strGofnZipfileFullpath)
Set oShellZippedFileInZipfile = oShellZipfile.Items.Item(0) 'assumes only 1 file in zip. 
        strShellZippedFileInZipfileFilename = oShellZippedFileInZipfile.Name

If Windows Explorer is set to hide extensions and the zipped file has a registered extension, strShellZippedFileInZipfileFilename does not include the extension.

However, the zipped file object (oShellZippedFileInZipfile) also has a Path property which does include the extension. So you can get the filename including extension like this:

strShellZippedFileInZipfileFilename = Right(oShellZippedFileInZipfile.Path, InStr(StrReverse(oShellZippedFileInZipfile.Path), Application.PathSeparator) - 1)
Greg Lovern
  • 958
  • 4
  • 18
  • 36
  • 2
    In the last line `strTargetFullpath` doesn't seem to have been assigned a value... – Tim Williams Jul 29 '14 at 23:41
  • Thanks Tim; I've added that line back in. I tried to simplify it by removing irrelevant lines, but missed including that one. – Greg Lovern Jul 30 '14 at 15:50
  • Have you tried `Dir(strTargetFullpath)` - does it give the same result? – Tim Williams Jul 30 '14 at 15:55
  • Hi Tim, after looking around it seems to me that I shouldn't rely on any single method for checking for file existence; it sounds like any of them can occasionally return a false negative. I'm writing a function to check for file existence using three different methods: fso, Dir, and FileLen. If any of them return true/success, then the function will return true. Only if none of them find the file will the function return false. – Greg Lovern Jul 30 '14 at 16:52

1 Answers1

0

One thing to keep in mind is the folder options within Windows explorer. This may not help you in this case, but I have had a number of fso interactions that have a huge issue with how files are displayed in Windows Explorer. Something as simple as your system is displaying the file extension, and the other is not, can sometimes be the culprit in looking for files.

SelfTaughtCodingIdiot
  • 109
  • 7
  • Thanks vbnoob, in fact the two computers are set differently in that respect; on the one where FileExists works, Windows Explorer is set to display file extensions. On the other one, it is not. I don't understand why that would affect fso, though?? – Greg Lovern Jul 30 '14 at 16:47
  • @GregLovern I don't have a good answer for that. The work around that I came up with was to look at the file name and if it did not include the extension, add it and then run whatever code I needed on the file. You can easily check if that is the case by changing your Window Explorer options and running your code again. If it fails, there's your problem. – SelfTaughtCodingIdiot Jul 30 '14 at 17:04
  • vbnoob, that was indeed the problem. With Windows Explorer set to hide file extensions, 'oShell.Namespace(strZipfileFullpath).Items.Item(0).Name' returns a filename with no extension. Then when fso.FileExists checks for the file's existence, it gets a false negative. – Greg Lovern Jul 30 '14 at 19:40