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.

Are vSource and vDest both string variables? Try Variant. – Tim Williams Jun 30 '15 at 03:28 — Tim Williams, Jun 30 '15 at 03:28

score 10 · Accepted Answer · answered Jun 30 '15 at 04:29

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

score 3 · Answer 2 · answered Jul 01 '15 at 15:23

3

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

answered Jul 01 '15 at 15:23

tmoore82

1,857
1
27
48

I know it's a bit older but I tried to find a solution for my question which seemed to be the same, but turns out to have a flavor added: – Bernd Stoeckel Dec 14 '16 at 13:47
I used everything above (already had set variables to variants) and tried the parentheses but had the same error. Looking at every detail, I found out that the foldername is first detected as 'my documents' and can't be set because the folder's real name is 'documents'. is this related or should I create a separate question? – Bernd Stoeckel Dec 14 '16 at 13:49
I'd create a new question, just because this is old. :) – tmoore82 Jan 13 '17 at 02:41

score 0 · Answer 3 · answered Oct 28 '22 at 06:36

for my case. i used the shell

Set oShell = CreateObject("Shell.Application")
x= oShell.xxxx
y= oShell.Namespace(x)

in two different lines. seems like i'll need to re initialized to able to use on second line. eg

Set oShell = CreateObject("Shell.Application")
x= oShell.xxxx
y= CreateObject("Shell.Application").Namespace(x)

then only it works.

Excel VBA Shell.Namespace returns Nothing

3 Answers3

Linked