In my work there is no one right answer. It all depends on what you are making and what look you are trying to achieve.
That said, for pieces where I want to highlight the wood, I will sand to 600 grit or higher, and I have sanded to 2000 grit on occasion. The reason is that wood grain can be incredibly detailed, and sanding to higher grits brings out that detail. Consider fine fleck in quarter sawn cherry. At every grit above 220, a little more of that fine detail comes out. On a floor, sanding above 220 may not be noticed, or even if it is, it may not be admired as the larger pattern of the floor overwhelms the fine detail. On a small to medium piece of furniture, it will be noticed. The ribbon grain in the[member=4907]kcufstoidi[/member] doors are a good example, and I bet they look even better when you look at them in person.
The Pollock Analogy
Fine differences in detail will be noticed and appreciated even if the the viewer does not identify the detail consciously. A while back a couple of researchers had non-artists pick between Jackson Pollock paintings and paint-splash paintings by copy-cat artists. They used a variety of paintings from Pollock and different artists, but always had the non-artist pick between one Pollock painting and one copy-cat painting. The non-artists didn't know which was Pollock's. Nearly always (in the 80-90% range), the non-artist subject preferred the Pollock painting. Even if they didn't like paint splash art, they would pick Pollock as the lesser of evils.
In trying to determine why this might be, the researchers started looking at the paintings in more detail. The only difference that they could find was that Pollock's paintings had an order of magnitude more fractal detail that the copy-cat paintings. That is, the detail was finer, and it was organized into semi-random patterns - patterns that were not perfectly geometric. The non-artists could not articulate this difference - no one even imagined that it existed until the researchers took a closer look - but viewers could tell that there was a difference, and clearly preferred the more detailed renderings of Pollock.
The grain of the most interesting woods is like that. Think of patterned detail in the flecks of quarter-sawn sycamore. Think of what the curl does to curly maple - it superimposes a curl pattern on top of the normal grain, and makes both more interesting. Think of the change of grain direction in ribbon grained woods. The detail of burls, of crotches, color that doesn't follow grain in woods like ziricote or rosewood. Wood grain is not perfectly geometrical, it is a combination of the randomness of a natural material combined with patterns that organize that randomness. The more patterns are superimposed on other patterns, the more detail. And for some reason, the human mind sees beauty in that, even when it doesn't know why.
Finishing techniques can either enhance or detract from the beauty of the grain. For example, if I want to bring out the grain by sanding to 600 grit or higher, I'm not going to use stain, which in my opinion just obscures detail. (I did used a transparent stain/oil on my deck though - looks great!) Likewise, I think oil finishes bring you as close to the wood as it is possible to get, so I tend to use oil a lot. I haven't tried any of the catalyzed oil finishes like Rubio (& Osmo?) yet, but the more I learn, the more I want to.
While that may be a justification for sanding to higher grits, it's one that only works in certain circumstances. In other circumstances, I sand differently. For my deck, 120 is plenty. For shop fixtures, 220-320 is enough. For architectural woodwork, 220-320 is enough. Bottom line - think about what you are trying to accomplish, and sand enough to accomplish it. Of course, you can always do more sanding if you truly enjoy the process, but I'd rather use my time other ways.
As an aside, I don't think that sanding to high grits affects overall absorption of oils. I've seen cherry (and other non-oily open woods - sycamore, oak, walnut) sanded to 600 grit just suck the first coat of oil into the wood, once to the point that it started coming out pores (not cracks or knots) in the other side of a 1/2" thick board. That said, a rough surface is going to accumulate oil on the surface in the wood fibers that are still sticking up. I imagine it holds surface (film) finishes better as well.
