This is related to, and a follow up question to, this VBA question post: https://eileenslounge.com/viewtopic.php ... 61#p313961 ( eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 )
I expect the problem is specific to XP though.
The stand is that at this stage I have a few solutions and the one offered most recently is/ was looking like the best, but.. it 'aint working for me in XP, :(
We are talking about the WSO (windows shell object, Microsoft Shell Controls And Automation) way using the ExtendedProperties to get at file and folder properties using these sort of pseudo code ideas
objWSOFolder.ParseName("sample.wmv").ExtendedProperty("System.NameOfPropertyYouWant")
So I had previously been looking at the similar but, prhaps "lesser", more commonly known about and used, windows shell object way, (WSO way, WSO Properties), to get properties of folders and files. This was seen to be OK and does work for me across the range XP, Vista, Win 7 Win 10, Win 11.
A couple of annoyances when writing VBA coding to automate this were
_(i) being sometimes dependant on using a name that might be different for different language version ( language version of what I am not too sure yet)
_(ii) The order given for the properties varied quite a bit across the operating systems, so using the item number / order number in a Operating System universal coding was a complication, (and using the property name was a complication due to _(i) )
_(iii) What properties are available varies across the operating systems.
Another solution was offered….
So, I been investigating….
(To help avoid confusion I will try to distinguish better between the more commonly used and known about WSO Properties and this newly offered ExtendedProperty propkey.h header stuff Properties, since a lot of people seem to think the WSO Properties are the extended advanced stuff compared to other much more commonly used and more restricted ways to get at file and folder properties.
I will refer to the "lesser" way as WSO Properties, or shell object properties
, and
this "better" ( perhaps more fundamentals low level way?), as WSO Propherkeys. )
Initial investigation of the WSO Propherkeys looked encouraging, - it seemed to overcome the problems. Now I hit a problem I can’t yet find a solution to, - it is not giving me any results when running in XP. I tried a few computers, XP Home, XP Professional, XP Media Center Edition, Service Packs 2 and 3 – same results – nothing.
Typical VBA Codings, working in Vista upwards, don't error in XP, they just don't give me any results.
( I am not expecting all the 1054 listed Propherkeys to work or give me meaningful results, but I can get a lot of useful ones, consistently, from a lot of computers with operating systems Vista, Win 7, Win 8,1, Win 10, Win 11. (Excels 2003, 2007, 2010, 2013 )
XP not working is particularly surprising, to me at least, as
_a) I found that the WSO properties were working slightly best in XP,
, and related to _a)
_b) I was surprised because I assumed that the WSO Properties are derived from or are some sort of sub set or "lesser" version of the WSO Propherkey?
Here is a simple example with just one similar propherkey and property, the Size
I will use Late Binding for ease of sharing, but I have checked extensively and never seen any difference in Late or Early Binding
Working on the sample .wmv file, ( put in the same folder as the Excel File with coding in it ), this will tell me the
_Rem 1 WSO Size property is 628 KB or 629 KB on all my computers, operating systems, Excel versions.
_Rem 2 Almost the same for the WSO Size Propherkey, I will get 643170, except for when using XP, in which case I get no result. The same is true for any other WSO Propherkey that consistently works for operating system Vista upwards – no result comes for XP
( I have checked other example file types, and folders and different properties etc. etc, - same results )
Code: Select all
Option Explicit
Sub SimpleWSOPropertiesAndPropherkeysLateBinding() ' https://eileenslounge.com/viewtopic.php?f=21&t=40701 https://eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837 https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff/page12 https://www.excelfox.com/forum/showthread.php/2559-Notes-tests-text-files-manipulation-of-text-files-in-Excel-and-with-Excel-VBA-CSV-stuff?p=23979&viewfull=1#post23979 https://www.excelfox.com/forum/showthread.php/2951-TestsExplorerWSO?p=23973&viewfull=1#post23973
' Late Binding WSO
Dim objWSO As Object
Set objWSO = CreateObject("shell.application") '
Dim objWSOFolder As Object: Set objWSOFolder = objWSO.Namespace(ThisWorkbook.Path) ' If using a variable for the path, then this may be needed Set objWSOFolder = objWSO.Namespace((ThisWorkbook.Path & "")) ' Set objWSOFolder = objWSO.Namespace((ThisWorkbook.Path & "")) ' (( & "" )) ' https://stackoverflow.com/questions/33868233/shell-namespace-not-accepting-string-variable-but-accepting-string-itself/77888851#77888851 https://microsoft.public.access.narkive.com/Jl55mts5/problem-using-shell-namespace-method-in-vba#post5
' Rem 1 WSO Property
Dim FldrItm As Object
Set FldrItm = objWSOFolder.Items.Item("sample.wmv")
Debug.Print objWSOFolder.GetDetailsOf(FldrItm, 1): MsgBox prompt:=objWSOFolder.GetDetailsOf(FldrItm, 1), Title:="sample.wmv Size Property" ' The 1 is the Size WSO property number/index for all operating systems, and a few other WSO properties have the same number for all operating systems , BUT NOT all of them ' 628 KB or 629 KB
Debug.Print
' Rem 2 WSO Propherkey ( ExtendedProperty )
Dim objWSOPassName As Object: Set objWSOPassName = objWSOFolder.ParseName("sample.wmv")
Debug.Print objWSOPassName.ExtendedProperty("System." & "Size"): MsgBox prompt:=objWSOPassName.ExtendedProperty("System." & "Size"), Title:="sample.wmv Size Propherkey" ' 643170
Debug.Print
End Sub
I know a few workarounds, of varying complexity and usefulness, depending on what properties are wanted, which I will post here for future reference if I can’t get the WSO Propherkeys working in XP. But the WSO Propherkeys is an attractive simpler solution, so a bit of a shame it’s falling down for me in XP.
Anyone got any ideas?
Thanks,
Alan
_._________________
sample.wmv https://app.box.com/s/leu06ql1fu9uzt59wnoizedg85qoo7k4
Excel File with coding in it.xls https://app.box.com/s/7lc6jogqsxdbgv9qwhk22ig3fduzv8vu