Excel 2007 OLEDB connection: error when file contains hyperlink

Question

Before everyone here closes this question for being a duplicate, I should say that I have looked at the other ones, and tried the answers with no luck.

Here's the connection string I'm using

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePathBox.Text + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

I've searched high and low about the 2007 connection string and I've tried every single one I've seen and many combinations there of. But no matter what I get an exception saying External table is not in the expected format.

I have Excel 2007 installed and I tried installing the correct drivers seperately.

Edit

Ok so weird thing: It only happens with the file I'm trying to convert. I made a new xlsx file, and it worked fine. Just happens to be this particular file.

EDIT 2

The file I was trying to conver had email addresses in it. When the email address is typed in, excel automatically makes it a hyperlink. This is the problem. If I remove the hyperlink from the text, the conversion works fine. Is there something special i have to do when connecting if the file contains hyperlinks?

Answer 1

Here is one that works for me for XLSX files:

strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dataSource + ";Extended Properties=\"EXCEL 12.0 XML;HDR=YES;IMEX=1\";Persist Security Info=True;Jet OLEDB:Database Password=admin";

For xlsm I use:

strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dataSource + ";Extended Properties=\"EXCEL 12.0 Macro;HDR=YES;IMEX=1\";Persist Security Info=True;Jet OLEDB:Database Password=admin";