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,  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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

679 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