Document Control System - Help Needed for small project/experiment

Good Day Gentlemen!

I've posted in here quite a bit recently, grabbing bits and bobs to try and complete a wee project I've started for myself.

I'm a document controller and I'm used to doing my job using some kind of Electronic document management software.

I believe I can create something better fitting on Excel and it would probably be a lot cheaper than the £2000 I've been quoted this far, and a lot more flexible.

However I know I can't do this alone, although I've picked up a bit of VB from posting on here and written some of my own macros, I can tell when something is simply out of my grasp. I've searched around quite a bit for someone else who's taken this approach before, and though I know excel is used by other for document controllers I don't think anyone else has undertaken the task of turning a workbook into a proper multi-functioning document control system.

This is probably boring to the most of you, but if anyone likes a challenge and enjoys programming then your help would be greatly appreciated.

Basically this workbook would include:

• A Master Document Register (MDR)
• A Transmittal Form
• A Transmittal Register
• A list of contacts

The master document register would include metadata for each document(doc no, title, revision, date, originator, project no), a hyperlink that would open the document.

The transmittal would be populated by selecting the documents from the register and using a macro. You'd then fill in the rest of the details yourself. You'd be able to create a pdf of the transmittal form and generate an e-mail by selecting the contacts you want from the contact list, using a macro.

When you save the transmittal it would create a new row in the transmittal register to say when, why and who the document was sent to. and also have check boxes to say if it was returned or not.


Those are the basic requirements I'd be going for. I'd probably add to it over time. If anyone who's quite advanced in excel/VB think that this is too much of a task to undertake. Then please say, I get that I'm probably punching above my weight here but I think it can be done.

So far I have a speadsheet that can generate e-mails automatically and can (partially) fill out the transmittal form based on data from the MDR.
But I've been working on it using a fairly messy spreadsheet that I was experimenting using macro's on so it's full of unwanted coding etc.

Again, I urge anyone who fancies a bit of a challenge to reply. I'm on here most days and will be updating this post regardless with what I come up with.


Kind regards,

R

I think it's doable, but there's a lot to think about.

Fundamentally, I think you're using the wrong tool. A database would be much better suited, so Access would be a better choice - it makes data management a breeze and allows you to keep a full history of changes in a more useful way. It also won't slow down as you increase the data - Excel will.

Reports will be easier and updates can cascade, for example if you want to change the metadata across all versions, this is straightforward in a database - it's not so straightforward in Excel.

So in summary, this would be more robust, faster to build and would require less code in Access, but is certainly doable in Excel, it will just require a lot more coding

Thanks for the input Kyle! I have very little experience with Access, and the little I do have was only dealing with a database that was already created. You're probably completely correct though!

Would access be able to cover all the points I mentioned?

Man, does this mean I have to join an Access Forum too? I was just getting settled here! :P

Would it be possible to use both? or would that just over complicate things?

I've always thought Access can provide a much nicer interface, but I'm unsure wether it would be good for all the applications I want.

I don't really understand why you'd want to use both - what would you gain from that?

Yes, access would cover all of your points

That was just an addition in case I wouldnt be able to do everything in access. And also because the thought of having to learn how to do everything in access is horrible, I'm going to have to re-think my entire strategy :P just not a nice thought for a Friday, haha!

I'm kind of tempted just to try do it in excel, unless access is way easier to use in general? I just think the amount of help I'll need regardless of the program I use is quite substantial and theres always thousands of people on the excel forums and only a couple of hundred on the access ones.

Thanks Kyle, Haha, it does seem like quite a task You're managing to talk me out of my challenge! Maybe I should just make my challenge convincing my managers to part with some cash to buy a program :P but for today at least I'm going to give it a shot in excel, because otherwise I've got nothing to do!

then do it in Access if you've got nothing to do. You'll regret it in Excel

I should, but I actually don't have Access on this computer, I'd have to wait for IT to install it. But I have requested it because of your suggestion. Monday's a new week and I'll start it then so I have a project for the week. I'll just practice my Macro writing for today instead Thanks for your input though dude, much appreciated!