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

Wed, 21 Sep 2011 08:51:36 GMT

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!

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

Thu, 22 Sep 2011 09:11:24 GMT

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

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

thierrytt1102 — Wed, 09 Nov 2011 12:55:43 GMT

Dear,

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

best regards

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

Wed, 09 Nov 2011 18:57:52 GMT

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.

topic How use OLEDB to connect to XLSM (excel file)? in QlikView

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

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

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

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