Excel 2010 Global Warming File Tracking File

I need to macro that will maintain a workbook that lists all files related to a global warming reduction research project. All files will be saved to and maintained in a user specified Parent Folder or sub-folders of the user specified Parent Folder.

The workbook has the columns listed below:

Path or Location
Date Content Created
Date Last Save
Last Saved by
Comments or Description

The files will be listed in alphabetical order of filename.

The user should be able to click on a “Refresh” button and the macro will search the user specified directory tree or Parent Folder (Cell C1) and all sub-folders for files whose file extensions are “XLSX”, “XLSM”, “XLS”, “DOCX”, “DOCM”, “DOC”, “HTM”, “HTML” AND “TXT.”

The macro will save the filename in the Filename column, the pathname in the Path or Location column, the file extension in the Type column, etc.

Researchers may enter Comments/Descriptions for files listed a possible problem will be to keep the Comments/Descriptions associated with a file.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

If you are intending to generate many files and many users may access them then ultimately you will want version control, check out management, backups etc -  especially if the data is potentially sensitive as you will then need to think about peer review and change management processes - If this is a serious project with a budget then you should consider a proper document management system  -  something like sharepoint to manage these elements for you would be a step in the right direction but a grown up system like documentum if its bigger.

If you must use Excel then most of what you want to achieve in terms of searching for files etc is fairly straightforward. I suggest putting the comments/description into the document properties rather than maintaining them separately and trying to maintain a link to the correct document - what if someone saves it with a different name for example.

I've  a macro I've been playing with which I'm happy to rework to make it suitable - It wasn't intended for this function but has most of the basic elements you need.
However I need to know if you are using 32 or 64 bit versions of excel so I can upload the appropriate version

Jeremy-MAuthor Commented:
Thanks for your offer to help us.  Thanks you.

There will be 4 or 5 major Excel workbooks and maybe 10 workbooks
There will be 8 - 10 major Word documents and maybe 200 smaller docs.

We need to access source docs (we have many of those).

User access is limited to 5 people, maybe a 6th.

This a small group of volunteers who have a plan to ignite a movement to using sustainable/renewable by offering professional support for grant writing, fundraising, educational materials, etc. to high profile not profits.  

I’m forever tasked with revising our website, http://www.magikool.org/.  I’ve attached a workbook listing 10 high profile non-profits. That will be highlighted on the web-site.  Some are in states whose legislatures deny the climate reality.  We have a list of over 250 print and electronic reporters, columnists, talk show hosts with e-Mail address who are very concerned about global warming.  Once, we've put it all together, hopefully, we will send a press release to everyone we've spoken with after the hoildays, but if we see a slow newsday Friday, we''ll GO

All we want is a file where we can and quickly locate documents.  I created the first version of this with hyperlinks to each document.  Would it be possible to add a hyperlink to the filename?   With the hyperlink we will not need the Path of Location

We’re game to try your macro.

Thanks again for you offer of help.

and which version of Excel are you using?

The attached will work for 32 bit and fail on 64 bit. But, if it works, will give you an idea of where I'm at

Bear in mind its a work in progress so some elements are scrappy and the filetype analysis needs work.

When you press 'analyse folder' it will ask for a directory. the user must have access to that directory and the permissions to run the various internal scripts.

Jeremy-MAuthor Commented:

Thanks for sharing. What you are developing.  When I clicked on the Analyse button an error message was displayed:       An error occurred: 53
                                                     At: open input file: pathname and filename.

I’m uploading a file that was created by another member that has a macro that seems to be along the lines we are both pursuing. I don’t know very much VBA so I’m stuck.  

The macro works fine but the filename and path need to be in different columns.   If you want to use it, it’s yours.   If can modify the macro so that the filename and path are written to different columns  for me even better.  

File Type seems to be a problems  I would be wonderful to have a File Type column.  

Thanks for your efforts I my upload helps with your project.
The error 53 means you don't have permissions to write a file to the default directory (the default on the macro I mean) , I'll modify it to point to your home directory. The filename (when it works) is a hyperlink with the folder the directory is in - the path on the end is a hyperlink to the containing folder.

The approach in the attached file is a good one - and I considered it in the initial stages - but it uses a scripting object which needs specific permissions and I wanted something I could use in a variety of network setting so I went for a more generic  (but much less elegant) solution that uses the permissions of the logged in person and therefore has access to more folders. Although it uses a   dos command (which is clunky) it won't fail when it hits a folder it doesn't have permission to access.

I'll do some fiddling and upload file type revisions and, hopefully, comments later - I'll also adjust the headings to match the sample.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.