Solved

Excel 2010 Global Warming File Tracking File

Posted on 2013-12-10
5
220 Views
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:

Filename            
Path or Location
Type
Date Content Created
Author
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.
Sample-Workbook.xlsx
0
Comment
Question by:Jeremy-M
  • 3
  • 2
5 Comments
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
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


Reg
0
 

Author Comment

by:Jeremy-M
Comment Utility
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.

Jeremy
High-Profile-Non-Profits.xlsx
0
 
LVL 19

Expert Comment

by:regmigrant
Comment Utility
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.

Reg
dirparser---template1.xlsm
0
 

Author Comment

by:Jeremy-M
Comment Utility
Reg,

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.
ADifferntApproach.xlsm
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 500 total points
Comment Utility
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.


Reg
dirparser---template1.xlsm
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now