I just ran into this issue today.

One existing answer from Himanshu says his file name was not matching the expected value.

For me - the expected tab name within the Excel spreadsheet didn't match the expected tab name. A user accidentally fat-fingered in a space at the end of the tab name so the original error was thrown (Opening a rowset for "Sheet1$" failed. Check that the object exists in the database)

TL;DR - Check to make sure your tab names don't have typos or leading/trailing spaces!