Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Dear,
i have the same problem but how to see if the proper ODBC is installed or not?
best regards
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.