![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/q-for-powertip.jpg\" "\\"Hey,")
\n \n
Hey, Scripting Guy! I Using VBScript, how can I identify the 20 most-recently modified files in a folder? Hey, TW. You know, the Scripting Guy who writes this column was just reading \u2013 thanks to the magic of Spam email \u2013 about a new voice mail service. With this service you don\u2019t have to listen to all those annoying voice mails; instead, whenever someone leaves you a voice mail a piece of software \u201clistens\u201d to that voice mail and then sends you an email or text message summarizing the voice mail. This is considered \u201crevolutionary,\u201d although, to be honest, the Scripting Guy who writes this column isn\u2019t exactly sure why it\u2019s \u201crevolutionary\u201d to receive a text message rather than a voice mail, but he\u2019ll take everyone\u2019s word for it. <\/P>\n
— TW<\/P>![\"Spacer\"](\"https:\/\/devblogs.microsoft.com\/scripting\/wp-content\/uploads\/sites\/29\/2019\/05\/spacer.gif\")
![\"Hey,](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/a-for-powertip.jpg\" "\\"Hey,")
![\"Script](\"http:\/\/img.microsoft.com\/library\/media\/1033\/technet\/images\/scriptcenter\/ad.jpg\" "\\"Script")
<\/A> \n
| \n Note<\/B>. However, the Scripting Guy who writes this column was<\/I> intrigued by a review of the service. The reviewer actually liked the service quite a bit, but he did mention the fact that the voice-to-text translation occasionally left something to be desired. \u201c\u2026when a friend left a message that said, \u2018My mom and I want to take you out to lunch and I wanted to see what you guys were in the mood for,\u2019” he noted, \u201cthe transcript came out as \u2018I’m on and I want you got goes up to lunch and I wanna see what you guys removed for.\u2019”<\/P>\n And yes, as a matter of fact, we do<\/I> use that very same piece of software when we write the Hey, Scripting Guy!<\/I> column. How did you know?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n At any rate, for awhile now we\u2019ve had software that can read your email, call you on the phone, and then read that email back to you. Now we have software that can take a voice mail and turn it into email. Which leads to an obvious question. Suppose the first piece of software reads an email, calls someone\u2019s cell phone, and leaves that email as a voice mail. Now, what do you think will happen if the second piece of software listens to the voice mail, converts it to email, and then emails that same message back to the first piece of software (which, of course, will then call you and try to read the email back to you)? Would life as we know it come to a sudden end?<\/P>\n Well, let\u2019s hope so; after all, the past month hasn\u2019t exactly been seashells and balloons for the Scripting Guys.<\/P>\n Of course, if cell phones can send email and email can make calls to cell phones you also have to wonder why these devices even need<\/I> human beings. Does this mean that the human race is now doomed to extinction? Well, let\u2019s hope so; like we said, the past month hasn\u2019t exactly been seashells and balloons for the Scripting Guys.<\/P>\n Besides, fewer people means fewer Christmas cards that the Scripting Guy who writes this column will have to send out this year.<\/P>\n As far as we know, however, the human race is not yet extinct. That means two things: 1) the Scripting Guy who writes this column needs to hang onto his Christmas card list a little longer; and, 2) he also needs to come up with a script that can identify the 20 most-recently modified files in a folder. Fortunately, a script like that is the very thing that the Scripting Guy who writes this column sent out as a Christmas card this past year. Happy holidays, everyone:<\/P> strComputer = “.”<\/p>\n Set objWMIService = GetObject(“winmgmts:\\\\” & strComputer & “\\root\\cimv2”)<\/p>\n Set colFiles = objWMIService.ExecQuery _\n (“ASSOCIATORS OF {Win32_Directory.Name=’C:\\Scripts’} Where ” _\n & “ResultClass = CIM_DataFile”)<\/p>\n Set DataList = CreateObject(“ADOR.Recordset”)\nDataList.Fields.Append “FileName”, adVarChar, MaxCharacters, adFldIsNullable\nDataList.Fields.Append “ModifiedDate”, adVarChar, MaxCharacters, adFldIsNullable\nDataList.Open<\/p>\n For Each objFile In colFiles\n DataList.AddNew\n DataList(“FileName”) = objFile.Name\n DataList(“ModifiedDate”) = objFile.LastModified\n DataList.Update\nNext<\/p>\n DataList.Sort = “ModifiedDate DESC”<\/p>\n For i = 1 to 20\n dtmWMIDate = DataList.Fields.Item(“ModifiedDate”)\n dtmConvertedDate = CDate(Mid(dtmWMIDate, 5, 2) & “\/” & Mid(dtmWMIDate, 7, 2) & _\n “\/” & Left(dtmWMIDate, 4) & ” ” & Mid (dtmWMIDate, 9, 2) & “:” & _\n Mid(dtmWMIDate, 11, 2) & “:” & Mid(dtmWMIDate, 13, 2))\n Wscript.Echo DataList.Fields.Item(“FileName”) & ” — ” & dtmConvertedDate\n DataList.MoveNext\nNext\n<\/PRE>\n As you can see, our script starts off by defining three constants, constants that will be required to create a disconnected recordset. <\/P>\n Note<\/B>. What\u2019s that? You didn\u2019t know we were going to use a disconnected recordset to tackle this problem? Sorry. As it turns out, that text message you got saying \u201cWhere groin Eustis this kinda wracks a sax\u201d was actually a voice mail from us saying \u201cWe\u2019re going to use a disconnected recordset.\u201d Obviously there are still a few kinks to be worked out here.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n Anyway, the constant adVarChar will be used to create a pair of fields with a Variant data type; the constant MaxCharacters will set the maximum number of characters that can be stored in that field to 255. As for adFldIsNullable, well, that tells the script that it\u2019s OK to have a record with null values in it. We won\u2019t actually have<\/I> any records with null values (unless we somehow encounter a file that has neither a file path nor a last modification date), but because adFldIsNullable is a useful thing to know about we decided to toss it in, just for the heck of it.<\/P>\n After defining our constants we next connect to the WMI service on the local computer. And yes, as a matter of fact we can<\/I> run this script against a remote computer; to do that, just assign the name of the remote computer to the variable strComputer. You know, like this:<\/P> Once we\u2019re connected to the WMI service we can use the following query to retrieve a collection of all the files (instances of the CIM_DataFile<\/B> class) found in the folder C:\\Scripts:<\/P> Of course, that leads to another obvious question: now that we have this collection of files what are we supposed to do with it? Well, for the moment at least, we\u2019re not going to do anything<\/I> with it; instead, we\u2019re going to use this block of code to create a disconnected recordset (a database table that exists only in memory, and isn\u2019t tied to an actual database file):<\/P> As you can see, there really isn\u2019t much to this block of code. In the first line we create an instance of the ADOR.Recordset<\/B> object, the object used to create a disconnected recordset. In line 2, we create a variant field (with a maximum of 255 characters) named FileName; in line 3, we create a similar field named ModifiedDate. Finally, in line 4, we call the Open<\/B> method to open the disconnected recordset and ready it for data entry.<\/P>\n Whew. And to think we said that there wasn\u2019t much to that block of code!<\/P>\n Now, back to our collection of files. (Remember, the collection of all the files found in the folder C:\\Scripts? The collection we just retrieved using the Associators Of query? Right, right: that<\/I> collection of files.)<\/P>\n What we\u2019re going to do now is loop through this collection, adding the path and last modified date for each file to our disconnected recordset. Why<\/I> are we adding this information to a disconnected recordset? Well, we need to determine the 20 most-recently modified files; the only way we know of to do that is to grab all<\/I> the files, sort them by the LastModified<\/B> property, and then report back the first 20 items in the list. As far as the Scripting Guys are concerned, the easiest way to store all these files and then sort them by modification date is to stash all the information in a disconnected recordset.<\/P>\n Which is just exactly what we do inside our For Each loop. To begin with, we call the AddNew<\/B> method to create a new, blank record for our disconnected recordset; we then use these two lines of code to add the value of the file\u2019s Name<\/B> and LastModified properties to our new record:<\/P> As soon as we\u2019ve assigned values to the two fields (FileName and ModifiedDate) we call the Update<\/B> method and add the record to the disconnected recordset:<\/P> And then it\u2019s back to the top of the loop, where we repeat the process with the next file in the collection.<\/P>\n Before we go any further let\u2019s talk about what these records actually look like. Better yet, and in keeping with the spirit of the times, let\u2019s show<\/I> you what these records look like:<\/P>\n FileName<\/B><\/P><\/TD>\n ModifiedDate<\/B><\/P><\/TD><\/TR>\n c:\\scripts\\add_access_rule.ps1<\/P><\/TD>\n 20080418125447.687500-420<\/P><\/TD><\/TR>\n c:\\scripts\\enumerator.txt<\/P><\/TD>\n 20080429131114.380340-420<\/P><\/TD><\/TR>\n c:\\scripts\\namechange.bat<\/P><\/TD>\n 20080423092144.906250-420<\/P><\/TD><\/TR>\n c:\\scripts\\test.doc<\/P><\/TD>\n 20080422095053.703000-420<\/P><\/TD><\/TR>\n c:\\scripts\\z.ps1<\/P><\/TD>\n 20080417150002.093750-420<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n The FileName field doesn\u2019t require much explanation; it\u2019s simply the path to the file in question. As for the ModifiedDate, well, strange as it might seem, that is<\/I> the date that the file was last modified. WMI, as many of you know by now, doesn\u2019t use a standard date-time format; instead, it uses the UTC (Universal Time Coordinate) format. With the UTC format, date-time values are broken down like this:<\/P>\n Year<\/B><\/P><\/TD>\n Month<\/B><\/P><\/TD>\n Day<\/B><\/P><\/TD>\n Hour<\/B><\/P><\/TD>\n Minute<\/B><\/P><\/TD>\n Seconds<\/B><\/P><\/TD>\n Milliseconds<\/B><\/P><\/TD>\n Bias<\/B><\/P><\/TD><\/TR>\n 2008<\/P><\/TD>\n 04<\/P><\/TD>\n 18<\/P><\/TD>\n 12<\/P><\/TD>\n 54<\/P><\/TD>\n 47<\/P><\/TD>\n .687500<\/P><\/TD>\n -420<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n Admittedly, that\u2019s an odd way to format dates and times. On the bright side, however, it turns out to be a good way to sort<\/I> dates and times. Consequently, we can enter these UTC values into our disconnected recordset as-is, then sort by ModifiedDate to determine the 20 files that were most-recently modified.<\/P>\n In fact, that\u2019s what we do the moment we exit our For Each loop:<\/P> All we\u2019re doing here is calling the Sort<\/B> method followed by the field we want to sort on (ModifiedDate) and the DESC parameter, which tells the script to sort the recordset in descending order; that\u2019s how we make sure that the most-recently modified file winds up at the top of the list.<\/P>\n After the files are sorted we can report back the 20 most-recently modified files by setting up a For Next loop that runs from 1 to 20:<\/P> |