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
– Tim Williams

2015-06-30 03:28:47 +00:00
Commented Jun 30, 2015 at 3:28 — Tim Williams
– Tim Williams, Commented Jun 30, 2015 at 3:28

Tim Williams · Accepted Answer · 2015-06-30 04:29:20Z

12

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

answered Jun 30, 2015 at 4:29

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tmoore82 · Accepted Answer · 2015-07-01 15:23:35Z

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 1, 2015 at 15:23

tmoore82

1,8753 gold badges27 silver badges49 bronze badges

3 Comments

Bernd Stoeckel Over a year ago

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 Over a year ago

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?

tmoore82 Over a year ago

I'd create a new question, just because this is old. :)

Alia Ramli Ramli · Accepted Answer · 2022-10-28 06:36:15Z

0

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.

answered Oct 28, 2022 at 6:36

Alia Ramli Ramli

4456 silver badges18 bronze badges

Collectives™ on Stack Overflow

Excel VBA Shell.Namespace returns Nothing

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related