I'm trying to work with the shell.application
object to unzip some files but am having trouble passing in a file path parameter as a string. I notice that when I pass in my file path as a variant
it works but a string
only works sometimes.
I came up with these test cases but so not see an obvious pattern.
Sub TestShellPaths()
Dim oShell
Dim oTempFolder
Dim aVariantPath
Dim aStringPath As String
Set oShell = CreateObject("shell.application")
aVariantPath = Environ("temp")
aStringPath = Environ("temp")
Debug.Print TypeName(aVariantPath)
'String (but really Variant/String according to Locals window)
Set oTempFolder = oShell.Namespace(aVariantPath)
Debug.Print "oTempFolder.Namespace(aVariantPath) worked? " & IIf(Not oTempFolder Is Nothing, "Yes", "NO :(")
'oTempFolder.Namespace(aVariantPath) worked? Yes
Debug.Print TypeName(Environ("temp"))
'String (and actually is String according to Watch window)
Set oTempFolder = oShell.Namespace(Environ("temp"))
Debug.Print "oTempFolder.Namespace(Environ('temp')) worked? " & IIf(Not oTempFolder Is Nothing, "Yes", "NO :(")
'oTempFolder.Namespace(Environ('temp')) worked? Yes
Debug.Print TypeName(aStringPath)
'String (and actually is String according to Locals window)
Set oTempFolder = oShell.Namespace(aStringPath)
Debug.Print "oTempFolder.Namespace(aStringPath) worked? " & IIf(Not oTempFolder Is Nothing, "Yes", "NO :(")
'oTempFolder.Namespace(aStringPath) worked? NO :(
Debug.Print TypeName(CStr(aVariantPath))
'String (and actually is String according to Watch window)
Set oTempFolder = oShell.Namespace(CStr(aVariantPath))
Debug.Print "oTempFolder.Namespace(cstr(aVariantPath)) worked? " & IIf(Not oTempFolder Is Nothing, "Yes", "NO :(")
'oTempFolder.Namespace(cstr(aVariantPath)) worked? Yes
aStringPath = aVariantPath
Debug.Print TypeName(CStr(aStringPath))
'String (and actually is String according to Watch window)
Set oTempFolder = oShell.Namespace(CStr(aStringPath))
Debug.Print "oTempFolder.Namespace(cstr(aStringPath)) worked? " & IIf(Not oTempFolder Is Nothing, "Yes", "NO :(")
'oTempFolder.Namespace(cstr(aStringPath)) worked? Yes
Dim fso As New FileSystemObject
Debug.Print TypeName(fso.GetFolder(aStringPath))
'Folder (but really Object/Folder according to Watch window)
Set oTempFolder = oShell.Namespace(fso.GetFolder(aStringPath))
Debug.Print "oTempFolder.Namespace(fso.GetFolder(aStringPath)) worked? " & IIf(Not oTempFolder Is Nothing, "Yes", "NO :(")
'oTempFolder.Namespace(fso.GetFolder(aStringPath)) worked? NO :(
Debug.Print TypeName(fso.GetFolder(aStringPath).Path)
'String (and actually is String according to Watch window)
Set oTempFolder = oShell.Namespace(fso.GetFolder(aStringPath).Path)
Debug.Print "oTempFolder.Namespace(fso.GetFolder(aStringPath).Path) worked? " & IIf(Not oTempFolder Is Nothing, "Yes", "NO :(")
'oTempFolder.Namespace(fso.GetFolder(aStringPath).Path) worked? Yes
aVariantPath = fso.GetFolder(aStringPath)
Debug.Print TypeName(aVariantPath)
'String (but really Variant/String according to Locals window)
Set oTempFolder = oShell.Namespace(aVariantPath)
Debug.Print "oTempFolder.Namespace(aVariantPath) worked? " & IIf(Not oTempFolder Is Nothing, "Yes", "NO :(")
'oTempFolder.Namespace(aVariantPath) worked? Yes
aStringPath = fso.GetFolder(aStringPath)
Debug.Print TypeName(aStringPath)
'String (and actually is String according to Locals window)
Set oTempFolder = oShell.Namespace(aStringPath)
Debug.Print "oTempFolder.Namespace(aStringPath) worked? " & IIf(Not oTempFolder Is Nothing, "Yes", "NO :(")
'oTempFolder.Namespace(aStringPath) worked? NO :(
End Sub
Our summary results are
Object type
| Failed to create a Shell Object
| Succeeded in creating a Shell Object
|
String
| 2
| 4
|
Variant/String
|
| 2
|
Object/Folder
| 1
|
|
So what do we have here? Not a ton of consistency from the String datatype, using Folder is totally out. Seems like the best way to get this to really reliably work is to use a variant. This technically matches the signature for the `NameSpace` methodShell.NameSpace(ByVal vDir As Variant) As Folder
It's definitely not expected that the cast from string to variant is not successful sometimes.
Keeping in mind how VBA would handle this
Sub TestVariantsAndStrings()
Dim v As Variant
v = "abc"
Dim s As String
s = "123"
VariantParameter v
VariantParameter s
StringParameter CStr(v)
StringParameter s
End Sub
Function VariantParameter(myVar)
Debug.Print myVar
End Function
Function StringParameter(mystring As String)
Debug.Print mystring
End Function
Notice that on
StringParameter CStr(v)
I had to cast the variant to a string otherwise I get a "ByRef argument type mismatch". This makes sense. Who knows if that variant has implented a default property which returns a string? I don't thinkg the compiler knows or maybe it just doesn't check because the Office VBA IDE doesn't even let you make that kind of change to a class. You need to export your class as a .bas file, edit it in a text editor to implement the default property using the Attribute keyword, then import it back in. (Chip Pearson has a way better write up than what I can provide.) So VBA doesn't like implicit variant --> string conversions but is fine with implicit string-->variant conversion. This kind of logic is called boxing. It makes sense.
With all that considered the restriction on the shell NameSpace method still does not make sense to me.
Good learning experience but not much gained.
My final unzip function looks like this and it seems to work well
Function UnZip(zipFilePath As String) As String
Dim oShell
Dim oFolder
Dim sDir
Dim oDest
Dim newFolderName, newfolderpath
sDir = zipFilePath
Set oShell = CreateObject("shell.application")
Set oFolder = oShell.Namespace(sDir)
Set oDest = oShell.Namespace(Environ("TMEP"))
Dim fso As New FileSystemObject
newFolderName = Replace(fso.GetFileName(zipFilePath), "." & fso.GetExtensionName(zipFilePath), "")
newfolderpath = fso.BuildPath(Environ("Temp"), newFolderName)
newfolderpath = MakeFolder(newfolderpath)
Set oDest = oShell.Namespace(newfolderpath)
If Not oDest Is Nothing Then
oDest.CopyHere oFolder.Items
UnZip = oDest.Self.Path
Else
UnZip = ""
End If
End Function
Private Function MakeFolder(pathToMake, Optional attempts As Integer = 0)
Dim fso As New FileSystemObject
pathToMake = pathToMake & IIf(attempts = 0, "", " (" & attempts & ")")
If Not fso.FolderExists(pathToMake) Then
MkDir pathToMake
MakeFolder = pathToMake
Else
MakeFolder = MakeFolder(pathToMake, attempts + 1)
End If
Set fso = Nothing
End Function