Shell.namespace not accepting string variable, but accepting string itself

Question

I have a code, in which I want to loop through files in a folder, check their built-in or custom document properties (without opening them), and later open those, which are meant to be open. To do this, I'm using Shell and to set the folder I'm using Shell.Namespace.

The problem is with the Namespace, I guess. When I use a variable strSuborCesta for the path it doesn't work. When I print the variable strSuborCesta into immediate window and use the printed string inside the Shell.Namespace("....") it does work.

By it doesn't work I mean I get:

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

when I try to loop through the files in folder (which is not set in that case, so I understand why the error occurred, but don't understand why it's not accepting a string variable)

The path is correct in both ways. But I need it to be a variable, not a hardcoded string.

Where do I error?

Is there any better way, to check document properties (like comments, title, author, etc.) without opening the Excel files?

Below is the section (currently just in testing phase) that is giving me a hard time.

str[name of variable] variables are string data types. sFile, oShell, oDir are as Variants

 '--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'get a root path
strPriecinokCesta = ThisWorkbook.Path 'path to this file
strPriecinokCesta = Left(strPriecinokCesta, Len(strPriecinokCesta) - (Len(strPriecinokCesta) - InStrRev(strPriecinokCesta, "\"))) 'root path is one level above this file
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'input files are in a subfolder
strSuborCesta = strPriecinokCesta & "Zdroje\"
strSuborPripona = "Formular_BD_kotolna*.xls" 'name of a file with extension
strSuborNazov = Dir(strSuborCesta & strSuborPripona) 'actual file name
'--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'get access to the file system
Set oShell = CreateObject("Shell.Application")
Set oDir = oShell.Namespace(strSuborCesta) '<----this will produce an error. In contrast to using a hard coded string. Why?

For Each sFile In oDir.Items '<---- run time error 91 occurs on this line
    Debug.Print test & " : " & oDir.GetDetailsOf(sFile, 24) 'comments
Next

Does strSuborNazov actually contain the full path to the file? — SierraOscar
– SierraOscar, Commented Nov 23, 2015 at 10:21
Sorry, I had an error there, used different variable with a file name strSuborNazov . The correct one should be strPriecinokCesta that is the folder path. Edited. Thanks for the comment. — kolcinx
– kolcinx, Commented Nov 23, 2015 at 10:27
To my previous comment...strSuborCesta is the correct variable, that should be used. That is the folder, with input files. — kolcinx
– kolcinx, Commented Nov 23, 2015 at 10:33

PASUMPON V N · Accepted Answer · 2015-11-23 10:19:18Z

2

Variable strSuborNazov should be a variant

Dim strSuborNazov as variant 

oShell.Namespace(strSuborNazov)

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

answered Nov 23, 2015 at 10:19

PASUMPON V N

1,1862 gold badges10 silver badges17 bronze badges

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

2 Comments

Alan Elston Over a year ago

This made no difference for me

Ivan Bezerra Allaman Over a year ago

Me too. Not work for me!!!

Alan Elston · Accepted Answer · 2024-01-26 20:41:21Z

0

High

Had this prob as well, with something like this

 Set objWSOFolder = objWSO.Namespace(Parf)

Saw this https://microsoft.public.access.narkive.com/Jl55mts5/problem-using-shell-namespace-method-in-vba#post5 so tried

Set objWSOFolder = objWSO.Namespace((Parf))

That worked.

I noticed later, that I did not have the problem if at least part of the path was given as a literal string. So I tried something like this

Set objWSOFolder = objWSO.Namespace("" & Parf)

That worked too. Whoopy Hoo, 4 me & U

Alan

edited Jan 26, 2024 at 20:41

answered Jan 26, 2024 at 20:32

Alan Elston

1191 gold badge4 silver badges12 bronze badges

Collectives™ on Stack Overflow

Shell.namespace not accepting string variable, but accepting string itself

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related