Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Excel 2010 Global Warming File Tracking File

Posted on 2013-12-10
Last Modified: 2013-12-26
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.
Question by:Jeremy-M
  • 3
  • 2
LVL 19

Expert Comment

ID: 39710280
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


Author Comment

ID: 39710427
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.

LVL 19

Expert Comment

ID: 39711251
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.


Author Comment

ID: 39711875

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.
LVL 19

Accepted Solution

regmigrant earned 500 total points
ID: 39713826
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.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question