Far from a SQL expert but I think your problem is the second ref to sheet1$ should not be there -
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Thanks Jim, just tried removing the second ref to the sheet and still getting the same error, but thanks for the suggestion!