Crosmando: I never played any of the Dune games (C&C games were the only Westwood RTS games I touched), but I loved the novels.
Can you like play as the Atreides-led Fremens and like use Worms in battle?
In Dune 2 you can get Fremen as a special ability from the Atreides Palace. They are free, but are under the control of the AI. In large numbers they can become quite strong, but don't expect too much of them.
In Dune 2000 you can train Fremen and fully control them yourself, but they aren't that good.
In Emperor: Battle for Dune the Fremen are at their best. They come with camouflage. As long as they don't attack (or no infantry is near) they are invisible. The regular Fremens are deadly against enemy infantry. The Fedaykin, however, are where the real fun begins. They can use thumpers to call sandworms and ride them, and in groups, they can make short work of enemy vehicles and structures with their weirding modules.
The biggest downside is, however, that all of the Dune games are primarily based on the movie. Westwood also took some very big liberties with the Spacing Guild, Tleilaxu, IX and others in Emperor ('Bi la kaifa' doesn't mean 'long live the fighters').
If you are interested in playing a Dune game, then try to get a hold of Dune 1 from Cryo Interactive. It is also based on the movie, but, unlike the Westwood-games, actually follows the story (to a degree). The only bad thing I can say about this game is, that Duncan Idaho looks more like Londo Mollari from Babylon 5 and has been turned into an accountant.