Sure the excitement of pulling a ridiculous weapon will be gone (no doubt I'll FINALLY pull a WoP a week after Mod9 drops), but I think the necessity for so many different weapons will make for more "hey, that's pretty decent" moments, which have been kinda rare as of late. I'm not sure which is better. I'm leaning toward the latter, at this point.
I suspect many folks, like myself, went through their inventory recently to see how many trans/PG or holy silver they had (in addition to keeping an eye out for bludeoning/etc.). I found a few gems that I had squirreled away, and picked up a nice sickle on the AH for my rogue (sorry to whoever posted it for such a cheap price... early bird gets the worm, I guess). I'll be back to having serious inventory issues on my halfling assassin, no doubt. The AH and trade forums will almost assuredly be fairly busy for the next few weeks as people adjust their inventory. That, in some respect, is something exciting to look forward to.
All in all, I'm optimistic about the Mod9. Ring crafting may make up for a lot of the concerns we have regarding weapon changes. The named loot may be well planned to address some of the issues (one can hope, at least). My only real gripe is the lack of HiPS for Assassins (for flavor, if nothing else), which I've vented about to anyone who will listen. But, I'm nearly over it... I think.