[VBA]FileSystemObject to collect video file length-CodePudding

Good morning people,

I have a need, to know the length (time in HH:MM:SS format) of several video files in several folders. Later I will consume this data in excel or power BI. However, I found a resource in VBA called FileSystemObject. I noticed in the library that I can get the modification date of the file. But actually I would like to collect the length in the video (which you can access via the file properties field). If it is not possible in VBA, I accept references in other languages or tools for study. Link with explanations of feature https://trumpexcel.com/vba-filesystemobject/

Thank you very much

CodePudding user response：

https://officetricks.com/excel-vba-get-file-property-author-modified-date-time/

I only updated so I could call as a function:

'Here is the complete list of properties that can be retrieved:

'0 Name 100 Cell phone 200 Bcc addresses '1 Size
101 Nickname 201 Bcc '2 Item type 102 Office location
202 Cc addresses '3 Date modified 103 Other address 203 Cc '4 Date created 104 Other city 204 Conversation ID '5 Date accessed 105 Other country/region 205 Date received '6 Attributes 106 Other P.O. box 206 Date sent '7
Offline status 107 Other postal code 207 From addresses '8 Offline availability 108 Other state or province 208 From '9 Perceived type 109 Other street 209 Has attachments '10 Owner 110 Pager 210 Sender address '11 Kind
111 Personal title 211 Sender name '12 Date taken 112 City 212 Store '13 Contributing artists 113 Country/region 213 To addresses '14 Album 114 P.O. box 214 To do title '15 Year 115 Postal code 215 To '16 Genre 116 State or province 216 Mileage '17 Conductors 117 Street 217 Album artist '18 Tags 118 Primary e-mail 218 Album ID '19 Rating 119 Primary phone 219 Beats-per-minute '20 Authors 120 Profession 220 Composers '21 Title 121 Spouse/Partner 221 Initial key '22 Subject 122 Suffix 222 Part of a compilation '23 Categories 123 TTY/TTD phone 223 Mood '24 Comments 124 Telex 224 Part of set '25 Copyright
125 Webpage 225 Period '26 # 126 Content status 226 Color '27 Length 127 Content type 227 Parental rating '28 Bit rate 128 Date acquired 228 Parental rating reason '29 Protected 129 Date archived 229 Space used '30 Camera model 130 Date completed 230 EXIF version '31 Dimensions 131 Device category 231 Event '32 Camera maker 132 Connected 232 Exposure bias '33 Company 133 Discovery method 233 Exposure program '34 File description 134 Friendly name 234 Exposure time '35 Program name 135 Local computer 235 F-stop '36 Duration 136 Manufacturer
236 Flash mode '37 Is online 137 Model 237 Focal length '38 Is recurring 138 Paired 238 35mm focal length '39 Location 139 Classification 239 ISO speed '40 Optional attendee addresses 140 Status 240 Lens maker '41 Optional attendees 141 Client ID 241 Lens model '42 Organizer address 142 Contributors 242 Light source '43 Organizer name 143 Content created 243 Max aperture '44 Reminder time 144 Last printed 244 Metering mode '45 Required attendee addresses 145 Date last saved 245 Orientation '46 Required attendees 146 Division 246 People '47 Resources 147 Document ID 247 Program mode '48 Meeting status 148 Pages
248 Saturation '49 Free/busy status 149 Slides 249 Subject distance '50 Total size 150 Total editing time 250 White balance '51 Account name 151 Word count 251 Priority '52 Task status 152 Due date 252 Project '53 Computer 153 End date 253 Channel number '54 Anniversary 154 File count 254 Episode name '55 Assistant’s name 155 Filename 255 Closed captioning '56 Assistant’s phone 156 File version 256 Rerun '57 Birthday 157 Flag color 257 SAP '58 Business address 158 Flag status
258 Broadcast date '59 Business city 159 Space free 259 Program description '60 Business country/region 160 Bit depth
260 Recording time '61 Business P.O. box 161 Horizontal resolution 261 Station call sign '62 Business postal code
162 Width 262 Station name '63 Business state or province
163 Vertical resolution 263 Summary '64 Business street 164 Height 264 Snippets '65 Business fax 165 Importance
265 Auto summary '66 Business home page 166 Is attachment
266 Search ranking '67 Business phone 167 Is deleted 267 Sensitivity '68 Callback number 168 Encryption status 268 Shared with '69 Car phone 169 Has flag 269 Sharing status '70 Children 170 Is completed 270 Product name '71 Company main phone 171 Incomplete 271 Product version '72 Department 172 Read status 272 Support link '73 E-mail address 173 Shared 273 Source '74 E-mail2 174 Creators 274 Start date '75 E-mail3 175 Date 275 Billing information '76 E-mail list 176 Folder name 276 Complete '77 E-mail display name 177 Folder path 277 Task owner '78 File as 178 Folder 278 Total file size '79 First name 179 Participants 279 Legal trademarks '80 Full name 180 Path 280 Video compression '81 Gender 181 By location 281 Directors '82 Given name 182 Type 282 Data rate '83 Hobbies 183 Contact names 283 Frame height '84 Home address 184 Entry type 284 Frame rate '85 Home city 185 Language
285 Frame width '86 Home country/region 186 Date visited
286 Total bitrate '87 Home P.O. box 187 Description 291 Masters Keywords (debug) '88 Home postal code 188 Link status 292 Masters Keywords (debug) '89 Home state or province 189 Link target 293 Primary disk '90 Home street 190 URL 294 Memory '91 Home fax 191 Media created 295 Machine status '92 Home phone 192 Date released 296 Configuration file '93 IM addresses 193 Encoded by 297 Audio tracks '94 Initials 194 Producers 298 Bit depth '95 Job title
195 Publisher 299 Contains chapters '96 Label 196 Subtitle 300 Content compression '97 Last name 197 User web URL 301 Subtitles '98 Mailing address 198 Writers
302 Subtitle tracks '99 Middle name 199 Attachments 303 Video tracks

Sub TestGet_Extended_File_Info()
  Debug.Print Get_Extended_File_Info("C:\Downloads\", "Test.mpg", 27)

End Sub

Function Get_Extended_File_Info(strPath As Variant, strFile As String, Optional PropNum As Integer = 3) As String
    Dim sFile, oDir As Object, obja
    Dim i As Integer
    
    'Create Shell Object & NameSpace
    Set oDir = CreateObject("Shell.Application").Namespace(strPath)
    Set sFile = oDir.ParseName(strFile)
    
            obja = oDir.GetDetailsOf(sFile, PropNum)
            If obja <> "" Then
                 obja = Replace(obja, ChrW(8206), "")
                 obja = Replace(obja, ChrW(8207), "")


                Get_Extended_File_Info = obja
   Set oDir = Nothing

                Exit Function
            
            End If
   Set oDir = Nothing
 End Function

CodePudding user response：

Thanks a lot for the replies.

@Keith Swerling The written function meets my need.

Have a great week and good luck.