Get full size properties (& perhaps a few other properties) using VBA of things in folders

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by DocAElstein »

Hi,
I seem to have partly ( or possibly fully ** ) answered my question while I was preparing it. But my answer is perhaps a bit messy, or wrong, or right. I don’t know. So I am looking for help / guidance, any comments / suggestions, etc.

What I am basically doing is comparing some files, (programmatically to do it efficiently as I may have a lot of files) .
The files should be the same or similar, but may have small differences in properties.
In particular the exact size, is useful for me to get at programmatically.

Manually I see a few different sizes given, for example for the uploaded video file example, sample.wmv , I can get these numbers from three different ways:
_Hovering over in the explorer window gives me 628 KB
_The explorer shown size property gives me 629 KB
_The right click on file in the explorer windows, left click properties way gives me a few numbers:
Size: 628 KB (643.170 Bytes)
Size
On Disk : 632 KB (647.168 Bytes)

( that Size On Disk number seems to vary a bit from computer to computer, - I guess that makes sense? )

https://i.postimg.cc/WbTKVfVC/Get-File- ... nually.jpg ,
https://bit.ly/3vw1DxN
Get File size manually.JPG
_.________________

This is my coding attempts so far

Code: Select all

 Sub GetFileSizeVBA()                                    '       https://eileenslounge.com/viewtopic.php?f=30&t=40533                https://www.excelfox.com/forum/showthread.php/2056-Appendix-Thread-2-(-Codes-for-other-Threads-HTML-Tables-etc-)?p=23916&viewfull=1#post23916   https://www.excelfox.com/forum/showthread.php/2936-YouTube-Video-making-and-editing-etc-coupled-to-excelfox-(-windows-Movie-Maker-)/page2#post23774
Rem 1 windows shell object way                                                              https://www.youtube.com/watch?v=jTmVtPHtiTg
' Early Binding windows shell object      '
'Dim objShell As Shell32.Shell: Set objShell = New Shell32.Shell  '                         https://i.postimg.cc/Fz9zrnNm/Tools-Referrences-Microsoft-Shell-Controls-And-Automation.jpg     https://i.postimg.cc/sDC9S54h/Tools-Referrences-Microsoft-Shell-Controls-And-Automation.jpg
' A bit Later Binding windows shell object               '
Dim objShell As Object: Set objShell = CreateObject("Shell.Application")

Dim objFolder As Shell32.Folder: Set objFolder = objShell.Namespace(ThisWorkbook.Path)  ' Put  sample.wmv  in same folder as the file from which this macro is running
Dim Fil As Shell32.FolderItem
    For Each Fil In objFolder.Items
        If Fil.Name = "sample.wmv" Then
         Debug.Print objFolder.GetDetailsOf(Fil, 1)  '   From this code line 628 KB                              Hover Over gives 628 KB       Right click properties gives Size: 628 KB (643.170 Bytes)  Size on Disk 632 KB (647.168 Bytes)   Size in explorer window 629 KB             https://i.postimg.cc/WbTKVfVC/Get-File-size-manually.jpg https://i.postimg.cc/Z5DDrbNz/Hover-Over-gotten-File-Size.jpg https://i.postimg.cc/XvRHzsx2/Right-click-properties-gotten-File-Size.jpg https://i.postimg.cc/sg2NQQvJ/Size-shown-in-explorer.jpg https://i.postimg.cc/PqpVygbp/Hover-Over-gotten-File-Size.jpg https://i.postimg.cc/vHhKZLHB/Size-shown-in-explorer.jpg
        Else
        End If
    Next Fil

Rem 2  Scripting Runtime Object Library  way      '                                         https://www.youtube.com/watch?v=zHJPliWS9FQ
' Early Binding
'Dim FSO As Scripting.FileSystemObject: Set FSO = New Scripting.FileSystemObject          '                        https://i.postimg.cc/d1GHPGxJ/Microsoft-Scripting-Runtime-Library.jpg
' Late Binding
Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject")

