If you torture the data long enough, it will confess.Ronald H. Coase
What it’s All About
The be clear and straight-forward from the beginning, I do not know VBA. My coding experience outside of HTML and SQL is entirely Python, R, and C/C++ (and C/C++ was long enough ago as to be another lifetime). But when I needed to make a list of all of my Bureau’s data files while still working without software beyond the basic build (I have browsers and MS Office), and without access to any useful utilities or the ability to install anything, I turned to VBA as a possible solution.
The most important nuance of my task is that I needed the authors attribute from the extended Windows file information (this is different from the normal author/owner file attribute that, in my case, showed a generic account for the Bureau that provisions the servers, and not the creator of the file like I needed. I found lots of examples online of code that made a list of all of the files in a folder. A few of them accessed the attributes I needed. I found a couple working examples of VBA that listed all of the files in a folder and all of its sub-folders. What I wasn’t able to find was an example of code that lists out all of the files in a folder and its subdirectories AND accessed the attributes I wanted.
I know enough about coding, in general, and I have access to Google, so I was able to put together something that mostly works, using bits from all of the various examples I found. I apologize to all those on Stack Exchange and elsewhere that contributed code that I absorbed into my mess: it didn’t occur to me to track authors for attribution until I had already long lost track of what pieces came from where.
Like I said, the whole thing is a mess, and breaks in strange places. And there is a weird “feature” that makes the list start back at the beginning of the spreadsheet once it reaches 65k records. But in case someone out there is struggling to accomplish the same thing that I was, here is the code I came up with:
Sub file_list() Call ListFilesInFolder("Z:\Test_Folder", True) End Sub Sub ListFilesInFolder(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean) Dim FSO As Object Dim SourceFolder As Object Dim SubFolder As Object Dim ObjectFolder As Object Dim ObjectShell As Object Dim FileItem As Object Dim ObjectFolderItem As Object Dim i As Long Dim ws As Worksheet Set FSO = CreateObject("Scripting.FileSystemObject") Set ObjectShell = CreateObject("Shell.Application") Set SourceFolder = FSO.GetFolder(SourceFolderName) r = Range("A65536").End(xlUp).Row + 1 Range("A1:G1").Value = Array("File Name", "Extension", "Path", "Date Created", "Date Modified", "Author", "Size") For Each FileItem In SourceFolder.Files Set ObjectFolder = ObjectShell.Namespace(SourceFolder.Path) Set ObjectFolderItem = ObjectFolder.ParseName(FileItem.Name) Cells(r, 1).Value = ObjectFolder.GetDetailsOf(ObjectFolderItem, 0) 'File Name Cells(r, 2).Value = FSO.GetExtensionName(ObjectFolderItem) 'Extension Cells(r, 3).Value = SourceFolder.Path 'Path Cells(r, 4).Value = ObjectFolder.GetDetailsOf(ObjectFolderItem, 4) 'Date Created Cells(r, 5).Value = ObjectFolder.GetDetailsOf(ObjectFolderItem, 3) 'Date Modified Cells(r, 6).Value = ObjectFolder.GetDetailsOf(ObjectFolderItem, 20) 'Author Cells(r, 7).Value = ObjectFolder.GetDetailsOf(ObjectFolderItem, 1) 'Size r = r + 1 X = SourceFolder.Path Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.SubFolders ListFilesInFolder SubFolder.Path, True Next SubFolder End If Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing End Sub
By definition all scientists are data scientists. In my opinion, they are half hacker, half analyst, they use data to build products and find insights.Monica Rogati, VP for Data, Jawbone
Big Data is like teenage sex: everyone talks about it, nobody really knows how to do it, everyone thinks everyone else is doing it, so everyone claims they are doing it.Dan Ariely, Duke University
Given my new job, I spend a lot of time thinking about equity (the people kind, not the investment kind), how we define it (in a formal, first-order logic sort of way), and how we measure it in the real world. As an autistic, gender-queer person, I spend more time thinking about visibility and representation than I ever thought I would. Those two things came together today while I was tagging along on a photoshoot (one that wasn’t mine, for a change) and I saw the sticker pictured above in the featured post image.
The sticker, and its quote, stuck with me (pun wasn’t intended, although it’s rather good and I’m inclined to take credit anyway) – and for good reason. Both the quote and the posterized image are from the first interview with Chelsea Manning, the US Army Intelligence Analyst who was convicted in 2013 under the Espionage Act after leaking classified documents to WikiLeaks*, after her release from prison under a presidential
pardon commutation of sentence in 2017.
I couldn’t find a transcript of the interview, which was hosted by the Institute of Contemporary Arts at the Royal Institution in London, and which was held as a conversation with writer James Bridle, but FRIEZE has a good summary (including the photo from the sticker), and video of the entire conversation is on YouTube. The quote from the sticker is just one finely-cut gemstone set in the middle of a very strong paragraph, placed in the middle of an interview full of powerful ideas and insight:
‘There’s a misunderstanding: visibility is not the same thing as equality. As a trans woman, I know that there is a systemic problem that can only be addressed when we challenge the core assumptions in society.’Chelsea Manning, 1 October 2018
Ms Manning argues that with technology, visibility can make injustice and inequality even worse, saying that if you were already more likely to be arrested, algorithms make that 1 million times worse. There is so much packed into those words, and into the conversation around it. But the thing from the interview that I took away most (beyond the original quote from this afternoon that started me down this rabbit hole, and the part that is likely the most applicable to my current work, was a call on algorithm developers to emulate the scientists who refused to work on the Manhattan Project and practice an ethical form of refusal.
An interesting note: I didn’t know who the quote was by, or who the woman on the sticker was when I saw it, but the photoshoot I was tagging along on was for a trans friend of ours and his boyfriend. Seems fitting.
*I am not at all interested in debating the morality of what Ms Manning did (which I, frankly, agree with), or the legality of it (which a vindictive and extremely embarrassed military court found to be illegal) – neither of which are material to this post.