Collins is not as uncommon a scientist as most non-scientists seem to think. I've known a few religious scientists (although only one of them an outspoken born again). People like Sam Harris only show how limited and dogmatic their ideas are when they claim that one can be an excellent scientist while not having a scientific frame of mind.

From my experience, there are more atheists among scientists than among the population at large, but they're still a minority, even in hard sciences. But obviously most scientists don't discuss religion with fellow scientists, in part because it is such a dangerous topic, in part because they generally aren't all that interested in religious questions. Science is a passion, at least for those that make a career out of it and there's little room for an intense faith in a scientists' life (hell, there often is barely room for a family).

Collins is perfect for the job, especially if he uses the position to defuse the antagonism between religion and science that pervades US society. By promoting mutual exclusiveness, Harris and Dawkins (amongst many others) are simply promoting their own wallets at the expense of science's position within US culture. For people that pretend to stand on facts, it is amazing how they seem to miss the obvious one: if a vast majority of the tax payers are religious and a large minority is very religious ("born again"), convincing them that the pursuit of science is in opposition to their beliefs means that the pursuit of science will suffer.

If you want an academic life in which you never have to worry about the interface between science and religion, you go to Europe. There you can even make fun of religion - it won't matter. But in the US, it simply isn't an option. The next best thing is to promote people like Collins so they can defuse the situation, in stead of elevating zealots like Dawkins as spokespersons.