{"id":112,"date":"2008-08-13T01:54:43","date_gmt":"2008-08-13T00:54:43","guid":{"rendered":"http:\/\/simoncpage.co.uk\/blog\/?p=112"},"modified":"2009-01-04T04:37:28","modified_gmt":"2009-01-04T03:37:28","slug":"excel-vb-xla-addin-content-folders","status":"publish","type":"post","link":"https:\/\/simoncpage.co.uk\/blog\/2008\/08\/excel-vb-xla-addin-content-folders\/","title":{"rendered":"Excel VB | XLA addin to display content of a folder"},"content":{"rendered":"

Recently I wanted to list in an email the contents of a windows folder containing 250+\u00a0files and folders\u00a0so that I could comment on some of them rather than copying and pasting. Excel to me seemed like the obvious application to use. I could attach\u00a0an .xls file to the email with a nice structured\u00a0comment list.\u00a0I found a few solutions but in the end decided to write my\u00a0own Excel .xla file to do exactly what I needed.<\/p>\n

1. SysExporter<\/h3>\n

Description: <\/strong>SysExporter<\/a> is a small tool that allows you to extract information from any windows application or window and export it as text, HTML, or even XML files. It is designed to retrieve information from programs that do not normally allow you to simply copy\/paste information, such as the files list inside a folder or zip archive, list of emails and\/or contacts in an email client, registry values in the right pane of the registry editor, error message prompts, etc.<\/p>\n

This works perfectly\u00a0for grabbing text from Windows\u00a0but this won’t work if you want to list a folder which has sub directories (as it just displays what you see). It also fails to work (in Vista anyway) for any folders that are displaying a search result. Otherwise its a great application for grabbing text from anything you see on your computer which you can’t just easily\u00a0copy and paste from – and best of all its freeware.<\/p>\n

2. Filemaker<\/h3>\n

A plugin that I have mentioned before\u00a0from Troi<\/a> (Troi File Plug-in) has a function\u00a0that will allow you to list the contents of a folder path in\u00a0to a\u00a0fields with paragraph returns after each file or folder (files are left with their extension to distinguish).<\/p>\n

TrFile_ListFolder( switches ; FileSpec )<\/p>\n

“switches” offer options like files, folders, hidden files, shortcuts etc.<\/p><\/blockquote>\n

The only problems are:<\/p>\n

    \n
  1. I would need to add in some sort of recursive loop for it to go through the other folders that it finds as it only does 1 level.<\/li>\n
  2. I want to grab details like file\/folder size and modified dates which I would need to factor\u00a0in to the solution.<\/li>\n
  3. I want to have it in a database grid like format (a record per file or folder) and\u00a0not listed all in one field – again more work involved in achieving this.<\/li>\n<\/ol>\n

    I felt this wasn’t the most ideal route and could end up being a little messy.<\/p>\n

    3. Excel Addin<\/h3>\n

    There are a couple of commercial addins out there that you can buy with this function in\u00a0albeit not all have subfolders. A lot of the addin also relied too heavily on dll file\u00a0and additional references need to be made in vb – I want something simple, portable and with little setup. The only one I found\u00a0which\u00a0ticked\u00a0most of\u00a0my boxes\u00a0was ASAP Utilities<\/a> (it also offers a few more options for adding hyperlinks, sort order and filtering by file type).<\/p>\n

    4. Do it yourself<\/h3>\n

    One problem I found with the ASAP addin (v4.2.5)\u00a0was that it wasn’t quick and\u00a0on top of that\u00a0not free.\u00a0So I decided to write my own – and 1hour later I had it cracked.<\/p>\n

    From a speed test on “My Documents” folder, with 10,000+ files and folders,\u00a0I managed to display the full list in about 14.8 secs where as the ASAP addin\u00a0did it in, a poor, 50.5 secs (and that with most of the options turned off like sort order and\u00a0hyperlinks).<\/p>\n

    Some\u00a0nice options I have added with mine on top of this is that it will display folders on a separate lines not included with the files,\u00a0greys out the folder\u00a0names on lines for files (so visually pleasing)\u00a0and\u00a0also\u00a0has another\u00a0sheet named “Folders Summary” which display a summary of the folders and subfolders\u00a0and their\u00a0size in bytes.<\/p>\n

    My\u00a0Excel addin is\u00a0in an\u00a0XLA format (zipped)\u00a0and is included below and offered unlocked – so feel free to pull it to bits – if you use it please give some credit or let me know of any improvements you would make?<\/p>\n

    The file as best as I can tell is compatible with Excel 2000, 2003 and 2007. In Excel 2007 you will find the button to bring up the form to\u00a0browse for\u00a0a folder path in the Custom Toolbars section\u00a0of the\u00a0Add-ins ribbon. Enjoy.<\/p>\n

    \"\"<\/p>\n

    filedir.zip<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

    Recently I wanted to list in an email the contents of a windows folder containing 250+\u00a0files and folders\u00a0so that I could comment on some of them rather than copying and pasting. Excel to me seemed like the obvious application to use. I could attach\u00a0an .xls file to the email with a nice structured\u00a0comment list.\u00a0I found […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[26],"tags":[123,79,78,131,117,27,119,121,30,130],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts\/112"}],"collection":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/comments?post=112"}],"version-history":[{"count":0,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/posts\/112\/revisions"}],"wp:attachment":[{"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/media?parent=112"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/categories?post=112"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/simoncpage.co.uk\/blog\/wp-json\/wp\/v2\/tags?post=112"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}