Probably several years too late, but I've just been working in the same problem...

Assuming your Excel is set up with some report header information, then when you try to select it via openrowset, you'll see this header info returned. It looks unstructured. It doesn't mean there's a problem there.

What I do is to select a large bunch of columns to the right of my last data column, and delete them. They're probably blank already, but this seems to help.

Then, I place the Excel cursor in a cell below my column headers, and change the types of the cells to suit (ie. Short Date for a date column) the data. I save and close the Excel with the cell with focus in the under the left most data item. Not sure if this latter part makes any difference, as yet.

If I don't do the above, I sometimes see the error you mentioned. But if I do, all seems to work magically. Seems a bit of black art to me, especially when trying to change the format of the data from within Excel...

Regards, Greg.