• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

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:

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
Jeremy-M
Asked:
Jeremy-M
  • 3
  • 2
1 Solution
 
regmigrantCommented:
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
 
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.

Jeremy
High-Profile-Non-Profits.xlsx
0
 
regmigrantCommented:
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
 
Jeremy-MAuthor Commented:
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
 
regmigrantCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now