Microsoft – Cómo usar ADO con datos de Excel desde Visual Basic o desde VBA


 

Cómo usar ADO con datos de Excel desde Visual Basic o desde VBA Id.   de artículo: 257819 – Ver los   productos a los que se aplica este artículo       Resumen

     Más información

     INTRODUCCIÓN

     Conectarse a Excel con ADO

     Cómo se utiliza el proveedor OLE DB   de Microsoft Jet

     Uso del proveedor OLE DB de   Microsoft para controladores ODBC

     Consideraciones aplicables a ambos   proveedores OLE DB

     Recuperar y modificar datos de   Excel con ADO

     Cómo se seleccionan datos

     Seleccionar datos de Excel mediante   código

     Seleccionar datos de Excel mediante   el control de datos ADO

     Seleccionar datos de Excel mediante   comandos del entorno de datos

     Cómo se cambian datos de Excel:   Modificar, Agregar y Eliminar

     Recuperar la estructura del origen   de datos (metadatos) de Excel

     Información de tablas en consultas

     Información de campos de consultas

     Enumeración de tablas y campos, y   sus propiedades

     Uso de la ventana Vista de datos

     Limitaciones de Excel

     Referencias

     Propiedades

     Propocionar comentarios

// <style>.tocTitle, #tocDiv{display:   none;}</style>

     Resumen

// En este artículo se trata el uso de ActiveX Data Objects (ADO) con   hojas de cálculo de Microsoft Excel como origen de datos. También se explican   los problemas de sintaxis y las limitaciones concretas de Excel. Pero no se   describen las tecnologías de tablas dinámicas y OLAP, ni otros usos   especializados de los datos de Excel.
 
  Para obtener información adicional al respecto, haga clic en el número de   artículo siguiente para verlo en Microsoft Knowledge Base:

303814