Dim Fl As Object: Set Fl = FSO.GetFile(ThisWorkbook.Path & "\" & "sample.wmv")          ' Put  sample.wmv  in same folder as the file from which this macro is running
 Debug.Print Fl.Size     '    gives 643170
End Sub
That seems to get me two of those numbers:
628 KB
643170



So my question is, does anyone know of any other ways to get all those numbers,
Perhaps there is a Power Shell way to do it ? , but is so I would want to know how to do that from within VBA


**Or, thinking a bit laterally, is the answer for what I am finally wanting to do, compare files from their size, something like, …. Use the FSO way (Rem 2) in my coding ) , since for comparing file sizes, that 643170 number would be the best/ most accurate and consistent / most appropriate for file comparing purposes. ?




Thanks,
Alan



_._________________________________

sample.wmv https://app.box.com/s/leu06ql1fu9uzt59wnoizedg85qoo7k4
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 20 Jan 2024, 13:17, edited 3 times in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get full file size properties using VBA

Post by HansV »

VBA has a built-in function that returns the same number as the FSO method: FileLen:

Debug.Print FileLen(ThisWorkbook.Path & "\" & "sample.wmv")
Best wishes,
Hans

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Get full file size properties using VBA

Post by DocAElstein »

Ah thanks, I may have passed that along the way, then I lost it. I expect there may be some other built in functions. I may have missed / forgot that approach a I started looking at over a hundred properties then went along narrowing it down a bit. I must recheck the built in functions approach, the size, dates, and version properties seem to me so far to be the ones most useful for comparing files.
(That line you gave gives me 643170 , which maybe suggests that is the important size result to look at.)
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

snb
4StarLounger
Posts: 596
Joined: 14 Nov 2012, 16:06

Re: Get full file size properties using VBA

Post by snb »

Code: Select all

Sub M_snb()
   MsgBox FileLen("G:\OF\sudoku_NRC_snb_004.xlsb")
   MsgBox CreateObject("wscript.shell").exec("cmd /c dir G:\OF\sudoku_NRC_snb_004.xlsb").stdout.readall
   MsgBox GetObject("G:\OF\sudoku_NRC_snb_004.xlsb").Application.Dialogs(474).Show
End Sub

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Get full file size properties using VBA

Post by DocAElstein »

Thanks, a couple of interesting alternatives to take a look at there, but I am getting an error with the third line

Code: Select all

 Sub M_snb()  '  https://eileenslounge.com/viewtopic.php?p=313570#p313570
 On Error GoTo Bed
1   Debug.Print "1 Built in stuff way " & vbCr & vbLf & FileLen(ThisWorkbook.Path & "\" & "sample.wmv") & vbCr & vbLf & vbCr & vbLf
2   Debug.Print "2 PowerShell in VBA way " & vbCr & CreateObject("wscript.shell").exec("cmd /c dir " & ThisWorkbook.Path & "\" & "sample.wmv").StdOut.ReadAll & vbCr & vbLf & vbCr & vbLf
3   Debug.Print "3 Dialogue thing way" & vbCr & vbLf & vbCr & vbLf & GetObject(ThisWorkbook.Path & "\" & "sample.wmv").Application.Dialogs(474).Show & vbCr & vbLf & vbCr & vbLf
Exit Sub
Bed:
 Debug.Print "Error with line " & Erl & vbCr & vbLf & Err.Number & vbCr & vbLf & Err.Description & vbCr & vbLf & vbCr & vbLf
 Application.Help HelpFile:=Err.HelpFile, HelpContextID:=Err.HelpContext
End Sub 
The third snb line is giving me an error.JPG
:scratch:
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Get full file size properties using VBA

Post by DocAElstein »

This version, (using the Excel file running the macro), does not error, but the second line is not giving any size results

Code: Select all

 Sub M_snb3()  '  https://eileenslounge.com/viewtopic.php?p=313570#p313570
 On Error GoTo Bed
1   Debug.Print "1 Built in stuff way " & vbCr & vbLf & FileLen(ThisWorkbook.FullName) & vbCr & vbLf & vbCr & vbLf
2   Debug.Print "2 PowerShell in VBA way " & vbCr & CreateObject("wscript.shell").exec("cmd /c dir " & ThisWorkbook.FullName).StdOut.ReadAll & vbCr & vbLf & vbCr & vbLf
3   Debug.Print "3 Dialogue thing way" & vbCr & vbLf & vbCr & vbLf & GetObject(ThisWorkbook.FullName).Application.Dialogs(474).Show & vbCr & vbLf & vbCr & vbLf
Exit Sub
Bed:
 Debug.Print "Error with line " & Erl & vbCr & vbLf & Err.Number & vbCr & vbLf & Err.Description & vbCr & vbLf & vbCr & vbLf
 Application.Help HelpFile:=Err.HelpFile, HelpContextID:=Err.HelpContext
End Sub
The second snb way gives no size results.JPG
Possibly this is all just characteristic of anything to do with file properties. I frequently find that the properties given can be a bit erratic, sometimes working, sometimes not.

Perhaps to get a program to get close to always working it probably needs to try a few ways, and take all the working results, and ignoring any that don’t work
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

snb
4StarLounger
Posts: 596
Joined: 14 Nov 2012, 16:06

Re: Get full file size properties using VBA

Post by snb »

Code: Select all

   MsgBox GetObject("G:\OF\sudoku_NRC_snb_004.xlsb").Application.Dialogs(474).Show
Has gone numb here after a few runs.

Code: Select all

MsgBox CreateObject("wscript.shell").exec("cmd /c dir G:\OF\sudoku_NRC_snb_004.xlsb").stdout.readall
is running well.

Some more inspiration:

https://www.snb-vba.eu/VBA_Bestanden.html#L_2.17

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Get full size properties using VBA of things in folders

Post by DocAElstein »

Hi,
I slightly mislead with my title and first post. I really meant I was interested in things in folders, like files and folders, in particular the size property. Sorry about that, but no matter, I think the basic ideas of what I wants is the same:
Properties of things in folders was / is the main thing I am interested in. (I just edited the Thread title.)
_._______________________________________________________________________________________________________________________
_.... Onwards…..
snb wrote:
17 Jan 2024, 20:38
...Some more inspiration:
https://www.snb-vba.eu/VBA_Bestanden.html#L_2.17
Thanks.
Not so much inspiration there, though, this time.
I took a look at this

Like this

Code: Select all

 '     http://www.eileenslounge.com/viewtopic.php?p=313578#p313578     https://www.snb-vba.eu/VBA_Bestanden.html#L_2.17
Sub SumSnbs()
Dim vTemp As Variant
' c009 Built in VBA FileLen( ) way
vTemp = FileLen(ThisWorkbook.Path & "\" & "sample.wmv") ' gives 643170

' c109 Scripting Runtime Object Library way
vTemp = CreateObject("scripting.filesystemobject").GetFile(ThisWorkbook.Path & "\" & "sample.wmv").Size  '   gives 643170

' c309 Microsoft Shell Controls And Automation way
vTemp = CreateObject("shell.application").Namespace(ThisWorkbook.Path).Items.Item("sample.wmv").Size     '   gives 643170

' c509 c609 Windows Script Host Object Model
''c509
'vTemp = Mid(Split(CreateObject("wscript.shell").Exec("cmd /c Dir " & Chr(34) & ThisWorkbook.Path & "\" & "sample.wmv" & Chr(34) & " /b").StdOut.ReadAll, vbCrLf)(5), 14, 13)
''c609
'vTemp = Split(CreateObject("wscript.shell").Exec("cmd /c Dir " & Chr(34) & ThisWorkbook.Path & "\" & "sample.wmv" & Chr(34) & " /b").StdOut.ReadAll, vbCrLf)(6)

' c709 VBA Open For Input Close way
Open ThisWorkbook.Path & "\" & "sample.wmv" For Input As #1
 Let vTemp = LOF(1) '  gives   643170
Close
End Sub 
And also I had a much more detailed look at all the ideas there, here

_._________

Conclusions
' c009 Built in VBA FileLen( ) way , ' c109 Scripting Runtime Object Library way , ' c309 Microsoft Shell Controls And Automation way
Those three ways are those we have already considered in this Thread. The results are consistent with what we have seen so far.

' c709 VBA Open For Input Close way

This is a nice simple extra way that I had not considered. It appears to give a nice Byte result for a file size.


' c509 c609 Windows Script Host Object Model
This is a new way that looked initially interesting to me. It seems to be concerned with a Cmd thing, and so I think may have something to do with Shell Cmd / PowerShell things, which I take an interest in.
Unfortunately those two lines don’t appear to work, unless I am mistaken. I tried so far in Windows XP and Windows 10 with Excel 2010 and Excel 2013

The problem seems to be this bit:
.Exec(CmdString).StdOut.ReadAll
in these two snb’s code line suggestions :

Code: Select all

c509 = Mid(Split(CreateObject("wscript.shell").Exec("cmd /c Dir " & Chr(34) & "E:\OF\voorbeeld.xls" & Chr(34) & " /b").StdOut.ReadAll, vbCrLf)(5), 14, 13)
c609 = Split(CreateObject("wscript.shell").Exec("cmd /c Dir " & Chr(34) & "E:\OF\voorbeeld.xls" & Chr(34) & " /b").StdOut.ReadAll, vbCrLf)(6)
In order for those two lines to work, the bit .Exec(CmdString).StdOut.ReadAll is presumably expected to give some text lines from which you get property values.
But, so far, if I investigate that .Exec(CmdString).StdOut.ReadAll bit, then it simply returns the text of the file, like
voorbeeld.xls

( If, instead of E:\OF\voorbeeld.xls , I used the folder like E:\OF, then I would get a list of just all things in that folder, - this sort of thing:
voorbeeld.xls
Another.xls
sample.wmv
MySubFolder
Book1.xlsx

)

So unless I got something wrong, those two line suggestions using the Windows Script Host Object Model idea are not working.

_.______________________________________________

If anyone has time and interest to check this, here is a simple thing to try.

Pick some folder you have which has a few files and maybe a sub folder or two in it. Get the path to that folder and hard code it into this macro.

Code: Select all

 Sub c509c609WindowsScriptHostObjectModelCheck()   '    https://eileenslounge.com/viewtopic.php?f=30&t=40533&p=313578#p313578
 Dim CmdString As String, vTemp As Variant
 Let CmdString = "cmd /c Dir " & Chr(34) & "F:\MMPropertyTest" & Chr(34) & " /b" '   <------  CHANGE PATH TO SUIT YOUR FOLDER
 Let vTemp = CreateObject("wscript.shell").Exec(CmdString).StdOut.ReadAll
 Debug.Print vTemp
 
 Debug.Print: Debug.Print
 Let CmdString = "cmd /c Dir " & Chr(34) & "F:\MMPropertyTest\sample.wmv" & Chr(34) & " /b" '   <------  CHANGE PATH TO SUIT ANY FILE IN YOUR FOLDER
 Let vTemp = CreateObject("wscript.shell").Exec(CmdString).StdOut.ReadAll
 Debug.Print vTemp
End Sub 
Run the macro and see what you get in the immediate window. ( No rush, I got a few tons of fresh snow to sweep away just now, :) :snow: :) )

If I run that macro on this;
MM Property Test Folder.JPG
, then I get this in the immediate window

Code: Select all

 sample.wmv
Microsoft Scripting Runtime  Library referrence.JPG
snbsize.JPG
Windows Script Host Object Model  Library referrence.JPG
MySubFolder
Movie Maker Versions.xls



sample.wmv 


Alan
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by DocAElstein »

Hi,
Here is some conclusions to this Thread and some offerings that might be useful for someone passing with a similar requirement.
To recap, I was basically wanting to compare program files for the same basic software thing , (or "App" as the trendy imprecise word to use seems to be) , both to check out different versions and at the same time catch a few naughty things dropped in where they shouldn’t have been in some downloads.
I figured it would be useful if I could build up a library of the program file profiles over a time scale so as to have a good record of how the things should look, as well as at the same time reviewing/ comparing the actual working software. A bonus could then also be the possibility of swapping some program files around to optimise a version a bit.

I started using the Microsoft Shell Controls and Automation / windows shell object way (WSO).
( https://www.youtube.com/watch?v=jTmVtPHtiTg )
Potentially if that worked well it should do the job easily and with around 300 properties theoretically available from it, it should knock the socks off any other way.
Unfortunately a few problems cropped up:
_ some things on it have never worked,
_ some things have stopped working with the newer operating systems,
_ the order of the properties given ( in other words their identifying number name) has been changed,
_ progressively a few new properties have been added.
_ some things, size for example , if it works, is given less precise than it is in other ways
So the WSO way is a partially broken spaghetti mess. Never the less, the potential for getting a lot more information than any other way is there, especially if, as me, you still use sometimes use the older Microsoft operating systems for non internet connected work. Also I like the way it orders all the things in a folder as folder items, as that seems to help in getting similar ordered profiles making comparisons a little easier. This is also is a plus for the WSO way over other ways, since other ways treat files and folders separately.

So, the TLDR.
Whilst looking here principally at the automation of doing what I wanted to do, I also in parallel refined a bit better knowing what it was I was wanting to do, that is to say what properties to narrow it down to for the purposes of comparing.
I think it will get me a long way to see for files and folders these properties
Full Name , Precise size, Date Last Modified , Date Created , Version
The WSO way almost does all that, and I would like to stay mainly with the WSO way for a few reasons. So the only thing to do is get the size by other ways: The size for folders is broken with the WSO way, ( as it seems to be for almost all other ways ), and the size for files is imprecise with the WSO way. But the File Scripting Object (FSO) seems get Folder and File size reliably and gives a more precision Byte number.

So this simple example demos just the basic idea that I am going with, which is basically looping to get the properties with the WSO way, but then at each loop, the sizes is got with the FSO way. That particular short coding is just intended to demo what I mean.


As far as an actual final useful coding is concerned, currently I have a full recursion type coding which seems to work well, to give me a long row of files and folder details, and all sub folder details, etc., (with a simple single row offset at a subfolder so as to help give it that "explorer level folder subfolder" type view)
The full story to that and a simple full actual working demo can be found here, and thereabouts in nearby and referenced posts from that one.


Here a slightly simpler demo, just for here, but it’s a real example looking at one of the smaller Folders I have been wanting to investigate.
To do it:
Put the uploaded file, EileenMMDemo.xls, and this folder ,
Movie Maker https://app.box.com/s/cxvc735a85q6az2r3gtb7ii9w2p3jzpf
, in the same place. Then just open the file EileenMMDemo.xls. That file only has one worksheet so the workbook should show just one window of that. In that window make a selection towards the left. Now run the only available macro, Sub PassFolderForReocursing3() , in that workbook which is in the only code module. At the start of the macro it will use the ActiveCell of that window to try and get a single cell range object from the your window Selection . It usually manages that, and goes on to put some results out with that cell at top left. It should look something like this

_.____________

The whole point of all this is (was originally) to do the equivalent of this sort of manually looking at stuff, but better and quicker.
https://i.postimg.cc/7YJxCzCw/Explore-M ... olders.jpg
Explore MM Files and Folders.JPG
(As it came out it had another very useful enlightenment, which was that an awful lot of these supplied files, (via download installations), despite coming from apparently perfect reliable sources, even official Microsoft, actually had some naughty things in them , which was good to know about, :flee: )




Alan
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
SpeakEasy
4StarLounger
Posts: 593
Joined: 27 Jun 2021, 10:46

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by SpeakEasy »

> the order of the properties given ( in other words their identifying number name

You can actually reference the extended properties by name, rather than by number. This maintains consistency if Microsoft change the numbering

>WSO way is a partially broken spaghetti mess
Have to sat that I have used 'WSO way' extensively in the past, and not found it to be broken

>orders all the things in a folder as folder items
Pros and cons with this. Makes the 'WSO way' somewhat slower than other methods as it has to read in all the folder items first (to be able to sort them). Can be a performance problem with big folders

>the size for files is imprecise with the WSO way
Given your requirements, yes - but this is documented behaviour

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by DocAElstein »

Hi, thx for the reply - I ‘ve only been looking at this for a month so I don’t know how typical my results are, so it’s good to know of other people’s experience and opinions.

> You can actually reference the extended properties by property name, rather than by number. That could be useful to me.
Just one snag, I have mostly German Office right now, but I would like to try and make my coding work in a few languages, in particular English. Maybe you or someone could do me a favour sometime: run the only macro in the enclosed file, WSO_PropNames.xls
Download both files, open just WSO_PropNames.xls and run Sub SchellGetNullDetails() ( Tabelle8. SchellGetNullDetails
That should get a list in column AA of the English ( or whatever language you have) names for me. Then please save and return the Excel file. (In AB you will get the property values, if they are given or work. )

>WSO way is a partially broken spaghetti mess
Have to say that I have used 'WSO way' extensively in the past, and not found it to be broken

It was a bit of a general feeling on my behalf so far. With 300 + properties and not all of them expected to be given for all file types, it would need a few years of experience to give an accurate appraisal. My limited experience says about half the things are broken. It varies for different operating systems. It will depend what properties you have been or are interested in.
I don’t know what property values should be given for all file types. But so far, my results suggest that a few that should be given are not always, as the WSO appears partially broken.
For me, folder size was important. That’s broken in WSO.
I wanted resolution of .wmv video files. So far I only got that working in WSO in XP
By spaghetti mess, I suppose I meant
_ the change in order,
_as well as different things available that may or may not work in different operating systems
_ sometimes the file name includes the extension, sometime not ( easy fix is like name = Dir(path & name &"*" , ) , but that all adds complexity again )
, all making it difficult to get a clear idea of what and how you can get at stuff. Using the property name would be useful, if they were all in one language. If I could get hold of an extensive list of all property names in all languages, preferably in a form I can copy and paste, then I could write some coding to do a selection based on name. But then that would add more complexity, and maybe extra problems if some exotic characters cause hiccups somewhere….

All in all, the WSO is not a simple working way in my limited experience so far. It is definitely broken in places. I can say that. I am not qualified yet to say exactly how much as a percentage of what it should do is broken.

(In my uploaded excel file, you can see some results across the board, XP – windows 11, for that sample file sample.wmv in columns A - Z


Alan

P.S. My estimate so far was that for good program file comparison these properties were the important ones
Size ( accurate full Bytes )
Full Name
Date Last Modified
Date Created
Version

Has anyone any comments on that? Can anyone think of other properties that might be good to know? My purpose is to check files from different sources that should be either the same or with specific changes, and if I have a few all claiming to be the same legit thing, then I am thinking that if I see unexpected things , or files with the same name but untypical properties compared to all the others, then that could be an indication of something dodgy going on.




_.____________________________________

WSO_PropNames.xls https://app.box.com/s/ynlabyb11ekmj6m4we99wk9xd7gx0xn3
sample.wmv https://app.box.com/s/leu06ql1fu9uzt59wnoizedg85qoo7k4



Code: Select all

Option Explicit
'   https://eileenslounge.com/viewtopic.php?p=313843#p313843
Public Sub SchellGetNullDetails()    '    https://www.youtube.com/watch?v=jTmVtPHtiTg&t=612s
Dim Ws As Worksheet:  Set Ws = Me
Dim Parf As String: Let Parf = ThisWorkbook.Path
'Dim objWSO As Shell32.Shell                 ' Early Binding
' Set objShell = New Shell32.Shell             '    https://i.postimg.cc/Fz9zrnNm/Tools-Referrences-Microsoft-Shell-Controls-And-Automation.jpg     https://i.postimg.cc/sDC9S54h/Tools-Referrences-Microsoft-Shell-Controls-And-Automation.jpg
Dim objWSO As Object                        ' Late Binding
 Set objWSO = CreateObject("shell.application")
'Dim objWSOFolder As Shell32.Folder: Set objWSOFolder = objWSO.Namespace(Parf)
Dim objWSOFolder As Object: Set objWSOFolder = objWSO.Namespace((Parf & ""))               '  ((  ))  & ""   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
'Dim FilItm As Shell32.FolderItem
Dim FilItm As Object
 Set FilItm = objWSOFolder.Items.Item("sample.wmv")
 Let Ws.Range("AA1") = Application.OperatingSystem
Dim Rw As Long
    For Rw = 0 To 400
     Let Ws.Cells(Rw + 4, 27) = Rw & " " & objWSOFolder.GetDetailsOf("Anyfink", Rw) '   Anyfink will do, apparntly    :    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg.9xhyRrsUUOM9zYlZPKdOpm
     Let Ws.Cells(Rw + 4, 28) = objWSOFolder.GetDetailsOf(FilItm, Rw)
    Next Rw
End Sub
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 29 Jan 2024, 20:28, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by HansV »

Here you go. For some reason. Application.Operatingsystem returns Windows (32-bit) NT 10.00, but it's actually Windows 11 Home, 64-bit.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by DocAElstein »

Thanks Hans, that gives me a good list of English names for windows 11, which I expect is the same for windows 10, - so far it was for me, ( But different to all other Operating systems. - So far I have seen 5 different lists
XP
Vista
Win7
Win 8.1
Win 10 and win 11

_.__________________

Did you also download the sample file, sample.wmv ?
Sorry, I didn't make that too clear. If you put sample.wmv in the same folder as WSO_PropNames.xls , then the macro will try to get the values of the properties for that file.
(It’s a strange characteristic that if you don’t give a valid folder item,
FilItm
in
objWSOFolder.GetDetailsOf(FilItm, Rw)
, then it does not error, but instead you get the property name instead, which is what the Anyfink , in objWSOFolder.GetDetailsOf("Anyfink", Rw) is all about, ( https://www.youtube.com/watch?v=jTmVtPH ... zYlZPKdOpm )

_.____________________
HansV wrote:
29 Jan 2024, 20:27
Application.Operatingsystem returns Windows (32-bit) NT 10.00, but it's actually Windows 11 Home, 64-bit.
That’s useful to know as well. So far the string returned in any operating system from Application.OperatingSystem does not return the same text that I have seen in Microsoft documentation.
By trial and error, this had mostly worked for me, ( or so I thought**) for example, to get the Version property from different operating systems with the WSO way, ( since it has a different number in different operating systems )

Code: Select all

         ' Property   Version
         Dim PVItm As Long
            If InStr(1, Application.OperatingSystem, "5.01", vbBinaryCompare) <> 0 Then  ' XP
             Let PVItm = 37   ' XP
            ElseIf InStr(1, Application.OperatingSystem, "6.01", vbBinaryCompare) Or InStr(1, Application.OperatingSystem, "6.00", vbBinaryCompare) <> 0 Then
             Let PVItm = 156  ' Win 7  or  Vista
            ElseIf InStr(1, Application.OperatingSystem, "6.02", vbBinaryCompare) <> 0 Then
             Let PVItm = 158  ' Win 8.1
            Else '  Windows 10 and 11 probsbly
             Let PVItm = 166
            End If
          'Let MeActiveCell.Offset(Rw + 4, Clm) = objWSOFolder.GetDetailsOf(666, PVItm)
          Let MeActiveCell.Offset(Rw + 4, Clm) = objWSOFolder.GetDetailsOf(FldItm, PVItm)
** But, after what you said , I went back and just re-checked my only windows 11 machine and its giving me ….
Windows (32-Bit) NT 6.02
( which should be for windows 8.1 I think - it is what my windows 8.1 machine gives me )
(By the way, my windows 11 machiine is an upgraded old Vista machine. I don't know if it had windows 8.1 along the way?, or if that would be any relavance at all? )
So it looks like Application.OperatingSystem is a bit iffy in windows 11
So I am glad you mentioned that. I will need to think again, since it appears that although that coding would work coincidentally for you, it would not work for me on my windows 11. Hmm…. I suppose that is another reason to use the property name to get the value.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by HansV »

Here is the workbook with results for the .wmv.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by DocAElstein »

Thanks Hans, the values specific to the file are identical to what I get on windows 10 and windows 11, ( and your results also are missing some property values, as do my windows 10 and windows 11 results, results which I can get on lower operating systems for that sample file )
Alan

_._____________
(Edit PS: If anyone else passing has any operating system (new or old) other than English windows 10 and English windows 11 or any German Operating System, and has time to download both files and run the macro for me, then return the Excel file, then I will modify the coding I am sharing with everyone to work also in that language , see also here
thanks,. Alan )
Last edited by DocAElstein on 30 Jan 2024, 17:03, edited 1 time in total.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15714
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by ChrisGreaves »

DocAElstein wrote:
17 Jan 2024, 13:49
What I am basically doing is comparing some files, (programmatically to do it efficiently as I may have a lot of files) .
The files should be the same or similar, but may have small differences in properties.
In particular the exact size, is useful for me to get at programmatically.
Alan, I suspect that a great deal of confusion arises from multiple approaches. I appreciate the problem because I face similar decisions in eliminating "duplicates" from my 19,800 MP3 music tracks in 2,066 folders. (In the end, I can define a duplicate as a rendition of a song by a different artist, or even the same artist at a different age :flee: )

Taking "file sizes" by itself: Try using every utility/method you can lay your hands on and decide (using a sample of, say, a dozen files) which type of figure best suits your purpose. It may be a crude 620KB method or it my be an explicit 643,170 Bytes method. (Leaving aside any file padding to 64-byte boundaries ...).
Once you have specified the accuracy desired, try to implement that measurement in VBA.

That done stick to that way of measurement no matter what.

I think that consistency is the key word here.
You want a means of measurement that is consistent right across your data set.

Cheers, Chris
By definition, educating the client is the consultant’s first objective

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by DocAElstein »

Hi Chris,
Are you suggesting that the accurate Byte size is a good indication of if a file is what it says it is, ( assuming you already have tried and trusted legit files and you know their Byte size accurately.)

If you are not suggesting that, what is your opinion of that? Is a Byte size a good indication of if a file is legit?, ( assuming you know with reasonable certainty the Byte size of the legit file). Or can a bad person make a naughty file with evil things in it, give it the name of a known popular good file, then fiddle it to show exactly the same Byte size as a legit file?

Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
StuartR
Administrator
Posts: 12650
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by StuartR »

File size is NOT a good indication. You need a digital fingerprint to be sure
StuartR


User avatar
SpeakEasy
4StarLounger
Posts: 593
Joined: 27 Jun 2021, 10:46

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by SpeakEasy »

>can a bad person make a naughty file with evil things in it, give it the name of a known popular good file, then fiddle it to show exactly the same Byte size as a legit file?

They can indeed! And they do.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15714
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Get full size properties (& perhaps a few other properties) using VBA of things in folders

Post by ChrisGreaves »

DocAElstein wrote:
30 Jan 2024, 12:39
Are you suggesting that the accurate Byte size is a good indication of if a file is what it says it is, ( assuming you already have tried and trusted legit files and you know their Byte size accurately.)
Alan, I am stepping back (up?) from that and suggesting that without a specified goal (and you may well have described a goal very well, but we don't know about it, I grant you), it will be difficult to choose a specific method of measurement.
But more than that, I suggest too that what really matters is that the type of measurement be consistent.

I was told early on to "adopt a standard and stick to it".

Of course in my younger days I would adopt the wrong standard and have to go back and re-do work. But "consistency" was drummed into me.

I will add that I am not judging the suggestions to date, but that there seem to be many suggestions of how to define "size of file", and without a well-defined goal, how can you choose which method best supports your goal? In particular "assuming you already have tried and trusted legit files and you know their Byte size accurately" suggests that this question can be answered only by your statement of Goal.

(signed) "Theorist" of Bonavista.
Last edited by ChrisGreaves on 30 Jan 2024, 15:56, edited 1 time in total.
By definition, educating the client is the consultant’s first objective