Man, I've been struggling with this one for years. If you want to spend a lot of money, there are a number of commercial document management systems out there. If you can't spend a lot of money, then you'll probably want to look at open-source or free solutions. You nailed the two big ones, WSS and Alfresco.
The big trouble with adoption is that document management systems are difficult to understand and use. Both Alfresco and WSS make it fairly easy to work with Microsoft Office documents directly from the application via the Sharepoint protocol, and I believe that both are on track to support CMIS which may or may not make things easier in the future (for other applications).
The big benefit to document management systems, at least in the case you're describing, is that you can force content models. That is, you can enforce required meta data for every content type, and this meta information lives and dies with the document. No more goofy file and folder naming conventions.
Then of course you have your standard benefits: searchability, version control, workflow etc.
Having evaluated both Alfresco (and Alfresco Share) and WSS, I would say that WSS is going to get you going a lot faster than Alfresco. First, WSS does integrated Windows / AD authentication out of the box. Alfresco can do it to, but it's non-trivial to setup, especially if you're not familiar with Java-based web applications (lots of XML!). Second, you can create your content models in WSS via the browser quite easily. Alfresco is considerably more difficult to work with in this regard, and you'll find yourself hacking quite a number of XML files to accomplish the same goal.
In my opinion, Alfresco is extremely powerful, but is one half full-blown application, and one-half framework. That is to say, it's got an ok web client that can do quite a bit, but it's real benefit is in its core repository, which you can get at through javascript "web scripts", CMIS, Java, etc. If you were going to build a document management system for your corporation, Alfresco would be a great base to start from.
If I were you, I'd setup both WSS and Alfresco and play around with them (be sure to try Alfresco Share in addition to the standard Alfresco Explorer application). Within a few weeks you'll have a pretty good understanding of what they're capable of, and whether you're going to get any sort of adoption from your users. Be sure to put some focus on edge cases. I've found that these systems are great for MS Office files, but working with various other applications can be quite difficult.
Which brings me to another part of your question: yes, Alfresco and WSS can provide "Explorer"-like interfaces to their document repositories. WSS does it via some special WebDAV, and Alfresco can provide both WebDAV and a native CIFS (windows shared folder) interface. Unfortunately, it's hit or miss whether these will work with a particular application. Try opening and saving files from Adobe Creative Suite from and to WSS via WebDAV...not too reliable. Try using Alfresco's CIFS support to save a modification to an existing file in an application that saves by deleting and overwriting the original file, like gedit....versioning is lost. Blah.
Again, I haven't talked about more expensive options (even MOSS is quite spendy). You may find it worthwhile to work with a vendor that can provide consultation so that you're not stuck trying to do this alone....or at least a more robust, user friendly solution. But really, that's going to start somewhere around 25K-100K. Good luck selling that if people aren't even aware that there's a problem.
Aside from document management systems, there's no solution to your problem. Your shared file system is going to grow and grow and grow.
