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

Seleccione idioma Seleccione idioma
Id. de artículo: 257819 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

En esta página

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 Cómo usar ADOX con datos de Excel desde Visual Basic o desde VBA

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: 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.
  1. 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.
  2. 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.
  3. 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.
  4. 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).
.ConnectionString = "Data Source=C:\MyFolder\MyWorkbook.xls;" & _
"Extended Properties=" " Excel 8.0; HDR=No;" " "
				

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.
  1. En la ficha Proveedor, seleccione Proveedor OLE DB de Microsoft para controladores ODBC.
  2. 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.
  3. 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 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 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 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 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 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 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:
  1. 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.
  2. 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.
  3. 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 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 Cómo transferir datos desde un origen de datos ADO a Excel con ADO
246335 Cómo transferir datos desde un conjunto de registros ADO a Excel con Automatización
247412 INFO: Métodos para transferir datos a Excel desde Visual Basic
278973 EJEMPLO: ExcelADO muestra cómo usar ADO para leer y escribir datos en libros de Excel
318373Có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

Propiedades

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

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com