Excel VBA Shell.Namespace returns Nothing

Question

I'm trying to extract a .CAB file using Excel VBA, but I'm getting the following error:

Run-time error '91': Object variable or With block variable not set

I usually get this when I forget to use Set with an Object, but I've checked for that.

All examples I can find are variations on this theme:

Private Function DeCab(vSource, vDest) As Long
    Dim objShell, objFileSource, objFileDest As Object
    Set objShell = CreateObject("Shell.Application")
    Set objFileSource = objShell.Namespace(vSource)
    Set objFileDest = objShell.Namespace(vDest)
    Call objFileDest.MoveHere(objFileSource.Items, 4 Or 16) 'Fails here
    Decab = objFileDest.Items.Count
End Function

It's not failing on the Set line, but it's setting both objFileSource and objFileDest to Nothing even though I've confirmed vSource and vDest exist.

To confirm it has nothing to do with the .CAB file, I've also tried it without setting objFileSource and checking the value of objFileDest after it's set. It still returns Nothing. Why would that be? I'm on Windows 7, 64-bit, running Office 2010.

Answer 1

Your parameters must be submitted as Variant, not String

Sub Tester()

    Dim src, dest                      '<< works
    'Dim src As String, dest As String '<< gives the error you see

    src = "D:\temp\test.zip"
    dest = "D:\temp\unzip"

    DeCab src, dest

End Sub

https://msdn.microsoft.com/en-us/library/windows/desktop/bb774085(v=vs.85).aspx

Answer 2

Tim's answer is correct. I found an alternative, as well:

Private Function DeCab(vSource, vDest) As Long
    Dim objShell, objFileSource, objFileDest As Object
    Set objShell = CreateObject("Shell.Application")
    Set objFileSource = objShell.Namespace((vSource)) '<-extra parentheses
    Set objFileDest = objShell.Namespace((vDest)) '<-extra parentheses
    Call objFileDest.MoveHere(objFileSource.Items, 4 Or 16) 'Fails here
    Decab = objFileDest.Items.Count
End Function

When you place an object in parentheses in VBA, it returns the default value of the object. Apparently, objShell.Namespace can't handle a pointer. It can only handle a string literal. Changing the signature to the following also works if you're passing in Strings:

Private Function DeCab(ByVal vSource, ByVal vDest) As Long