Trouble using Microsoft Query to extract data from an .xlsm file

Question

When I try to use Excel's Data | Get External Data | From Other Sources and select "Excel Files*" to query data from an Excel .xlsm file, I receive the error message, "Unrecognized database format" 

Perhaps I'm missing an install or an update or maybe we can not use Microsoft Query to extract data from a .xlsm file?

Any help regarding this problem would be appreciated.

Thanks.

Answer 1

I've tried that; unfortunately the .xlsm uses hundreds of formulas that reference a table named: FTE_Tbl

Example:

     =SUMIFS(FTE_Tbl[Accum_FTEs],FTE_Tbl[Subdivision],I2)

After saving it as an Excel 1997-20013 .xls file, the formula becomes:

     =SUMIFS(FTEs!$N$6:$N$2739,FTEs!$B$6:$B$2739,I2)

IE., it loses the table reference, which changes as the table grows or shrinks, and becomes an absolute reference ... not an acceptable solution.

Is there a way to query an Excel 2010 .xlsm file?

Answer 2

Hi,

Save the file in Excel 1997-2003 format and see if it works.