How use OLEDB to connect to XLSM (excel file)?

[#1]

Hi, i cannot get data from .xlsm as i was using with .xls files. With xls i was using a OleDB connection without problem with these sentences:

let RutaOLEDB = '[Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\2011\2011_FILE.xlsm;Extended Properties="Excel 8.0;"]';

let Ruta = '[.\2011\2011_FILE.xlsm]';

CONNECT TO $(RutaOLEDB);

tables:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

    LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

    CrossTable(MesNombre, Datos, 3)

    vDatos:

    LOAD DÍA as Día,

         left(filename(),4) as Año,

         purgechar(trim('$(sheetName)'),'$') as Técnico,

         Enero,

         Febrero,

         Marzo,

         Abril,

         Mayo,

         Junio,

         Julio,

         Agosto,

         Septiembre,

         Octubre,

         Noviembre,

         Diciembre

    FROM

    $(Ruta)

    (biff, embedded labels, header is 1 lines, table is [$(sheetName)], filters(

Remove(Col, Pos(Top, 28)),

Remove(Col, Pos(Top, 27)),

Remove(Col, Pos(Top, 26)),

Remove(Col, Pos(Top, 25)),

Remove(Col, Pos(Top, 24)),

Remove(Col, Pos(Top, 23)),

Remove(Col, Pos(Top, 22)),

Remove(Col, Pos(Top, 21)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 18)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 16)),

Remove(Col, Pos(Top, 15)),

Remove(Col, Pos(Top, 14))

));

I saw that with xlsm is not used BIFF but OOXML but when changed it doesn't work.

I also tried to change the Properties Excel 8.0 to other bigger with the same result, it doesn't find the sheets of excel file.

I need your help, thanks!

[#2 · Accepted solution]

It's solved. There were 2 problems, the principal was the system doesn't have the proper ODBC installed and the second was i needed to add purgeChar(sheetName,'$') .

Issue closed

[thierrytt1102]

Dear,

i have the same problem but how to see if the proper ODBC is installed or not?

best regards

[#4]

Hi Thierry, you will need to install Office 2007 or higher. Then could create a odbc connector with Microsoft Excel Driver (*.xls, *.xlsm, *.xlsb).

I don't know if Office 2003 does work.

Hope this can help you.