Have a look at Re^3: Win32::Ole excel external data range, in which I show the use of dynamic ranges. It does other things too (it's answering a different question), but I think it's reasonably clear.

Regards,

John Davies