I don't think you can get this code to work in any version of Excel. The formula you are assigning to mydata1 has a syntax error. I will bet that you changed the string for mydata1. It has an unmatched single quote. You don't need the single quote here anyway. Try this:
mydata1 = "=c:\extract data\[r.xls]Sheet1!$A$1:$A$10"
or if you want to use the quotes
mydata1 = "=c:\extract data\'[r.xls]Sheet1'!$A$1:$A$10"