Probably you can't but if you could eliminate Excel all together you'd save yourself a whole lot of pain. Is there any reason that you can't control the sheet layout and formatting from one period to the next? Why not use db for input and forget the sheets?
The best you will be able to do IMHO is to ascertain (per sheet) how many header rows there are and input that in a prompt. Then if you can prevent sheet data from going outside of a horizontal range, you could use a dynaminc (vertically) range to automatically grab the data and write it to the table via automation. You'd need some way to match the spreadsheet column to the correct table field. If you cannot control the sheet column name or position (i.e. they must be static) then I'd say you're beat.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.