(http://support.microsoft.com/kb/303814/   )

Cómo   usar ADOX con datos de Excel desde Visual Basic o desde VBA

     Volver al principio | Propocionar   comentarios

     Más   información

// INTRODUCCIÓN

// Las filas y las columnas de una hoja de cálculo de Microsoft Excel   son muy similares a las filas y las columnas de una tabla de base de datos.   En tanto que los usuarios tengan en cuenta que Microsoft Excel no es un   sistema de administración de bases de datos relacionales, y reconozcan las   limitaciones derivadas de este hecho, puede tener sentido en algunas   ocasiones aprovechar las ventajas de Excel y sus herramientas para almacenar   y analizar datos.
 
  Microsoft ActiveX Data Objects permite tratar un libro de Excel como si fuera   una base de datos. En las siguientes secciones de este artículo se describe   cómo se puede conseguir:

Conectarse   a Excel con ADO

Recuperar y   modificar datos de Excel con ADO

Recuperar   la estructura del origen de datos (metadatos) de Excel

NOTA:
  Las pruebas para este artículo se realizaron con Microsoft Data Access   Components (MDAC) 2.5 en Microsoft Windows 2000 con Visual Basic 6.0 Service   Pack 3 y Excel 2000. Este artículo no permite confirmar ni analizar las   diferencias de comportamiento que los usuarios pueden observar con versiones   diferentes de MDAC, Microsoft Windows, Visual Basic o Excel.

Conectarse   a Excel con ADO

// ADO puede conectarse a un archivo de datos de Excel con uno de los   dos proveedores OLE DB incluidos en MDAC:

Proveedor   OLE DB de Microsoft Jet, o bien

Proveedor   OLE DB de Microsoft para controladores ODBC

Cómo   se utiliza el proveedor OLE DB de Microsoft Jet

// El proveedor Jet sólo requiere dos elementos de información para   conectarse a un origen de datos de Excel: la ruta de acceso, incluido el   nombre de archivo, y la versión del archivo de Excel.
 
  Proveedor Jet utilizando una cadena de conexión

Dim   cn as ADODB.Connection

Set cn = New ADODB.Connection

With cn

            .Provider   = “Microsoft.Jet.OLEDB.4.0”

            .ConnectionString   = “Data Source=C:\MyFolder\MyWorkbook.xls;” & _

“Extended Properties=Excel 8.0;”

            .Open

End With

                                              

Versión   de proveedor: es necesario utilizar el proveedor Jet 4.0; el proveedor Jet   3.51 no admite los controladores Jet ISAM. Si especifica el proveedor Jet   3.51, recibirá un mensaje de error en tiempo de ejecución similar al   siguiente:

No   se pudo encontrar ISAM instalable.

Versión   de Excel: Especifique Excel 5.0 para un libro de Excel 95 (versión 7.0 de   Excel) y Excel 8.0 para un libro de Excel 97, Excel 2000 o Excel 2002 (XP)   (versiones 8.0, 9.0 y 10.0 de Excel).
 
  Proveedor Jet utilizando el cuadro de diálogo Propiedades de vínculo de datos
 
  Si utiliza el control de datos ADO o el entorno de datos de la aplicación, se   muestra el cuadro de diálogo Propiedades de vínculo de datos para recopilar   los valores de configuración de conexión necesarios.

En   la ficha Proveedor, seleccione el proveedor Jet 4.0; el proveedor Jet 3.51 no   admite los controladores Jet ISAM. Si especifica el proveedor Jet 3.51,   recibirá un mensaje de error en tiempo de ejecución similar al siguiente:

No   se pudo encontrar ISAM instalable.

En   la ficha Conexión, busque el archivo de libro. Omita las entradas de Id. de   usuario y de contraseña, porque no son aplicables a conexiones de Excel. No   puede abrir un archivo de Excel protegido mediante contraseña como origen de   datos. Más adelante en este artículo encontrará información adicional al   respecto.

En   la ficha Todas, seleccione Propiedades extendidas en la lista y, a   continuación, haga clic en Modificar valor. Escriba Excel 8.0; separándola de   las demás entradas existentes mediante un punto y coma (;). Si omite este   paso, aparecerá un mensaje de error cuando pruebe la conexión, porque el   proveedor Jet espera una base de datos de Microsoft Access a no ser que   especifique lo contrario.

Vuelva   a la ficha Conexión y haga clic en Probar conexión. Observe que aparece un   cuadro de mensaje indicando que el proceso se ha completado correctamente.

Valores   adicionales de configuración de conexión del proveedor Jet
 
  Encabezados de columnas: De forma predeterminada, se supone que la primera   fila del origen de datos de Excel contiene encabezados de columnas que se   pueden utilizar como nombres de campos. Si no es el caso, debe desactivar   este valor; de lo contrario, la primera fila de datos   “desaparecerá” para ser utilizada como nombres de campos. Esto se   realiza agregando el valor HDR= opcional a Extended Properties en la cadena   de conexión. El valor predeterminado, que no necesita especificarse, es HDR=Yes.   Si no hay encabezados de columnas, debe especificar HDR=No; el proveedor   asigna a los campos los nombres F1, F2, etc. Como la cadena Extended   Properties contiene ahora varios valores, se debe escribir entre comillas y   agregar un par adicional de comillas para indicar a Visual Basic que trate el   primer conjunto de comillas como valores literales, como en el siguiente   ejemplo (donde se han agregado espacios adicionales para mayor claridad).

                                         

Uso   del proveedor OLE DB de Microsoft para controladores ODBC

// El proveedor para controladores ODBC (denominado en este artículo   “proveedor ODBC” para abreviar) también requiere sólo dos (2)   elementos de información para conectarse a un origen de datos de Excel: el   nombre del controlador, así como la ruta de acceso y el nombre de archivo del   libro.
 
  IMPORTANTE:
  De forma predeterminada, las conexiones ODBC a Excel son de sólo lectura. El   valor de la propiedad LockType del objeto Recordset de ADO no reemplaza este   valor del nivel de conexión. Debe establecer ReadOnly en False en la cadena   de conexión o la configuración de DSN si desea modificar los datos. De lo   contrario, aparecerá un mensaje de error similar al siguiente:

La   operación debe utilizar una consulta actualizable.

Proveedor   ODBC utilizando una cadena de conexión sin DSN

Dim cn as ADODB.Connection

Set cn = New ADODB.Connection

With cn

            .Provider   = “MSDASQL”

            .ConnectionString   = “Driver={Microsoft Excel Driver (*.xls)};” & _

“DBQ=C:\MyFolder\MyWorkbook.xls;   ReadOnly=False;”

            .Open

End   With

                                              

Proveedor   ODBC utilizando una cadena de conexión con DSN

Dim cn as ADODB.Connection

Set cn = New ADODB.Connection

With cn

            .Provider   = “MSDASQL”

            .ConnectionString   = “DSN=MyExcelDSN;”

            .Open

End   With

                                              

Proveedor   ODBC utilizando el cuadro de diálogo Propiedades de vínculo de datos
 
  Si utiliza el control de datos ADO o el entorno de datos de la aplicación, se   muestra el cuadro de diálogo Propiedades de vínculo de datos para recopilar los   valores de configuración de conexión necesarios.

En   la ficha Proveedor, seleccione Proveedor OLE DB de Microsoft para   controladores ODBC.

En   la ficha Conexión, seleccione el DSN existente que desee utilizar o elija Usar   cadena de conexión. Se abrirá el cuadro de diálogo de configuración de DSN   estándar para recopilar los valores de configuración de conexión necesarios.   Asegúrese de no seleccionar el valor de sólo lectura predeterminado si así lo   desea, como se ha mencionado anteriormente.

Vuelva   a la ficha Conexión y haga clic en Probar conexión. Observe que aparece un   cuadro de mensaje indicando que el proceso se ha completado correctamente.

Otros   valores de configuración de conexión del proveedor ODBC
 
  Encabezados de columnas: De forma predeterminada, se supone que la primera   fila del origen de datos de Excel contiene encabezados de columnas que se   pueden utilizar como nombres de campos. Si no es el caso, debe desactivar   este valor; de lo contrario, la primera fila de datos   “desaparecerá” para ser utilizada como nombres de campos. Esto se   realiza agregando el valor FirstRowHasNames= opcional a la cadena de   conexión. El valor predeterminado, que no necesita especificarse, es FirstRowHasNames=1,   donde 1 = True. Si no hay encabezados de columnas, debe especificar FirstRowHasNames=0,   donde 0 = False; el controlador asigna a los campos los nombres F1, F2, etc.   Esta opción no está disponible en el cuadro de diálogo de configuración de   DSN.
 
  Sin embargo, debido a un error en el controlador ODBC, la especificación del   valor FirstRowHasNames no tiene efecto actualmente. Ese decir, el controlador   ODBC para Excel (MDAC 2.1 y posterior) siempre trata la primera fila del   origen de datos especificado como nombres de campos. Para obtener información   adicional sobre el error de los encabezados de columnas, haga clic en el   número de artículo siguiente para verlo en Microsoft Knowledge Base:

288343

(http://support.microsoft.com/kb/288343/   )

ERROR:   El controlador ODBC para Excel pasa por alto la configuración   FirstRowHasNames o de encabezado

Filas   en las que buscar: Excel no proporciona ADO con información de esquema   detallada sobre los datos que contiene, como lo haría una base de datos   relacional. Por lo tanto, el controlador debe buscar en unas cuantas filas de   datos existentes para hacer una conjetura hipotética en los tipos de datos de   cada columna. El valor predeterminado para “Filas en las que   buscar” es ocho (8) filas. Puede especificar un valor entero entre una   (1) y dieciséis (16) filas, o cero (0) para buscar en todas la filas   existentes. Esto se realiza agregando el valor MaxScanRows= opcional a la   cadena de conexión, o cambiando el valor Filas en las que buscar en el cuadro   de diálogo de configuración de DSN.
 
  Sin embargo, debido a un error en el controlador ODBC, la especificación del   valor de Filas en las que buscar (MaxScanRows) no tiene efecto actualmente.   Es decir, el controlador ODBC para Excel (MDAC 2.1 y posterior) siempre busca   en las 8 primeras filas del origen de datos especificado para determinar el   tipo de datos de cada columna.
 
  Para obtener información adicional sobre el error de Filas en las que buscar,   incluida una solución simple, haga clic en el número de artículo siguiente   para verlo en Microsoft Knowledge Base:

189897

http://support.microsoft.com/kb/189897/  

XL97:   Se truncan los datos a 255 caracteres con el controlador ODBC para Excel

Más   opciones: Si crea una cadena de conexión utilizando el cuadro de diálogo Propiedades   de vínculo de datos, puede que observe otros valores de Propiedades   extendidas agregados a la cadena de conexión que no son absolutamente   necesarios, como:

…   DefaultDir=C:\WorkbookPath;DriverId=790;FIL=excel   8.0;MaxBufferSize=2048;PageTimeout=5;

                                              

Mensaje   de error de “Secuencia de ordenación” en el Editor de Visual Basic
 
  En el entorno de diseño de Visual Basic con algunas versiones de MDAC, es   posible que vea un mensaje de error similar al siguiente la primera vez que   el programa se conecte a un origen de datos de Excel en tiempo de diseño:

Secuencia   de ordenación seleccionada no admitida por el sistema operativo.

Este   tipo de mensaje sólo aparece en el IDE y no aparecerá en la versión compilada   del programa. Para obtener información adicional al respecto, haga clic en el   número de artículo siguiente para verlo en Microsoft Knowledge Base:

246167

(http://support.microsoft.com/kb/246167/%5DLN%5D/   )

PRB:   Error en la secuencia de ordenación la primera vez que se abre un conjunto de   registros ADODB en una hoja de cálculo de Excel

Consideraciones   aplicables a ambos proveedores OLE DB

// Precaución sobre tipos mixtos de datos
 
  Como se ha especificado anteriormente, ADO debe hacer una conjetura sobre el   tipo de datos correspondiente a cada columna de la hoja de cálculo o del   rango de Excel. Los valores de configuración de formato de celdas de Excel no   afectan a todo esto. Puede producirse un problema grave si se combinan   valores numéricos con valores de texto en la misma columna. Tanto el   proveedor Jet como el proveedor ODBC devuelven los datos del tipo   mayoritario, pero devuelven valores NULL (vacíos) para el minoritario. Si los   dos tipos se combinan en la misma proporción en la columna, el proveedor   elige el tipo numérico en detrimento del tipo de texto.
 
  Por ejemplo:

En   las ocho (8) filas examinadas, si la columna contiene cinco (5) valores   numéricos y tres (3) valores de texto, el proveedor devuelve cinco (5)   números y tres (3) valores nulos.

En   las ocho (8) filas examinadas, si la columna contiene tres (3) valores   numéricos y cinco (5) valores de texto, el proveedor devuelve tres (3)   valores nulos y cinco (5) valores de texto.

En   las ocho (8) filas examinadas, si la columna contiene cuatro (4) valores   numéricos y cuatro (4) valores de texto, el proveedor devuelve cuatro (4)   números y cuatro (4) valores nulos.

Por   tanto, si la columna contiene valores combinados, el único recurso es   almacenar los valores numéricos de esa columna como texto y volverlos a   convertir en números cuando sea necesario en la aplicación cliente utilizando   la función VAL de Visual Basic u otra equivalente.
 
  Para evitar este problema en los datos de sólo lectura, habilite la opción Modo   de importación utilizando el valor “IMEX=1” en la sección de   propiedades extendidas de la cadena de conexión. De este modo se exige la   opción del Registro ImportMixedTypes=Text. Sin embargo, tenga en cuenta que   las actualizaciones pueden dar resultados inesperados en este modo. Para   obtener información adicional acerca de este valor, haga clic en el número de   artículo siguiente para verlo en Microsoft Knowledge Base (en inglés):

194124

(http://support.microsoft.com/kb/194124/   )

PRB:   Los valores de Excel se devuelven como NULL cuando se utiliza OpenRecordset   de DAO

No   se puede abrir un libro protegido mediante contraseña
 
  Si el libro de Excel está protegido mediante contraseña, no puede abrirlo   para el acceso a datos, aunque proporcione la contraseña correcta con los   valores de configuración de conexión, a menos que el archivo de libro ya esté   abierto en la aplicación Microsoft Excel. Si lo intenta, se mostrará un mensaje   de error similar al siguiente:

No   se pudo descifrar el archivo.

Para   obtener información adicional, haga clic en el número de artículo siguiente   para verlo en Microsoft Knowledge Base:

211378

(http://support.microsoft.com/kb/211378/   )

XL2000:   Error “No se pudo descifrar el archivo” con un archivo protegido   por contraseña

Recuperar   y modificar datos de Excel con ADO

// En esta sección se explican dos aspectos del trabajo con los datos   de Excel:

Cómo   se seleccionan datos, y

Cómo   se modifican datos

Cómo   se seleccionan datos

// Hay varias formas de seleccionar datos. Puede hacer lo siguiente:

Seleccionar   datos de Excel mediante código.

Seleccionar   datos de Excel mediante el control de datos ADO.

Seleccionar   datos de Excel mediante comandos del entorno de datos.

Seleccionar   datos de Excel mediante código

// Los datos de Excel pueden estar contenidos en uno de los siguientes   elementos del libro:

Una   hoja de cálculo completa.

Un   rango con nombre de celdas en una hoja de cálculo.

Un   rango sin nombre de celdas en una hoja de cálculo.

Cómo   se especifica una hoja de cálculo
 
  Para especificar una hoja de cálculo como origen del registro, utilice el   nombre de la hoja seguido de un signo de dólar e inclúyalo entre corchetes.   Por ejemplo:

            strQuery = “SELECT * FROM   [Sheet1$]”

                                              

También   puede delimitar el nombre de la hoja de cálculo con el carácter de comilla   simple inclinada (`) que se encuentra en el teclado debajo de la tilde (~).   Por ejemplo:

            strQuery = “SELECT * FROM   `Sheet1$`”

                                              

Microsoft   prefiere que se utilicen corchetes, que es la convención vigente desde hace   mucho tiempo para nombres de objetos problemáticos de bases de datos.
 
  Si omite el signo de dólar y los corchetes, o sólo el signo de dólar,   aparecerá un mensaje de error similar al siguiente:

…   el motor de bases de datos Jet no pudo encontrar el objeto especificado

Si   utiliza el signo de dólar pero omite los corchetes, verá un mensaje de error   similar al siguiente:

Error   de sintaxis en cláusula FROM.

Si   intenta utilizar comillas simples ordinarias, aparecerá un mensaje de error   similar al siguiente:

Error   de sintaxis en consulta. Cláusula de consulta incompleta.

Cómo   se especifica un rango con nombre
 
  Para especificar un rango con nombre de celdas como origen del registro, sólo   tiene que utilizar el nombre definido. Por ejemplo:

            strQuery = “SELECT * FROM   MyRange”

                                              

Especificar   un rango sin nombre
 
  Para especificar un rango sin nombre de celdas como origen del registro,   anexe la notación de filas/columnas de Excel estándar al final del nombre de   la hoja dentro de los corchetes. Por ejemplo:

            strQuery = “SELECT * FROM   [Sheet1$A1:B10]”

                                              

Precaución   sobre la especificación de hojas de cálculo: El proveedor supone que la tabla   de datos empieza en la celda que no esté en blanco situada en el extremo   superior izquierda de la hoja de cálculo especificada. Es decir, no hay   ningún problema para que la tabla de datos empiece en la celda   correspondiente a la fila 3 y la columna C. Sin embargo, por ejemplo, no   puede escribir el título de una hoja de cálculo encima y a la izquierda de   los datos de la celda A1.
 
  Precaución sobre la especificación de rangos: Cuando especifica una hoja de   cálculo como origen del registro, el proveedor agrega nuevos registros debajo   de los existentes en la hoja de cálculo según lo permita el espacio. Cuando   especifica un rango (con o sin nombre), Jet también agrega nuevos registros   debajo de los existentes en el rango según lo permita el espacio. Sin   embargo, si efectúa una nueva consulta sobre el rango original, el conjunto   de registros resultante no incluye los registros que se acaban de agregar   fuera del rango.
 
  Con las versiones de MDAC anteriores a la versión 2.5, cuando especifica un   rango con nombre, no puede agregar nuevos registros fuera de los límites   definidos del rango; si lo intenta, aparecerá un mensaje de error similar al   siguiente:

No   se puede expandir el rango con nombre.

Seleccionar   datos de Excel mediante el control de datos ADO

// Después de especificar los valores de configuración de conexión para   el origen de datos de Excel en la ficha General del cuadro de diálogo Propiedades   de ADODC, haga clic en la ficha OrigenDelRegistro. Si elige CommandType de   adCmdText, puede especificar una consulta SELECT en el cuadro de diálogoTexto   del comando con la sintaxis descrita anteriormente. Si elige CommandType de   adCmdTable, y está utilizando el proveedor Jet, la lista desplegable muestra   los rangos con nombre y los nombres de hoja de cálculo que están disponibles   en el libro seleccionado, apareciendo al principio de la lista los rangos con   nombre.
 
  Este cuadro de diálogo anexa correctamente el signo de dólar a los nombres de   hoja de cálculo, pero no anexa los corchetes necesarios. Por tanto, si sólo   selecciona un nombre de hoja de cálculo y hace clic en Aceptar, recibirá más   tarde un mensaje de error similar al siguiente:

Error   de sintaxis en cláusula FROM.

Debe   agregar manualmente los corchetes al principio y al final del nombre de hoja   de cálculo. Este cuadro combinado sí permite efectuar modificaciones. Si está   utilizando el proveedor ODBC, sólo verá los rangos con nombre incluidos en   esta lista desplegable. No obstante, puede escribir manualmente un nombre de   hoja de cálculo con los delimitadores apropiados.

Seleccionar   datos de Excel mediante comandos del entorno de datos

// Después de configurar la conexión del entorno de datos para el   origen de datos de Excel, cree un nuevo objeto Command. Si elige un Origen de   datos de Instrucción SQL, puede especificar una consulta en el cuadro de   texto utilizando la sintaxis descrita anteriormente. Si elige un Origen de   datos de Objeto de base de datos, seleccione Tabla en la primera lista   desplegable, y si está utilizando el proveedor Jet, la lista desplegable   muestra los rangos con nombre y los nombres de hoja de cálculo que están   disponibles en el libro seleccionado, apareciendo al principio de la lista   los rangos con nombre. Si elige un nombre de hoja de cálculo en esta   ubicación, no necesita agregar corchetes al principio y al final del nombre   de hoja de cálculo manualmente, como sucede en el caso del control de datos   ADO. Si está utilizando el proveedor ODBC, sólo verá los rangos con nombre   incluidos en esta lista desplegable. No obstante, puede escribir manualmente   un nombre de hoja de cálculo.

Cómo   se cambian datos de Excel: Modificar, Agregar y Eliminar

// Modificar
 
  Puede modificar datos de Excel con los métodos habituales de ADO. Los campos   de conjunto de registros que se corresponden con celdas de la hoja de cálculo   de Excel que contienen fórmulas de Excel (que empiezan por “=”) son   de sólo lectura y no se pueden modificar. Recuerde que una conexión ODBC a   Excel es de sólo lectura de forma predeterminada, a no ser que especifique lo   contrario en los valores de configuración de conexión. Consulte el apartado   “Uso del proveedor de Microsoft OLE DB para controladores ODBC” más   arriba.
 
  Agregar
 
  Puede agregar registros al origen del registro de Excel según lo permita el   espacio. Sin embargo, si agrega nuevos registros fuera del rango que   especificó originalmente, estos registros no son visibles si efectúa una   nueva consulta sobre la especificación del rango original. Consulte el   apartado “Precaución sobre la especificación de rangos” más arriba.
 
  En algunos casos, cuando utiliza los métodos AddNew y Update del objeto Recordset   de ADO para insertar nuevas filas de datos en una tabla de Excel, es posible   que ADO inserte los valores de datos en columnas inapropiadas de Excel. Para   obtener información adicional al respecto, haga clic en el número de artículo   siguiente para verlo en Microsoft Knowledge Base:

314763

(http://support.microsoft.com/kb/314763/   )

REVISIÓN:   ADO inserta datos en columnas erróneas en Excel

Eliminar
 
  Hay más limitaciones para eliminar datos de Excel que para eliminarlos de un   origen de datos relacional. En una base de datos relacional, “fila”   no tiene significado ni existe si no está asociada a “registro”; en   una hoja de cálculo de Excel, esto no es verdad. Puede eliminar valores en   campos (celdas). Sin embargo, no puede realizar las siguientes acciones:

Eliminar   un registro completo de una vez; si lo intenta, aparecerá un mensaje de error   similar al siguiente:

Este   ISAM no admite la eliminación de datos de una tabla vinculada.

Sólo   puede eliminar un registro si borra el contenido de cada campo individual.

Eliminar   el valor de una celda que contiene una fórmula de Excel; si lo intenta,   aparecerá un mensaje de error similar al siguiente:

Operación   no permitida en este contexto.

No   puede eliminar las filas vacías de la hoja de cálculo donde estaban ubicados   los datos eliminados, y el conjunto de registros continuará mostrando los   registros vacíos correspondientes a estas filas vacías.

Precaución   sobre la modificación de datos de Excel con ADO: Cuando inserta datos de   texto en Excel con ADO, el valor de texto va precedido de un carácter de   comilla simple. Esto puede ocasionar problemas posteriormente al trabajar con   los datos nuevos.

Recuperar   la estructura del origen de datos (metadatos) de Excel

// Puede recuperar datos relativos a la estructura del origen de datos   de Excel (tablas y campos) con ADO. Los resultados difieren ligeramente entre   los dos proveedores OLE DB, aunque ambos devuelven al menos el mismo número   reducido de campos útiles de información. Estos metadatos se pueden recuperar   con el método OpenSchema del objeto Connection de ADO, que devuelve un objeto   Recordset de ADO. También puede utilizar para este fin la biblioteca de   Microsoft ActiveX Data Objects Extensions for Data Definition Language and   Security (ADOX) que es más eficaz. Sin embargo, en el caso de un origen de   datos de Excel, donde “tabla” es una hoja de cálculo o un rango con   nombre, y “campo” es uno del número limitado de tipos de datos genéricos,   no es útil esta funcionalidad adicional.

Información   de tablas en consultas

// De los diversos objetos disponibles en una base de datos relacional   (tablas, vistas, procedimientos almacenados, etc.), un origen de datos de   Excel sólo expone equivalentes de tablas, que constan de las hojas de cálculo   y los rangos con nombre definidos en el libro de trabajo especificado. Los   rangos con nombre se tratan como “Tablas” y las hojas de cálculo   como “Tablas del sistema”, y no hay mucha información útil sobre   tablas que se pueda recuperar fuera de la propiedad “table_type”.   Puede solicitar una lista de las tablas disponibles en el libro utilizando el   código siguiente:

Set rs = cn.OpenSchema(adSchemaTables)

                                              

El   proveedor Jet devuelve un conjunto de registros con nueve (9) campos, de los   que sólo rellena cuatro (4):

table_name

table_type (“Tabla” o “Tabla del   sistema”)

date_created

date_modified

Los   dos campos de fecha para una tabla dada muestran siempre el mismo valor, que   parece ser la “fecha de la última modificación”. Es decir, el campo   “date_created” no es confiable.
 
  El proveedor ODBC también devuelve un conjunto de registros con nueve (9)   campos, de los que sólo rellena tres (3):

table_catalog,   carpeta donde se encuentra el libro.

table_name

table_type,   como se especifica más arriba.

De   acuerdo con la documentación de ADO, es posible recuperar una lista de hojas   de cálculo, por ejemplo, especificando los siguientes criterios adicionales   en el método OpenSchema:

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty,   Empty, Empty, “System Table”))

                                              

Desgraciadamente,   esto no funciona en el caso de un origen de datos de Excel con versiones de   MDAC posteriores a la versión 2.0, utilizando cualquiera de los dos proveedores.  

Información   de campos de consultas

// Cada uno de los campos (columnas) de un origen de datos de Excel   pertenece a uno de los siguientes tipos de datos:

numeric (ADO datatype 5, adDouble)

currency (ADO datatype 6, adCurrency)

logical o boolean (ADO datatype 11, adBoolean)

date   (ADO datatype 7, adDate, utilizando Jet; 135, adDBTimestamp, utilizando ODBC)

text   (tipo ADO ad…Char, como 202, adVarChar, 200, adVarWChar o similar)

El   campo numeric_precision de una columna numérica se devuelve siempre como 15   (que es la precisión máxima en Excel); el campo character_maximum_length de   una columna de texto se devuelve siempre como 255 (que es el ancho máximo de   presentación, pero no la longitud máxima, de texto en una columna de Excel).   No hay mucha información útil sobre campos que se pueda obtener fuera de la   propiedad data_type. Puede solicitar una lista de los campos disponibles en   una tabla utilizando el código siguiente:

Set rs = cn.OpenSchema(adSchemaTables, Array(Empty,   Empty, “TableName”, Empty))

                                              

El   proveedor Jet devuelve un conjunto de registros que contiene 28 campos, de   los que rellena ocho (8) para campos numéricos y nueve (9) para campos de   texto. Es probable que los campos más útiles sean los siguientes:

table_name

column_name

ordinal_position

data_type

El   proveedor ODBC devuelve un conjunto de registros que contiene 29 campos, de   los que rellena diez (10) para campos numéricos y once (11) para campos de   texto. Los campos más útiles son los que se han mencionado anteriormente.

Enumeración   de tablas y campos, y sus propiedades

// Se puede utilizar código de Visual Basic (como el del siguiente   ejemplo) para enumerar las tablas y las columnas en un origen de datos de   Excel y los campos disponibles de información sobre cada una de ellas. Este   ejemplo muestra los resultados en un cuadro de lista, List1, en el mismo   formulario.

 Dim cn As ADODB.Connection Dim   rsT As ADODB.Recordset Dim intTblCnt As Integer, intTblFlds As Integer Dim   strTbl As String Dim rsC As ADODB.Recordset Dim intColCnt As Integer,   intColFlds As Integer Dim strCol As String Dim t As Integer, c As Integer, f   As Integer Set cn = New ADODB.Connection With cn   .Provider = “Microsoft.Jet.OLEDB.4.0”    .ConnectionString = “Data   Source=” & App.Path & _ “\ExcelSrc.xls;Extended   Properties=Excel 8.0;”      ‘.Provider   = “MSDASQL”             ‘.ConnectionString   = “Driver={Microsoft Excel Driver (*.xls)};” & _   “DBQ=” & App.Path & “\ExcelSrc.xls; ” .CursorLocation = adUseClient         .Open End With Set rsT =   cn.OpenSchema(adSchemaTables) intTblCnt = rsT.RecordCount intTblFlds =   rsT.Fields.Count List1.AddItem “Tables:       ”   & intTblCnt List1.AddItem “——————–” For t = 1 To   intTblCnt         strTbl =   rsT.Fields(“TABLE_NAME”).Value List1.AddItem   vbTab & “Table #” & t & “:      ” & strTbl         List1.AddItem   vbTab & “——————–”             For   f = 0 To intTblFlds – 1                  List1.AddItem   vbTab & rsT.Fields(f).Name & _ vbTab & rsT.Fields(f).Value        Next    List1.AddItem   “——————–”        Set   rsC = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, strTbl, Empty))             intColCnt = rsC.RecordCount           intColFlds = rsC.Fields.Count            For c = 1 To intColCnt                     strCol =   rsC.Fields(“COLUMN_NAME”).Value                    List1.AddItem vbTab & vbTab   & “Column #” & c & “: ” & strCol                 List1.AddItem   vbTab & vbTab & “——————–”              For f = 0 To intColFlds – 1                          List1.AddItem vbTab   & vbTab & rsC.Fields(f).Name & _ vbTab & rsC.Fields(f).Value              Next                List1.AddItem vbTab & vbTab &   “——————–”                         rsC.MoveNext                         Next                rsC.Close                         List1.AddItem   “——————–”                    rsT.MoveNext   Next rsT.Close cn.Close                                       

Uso   de la ventana Vista de datos

// Si crea un vínculo de datos a un origen de datos de Excel en la   ventana Vista de datos de Visual Basic, esta ventana muestra la misma   información que puede recuperar mediante programación de la forma descrita   anteriormente. En concreto, observe que el proveedor Jet enumera las hojas de   cálculo y los rangos con nombre debajo de “Tablas”, donde el   proveedor ODBC sólo muestra rangos con nombre. Si está utilizando el   proveedor ODBC y no ha definido rangos con nombre, la lista   “Tablas” quedará vacía.

Limitaciones   de Excel

// El uso de Excel como origen de datos está constreñido por las   limitaciones internas de los libros y las hojas de cálculo de Excel. Algunas   de estas limitaciones son las siguientes:

Tamaño   de la hoja de cálculo: 65.536 filas por 256 columnas

Contenido   en una celda (texto): 32.767 caracteres

Hojas   de un libro: cantidad limitada por la memoria disponible

Nombres   de un libro: cantidad limitada por la memoria disponible

           Referencias

// Para obtener información adicional sobre cómo se utiliza ADO.NET   para recuperar y modificar registros en un libro de Excel con Visual Basic   .NET, haga clic en el número de artículo siguiente para verlo en Microsoft   Knowledge Base:

316934

(http://support.microsoft.com/kb/316934/   )

Cómo   utilizar ADO.NET para recuperar y modificar registros en un libro de Excel   con Visual Basic .NET

Para   obtener información adicional, haga clic en los números de artículo   siguientes para verlos en Microsoft Knowledge Base:

295646

(http://support.microsoft.com/kb/295646/   )

Cómo   transferir datos desde un origen de datos ADO a Excel con ADO

246335

(http://support.microsoft.com/kb/246335/   )

Cómo   transferir datos desde un conjunto de registros ADO a Excel con   Automatización

247412

(http://support.microsoft.com/kb/247412/   )

INFO:   Métodos para transferir datos a Excel desde Visual Basic

278973

(http://support.microsoft.com/kb/278973/   )

EJEMPLO:   ExcelADO muestra cómo usar ADO para leer y escribir datos en libros de Excel

318373

Cómo   recuperar metadatos de Excel utilizando el método GetOleDbSchemaTable en   Visual Basic .NET

Para   obtener más información al respecto, consulte el siguiente curso de Microsoft   Training & Certification:

Microsoft Corporation 1301 Mastering Office 2000 Solution Development

(http://www.microsoft.com/spain/formacion/default.mspx)  

    // Id. de artículo: 257819 – Última revisión: viernes, 17 de febrero de   2006 – Versión: 4.3

La   información de este artículo se refiere a:

Microsoft Excel 2000 Standard Edition

Microsoft Visual Basic 6.0 Edición de aprendizaje

Microsoft Visual Basic 6.0 Professional Edition

Microsoft Visual Basic 6.0 Edición empresarial

Service Pack 3 de Microsoft Visual Basic 6.0   Enterprise Edition

Microsoft Visual Basic for Applications 6.0

Microsoft ActiveX Data Objects 2.1

Microsoft ActiveX Data Objects 2.1 Service Pack 1

Microsoft ActiveX Data Objects 2.1 Service Pack 2

Microsoft ActiveX Data Objects 2.5

Microsoft Data Access Components 2.1

Microsoft Data Access Components 2.1 Service Pack 1

Microsoft Data Access Components 2.1 Service Pack 2

Microsoft Data Access Components 2.5

Microsoft Excel 2002 Standard Edition

Microsoft Excel 97 Standard Edition

Microsoft Excel 95 Standard Edition

   Palabras     clave:       kbhowto     kbiisam KB257819   

http://support.microsoft.com/kb/318373

   

                      

   

   

                      

   

   

                      

   

 

 

 

   

                     

   

   

                      

   

 

 

   

                     

   

   

                     

   

   

                      

   

   

                      

   

   

                      

   

 

¡

 

Seleccione   idioma(الشرق الاوسط (العربيةBrasil   (Português)Česká   republika (Čeština)

Deutschland (Deutsch)

France   (Français)

Indonesia (Bahasa Indonesia)

Italia   (Italiano)

Nederland (Nederlands)

Portugal   (Português)

Türkiye (Türkçe)

United   States (English)

Việt Nam   (Tiếng Việt)

Ελλάδα (Ελληνικά)

Россия (Русский)

Україна (Україньска)

भारत (हिंदी)

ไทย (ไทย)

대한민국 (한국어)

中国 (简体中文)

台灣 (繁體中文)

日本 (日本語)

                   

// // // // <![CDATA[
/* banner display settings */

/* Office student promotion*/
var student = ‘

‘;

/* office */
var office = ‘

‘;
/*http://support.microsoft.com/library/images/support/es-es/Office_2010_165x240_Estudiantes_ES-ES.png*/
/*http://support.microsoft.com/library/images/support/es-es/Office_2010_165x240_ES-ES.png*/

/* windows 8 */
var windows8 = ‘

‘;

/* windows 7 */
var windows7 = ‘

‘;
/*http://support.microsoft.com/library/images/support/es-es/165x240_Win-7-Discount_ES.jpg*/

/* msstore */
var msstore = ‘

‘;

/* mac */
var mac = ‘

‘;

/* ie9 */
var ie9 = ‘

‘;

/* ie8 */
var ie8 = ‘

‘;

/* dc */
var dc = ‘

‘;

/* mse */
var mse = ‘

‘;

/* pinpoint */
var pinpoint = ‘

‘;

/* banner set settings */
var Browseinfobanner_ie10_win8 = office + windows8 + msstore + student;
var Browseinfobanner_ie9_rest_win8 = office + windows8 + msstore + student;
var Browseinfobanner_ie9_win7_vista = office + windows8 + msstore + student;
var Browseinfobanner_ie8_rest_win7_vista = ie9 + office + windows8 + msstore + student;
var Browseinfobanner_ie8_winxp = office + windows8 + msstore + student;
var Browseinfobanner_ie7_rest_winxp = ie8 + office + windows8 + msstore + student;
var Browseinfobanner_ie8_other_win = office + windows8 + msstore + student;
var Browseinfobanner_ie7_rest_other_win = ie8 + office + windows8 + msstore + student;
var Browseinfobanner_non_win = office + windows8 + msstore + student;
var Browseinfobanner_mac = mac; /* dynamic banner has been disabled for mac, only static banner – see below */

/* dyn. banner body */
var banner_header = ‘

      ‘;
    var banner_footer = ‘

‘;

/* end of dynamic banner content variables */

banner_init();
banner_init_mac();
// ]]>     

    

    

    

    

    

Previous Next

 

 

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s