Trabajar con ADO, DAO y Excel |
|||
Por Enrique Martínez Montejo |
|||
Última revisión: 15/08/2004 |
|||
3. Los parámetros IMEX y MAXSCANROWSA veces puede ocurrir que una columna de nuestra hoja de cálculo contenga datos numéricos y de texto entremezclados, lo que pueda dar lugar a que se devuelvan valores Null cuando intentemos exportar los datos contenidos en dicha columna. El problema está provocado por una limitación del driver ISAM de Excel, ya que en estos casos, el mismo no es capaz de determinar el tipo de dato que contiene la columna, por lo que retornará un valor Null por cada valor que no sea del tipo de dato por defecto de la columna. El ISAM de Excel determina el tipo de dato de una columna examinando los valores actuales de las primeras filas, en concreto las 8 primeras filas, eligiendo para ello el tipo de dato que representa la mayoría de los valores probados. El valor de las filas escaneadas puede modificarse mediante la inclusión del parámetro MaxScanRows en las propiedades extendidas de la cadena de conexión. Puede especificar un valor entero comprendido entre 1 y 16 filas, o puede especificar cero (0) para forzar a escanear todas las filas existentes:
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ Si está utilizando el driver ODBC con un nombre de origen de datos (DSN), deberá modificar el valor Rows to Scan en el cuadro de diálogo para la configuración del origen de datos. Sin embargo, debido a un bug del driver ODBC, especificar el parámetro Rows to Scan (MaxScanRows) no tiene ningún efecto. En otras palabras, el driver ODBC de Excel (MDAC 2.1 y superiores) siempre escanea las primeras ocho (8) filas del origen de datos especificado para determinar el tipo de dato que contiene la columna. Para más información consulte el siguiente artículo de Microsoft Knowledge Base: XL97: Data Truncated to 255 Characters with Excel ODBC Driver Pero lo anteriormente comentado, puede que no resulte suficiente para obtener todos los datos de la columna, por tanto, y para prevenir que se devuelvan valores Null, no nos va a quedar más remedio que añadir en las propiedades extendidas de la cadena de conexión, el parámetro IMEX=1, sobre todo si vamos a utilizar el método OpenRecordset de la biblioteca de datos DAO: Set db = OpenDatabase("C:\Libro1.xls", False, True, "Excel 8.0; HDR=NO; IMEX=1;") El establecer dicho valor obliga al driver a utilizar el modo de Importación, de esta forma el valor ImportMixedTypes=Text, contenido en la clave del registro de Windows \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel, será advertido, forzando de esta manera a que los tipos de datos entremezclados se conviertan a texto. Para obtener un resultado fiable, quizás haya también que modificar el valor TypeGuessRows=8, existente en la misma clave del registro anteriormente mencionada. Como se ha dicho anteriormente, el driver del ISAM comprueba por defecto las primeras ocho filas para determinar el tipo de dato que contiene la columna. Si del examen de dichas filas se comprueba que todos son valores numéricos, de nada sirve establecer IMEX=1 ya que no convertirá a Texto el tipo de dato por defecto. Deberá ser cuidadoso para no utilizar indiscriminadamente el valor IMEX=1. Este es el valor del modo de IMPORTACIÓN, por lo que los resultados obtenidos pueden ser impredecibles si intenta añadir o actualizar datos utilizando éste modo. Los posibles valores que puede contener IMEX son:
Con un ejemplo se entenderá mejor la explicación anterior. Imagine que tenemos un rango de celdas con nombre con los siguientes datos:
En las distintas columnas existen tipos de datos diferentes, con la alineación horizontal característica de Excel según el tipo de dato que contenga la celda. Si no se utiliza el parámetro IMEX=1, estos serían los resultados de la exportación representados en un control DataGrid:
Como se puede observar, existen celdas cuyo valor es Null, ya que no se han exportado ningún dato debido a la mezcla de tipos de datos contenidas en ellas. Los tipos de datos de los campos del objeto Recordset subyacente serán del tipo de campo mayoritario que contenga la columna de la hoja de cálculo de Excel. Quiero recalcar nuevamente, debido a su importancia, que si el usuario utiliza la biblioteca de datos DAO, sólo se devolverá la primera fila del rango de celdas. Por el contrario, si utilizamos el modo de Importación, estableciendo el parámetro IMEX=1, forzamos a que todo el contenido del rango de celdas se exporte como texto, tanto si utilizamos la biblioteca de datos ADO como la de DAO:
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
5. Limitaciones a tener en cuentaLos controladores ISAM del motor de base de datos Microsoft Jet son compatibles con las siguientes versiones de Microsoft Excel:
También hay otras operaciones que no se pueden realizar en las hojas de cálculo ni en los libros de trabajo de Microsoft Excel mediante los controladores ISAM correspondientes:
Siempre que se utilice Excel como origen de datos, hay que tener en cuenta las limitaciones internas que presentan los libros de trabajo y las hojas de cálculo de Excel. Estas limitaciones incluyen, pero no se limitan:
|
||||||||||||||||||||
6. Vinculación de hojas de cálculo de Microsoft Excel6.1. Vincular con ADOXLa biblioteca de ADO no permite vincular una hoja de cálculo Excel a una base de datos Access, para ello tenemos que ayudarnos de la biblioteca «Microsoft ADO Ext. 2.7 for DLL and Security», más conocida por ADOX, la cual es una extensión de los objetos y del modelo de programación de ADO. Por tanto, en nuestro proyecto no hay que olvidarse hacer referencia a la citada biblioteca. El siguiente ejemplo, muestra como vincular un rango con nombre de una hoja de cálculo Excel, a una base de datos Access 2000:
Public Sub LinkExcelSheetWithADO 6.2. Vincular con DAOPara vincular una hoja de cálculo de Microsoft Excel a una base de datos Access, hay que utilizar el método OpenDatabase para abrir la base de datos, crear un objeto TableDef y establecer las propiedades Connect y SourceTableName del objeto TableDef para indicar la hoja de cálculo que se desea vincular. En el siguiente ejemplo, vamos a vincular un rango de celdas, teniendo en cuenta que la primera fila de la hoja de cálculo se tratará como un registro de la tabla, no como un encabezado que contiene los nombres de los campos, debido a que el parámetro HDR de la cadena de conexión está establecido a No:
Public Sub LinkExcelSheetWithDAO |
||
7. Abrir una hoja de cálculo de Microsoft Excel7.1. Mediante ADO visualizando la información en un DataGridHay que tener en cuenta que con el ISAM «Excel 8.0» se puede abrir una hoja completa de un libro de trabajo de Microsoft Excel, así como un rango con nombre o un rango sin nombre de celdas. La versión del proveedor necesaria que hay que utilizar para abrir una hoja de cálculo Excel, es la versión del proveedor Jet 4.0; el proveedor Jet 3.5 no soporta los drivers ISAM de Jet. Si se especifica el proveedor Jet 3.51, en tiempo de ejecución se recibirá el siguiente mensaje de error: No se pudo encontrar el ISAM instalable. A continuación, y mediante ADO, vamos a escribir un procedimiento que mostrará en un control DataGrid el conjunto de registros devueltos dependiendo de la selección de datos efectuada:
Public Sub OpenExcelSheetWithADO (ByVal Option As Integer) 7.2. Mediante DAO mostrando los datos en un DBGridEn este ejemplo, vamos a utilizar un simple control Data para que automáticamente se llene el control DBGrid, por tanto, en tiempo de diseño es necesario establecer la propiedad DataSource del control DBGrid con el nombre de un control Data, ya que el control DBGrid no admite automatización.
Public Sub OpenExcelSheetWithDAO (ByVal Option As Integer) 7.3. Configurar un control de datos de ADO en tiempo de ejecuciónPersonalmente opino que la mejor manera de configurar un control determinado es establecer sus propiedades en tiempo de ejecución, de esta forma obtendremos un código limpio y fácil de mantener con el tiempo, sobre todo si nuestro proyecto es compartido por varias personas. Por tanto, salvo que alguna propiedad necesariamente tenga que establecerse en tiempo de diseño, lo mejor es utilizar el método Form_Load de nuestro formulario para modificar los valores por defecto del control que vamos a utilizar. De esta forma, podremos revisar el código y hacer las modificaciones oportunas sin necesidad de recurrir a la ventana de propiedades del entorno de desarrollo de Visual Basic. El configurar un control de datos de ADO en tiempo de ejecución requiere quizás un poco más de tiempo debido en parte a la escritura del propio código, pero ese tiempo añadido se verá recompensado con creces cuando deseemos modificar algún valor o necesitemos saber con exactitud donde se produce un error en concreto. En el siguiente ejemplo vamos a utilizar un control de datos de ADO, dos controles TextBox y un control DataGrid, para ver los datos de un rango de celdas sin nombre de un libro de trabajo de Excel, donde la primera fila incluye los nombres de las columnas, configurando los controles enlazados a datos en tiempo de ejecución, por lo que sólo necesitará añadir dichos controles al formulario y pegar el siguiente código en la sección Declaraciones:
Private Sub Form_Load() Si han ejecutado el código, habrán podido observar lo fácil y limpio que ha resultado configurar todo en tiempo de ejecución, sin necesidad de utilizar para nada la ventana de propiedades. De esta forma, si queremos abrir otro libro de trabajo, u otro rango de celdas, con sólo cambiar los valores de Data Source o RecordSource, respectivamente, podemos reutilizar el código. 7.4. Conocer el nombre de las columnas de ExcelSi tenemos una hoja de cálculo, o un rango de celdas, cuya primera fila sabemos de antemano que contiene el nombre de las columnas, podemos conocer el nombre de las mismas abriendo un objeto Recordset y recorriendo la colección Fields de dicho objeto. El siguiente ejemplo utiiza la biblioteca de ADO, fácilmente adaptable para los usuarios de DAO, para conocer el nombre de las columnas de un rango de celdas sin nombre:
Dim cnn As ADODB.Connection En éste caso sí es importante utilizar y establecer a Yes el parámetro HDR en la cadena de conexión, porque de lo contrario obtendríamos como nombres de campos F1, F2, ... Fn, en el supuesto de que indicáramos el valor No, o bien, el valor de la clave FirstRowHasNames del registro de Windows esté establecida a 00. |
||
8. Cómo crear un nuevo libro de trabajo de ExcelSin lugar a dudas, la forma más fácil y sencilla de crear un nuevo libro de trabajo es a través del propio programa Microsoft Excel. Pero puede ser que el usuario final de nuestra aplicación no disponga del mencionado programa instalado en su sistema, lo que será un impedimento si en nuestro programa tenemos hecha una referencia a la biblioteca ActiveX Microsoft Excel x.x Object Library, por lo que no podremos utilizar la técnica que se conoce como automatización para crear un nuevo libro de trabajo, con sus correspondientes hojas de cálculo. Si deseamos crear una aplicación que interactúe con Excel, con independencia de que el usuario final disponga o no del mencionado programa, necesitaremos nuevamente hacer uso de los componentes de acceso a datos, y utilizar el ISAM de Excel del motor Microsoft Jet, para crear nuevos libros y hojas de trabajo, por lo que lo único que necesitamos es que el cliente final tenga instalado los componentes de Microsoft Jet, incluida la biblioteca del ISAM de Excel, que serán los archivos msexcl40.dll (para la versión del motor Jet 4.0), o msexcl35.dll (para la versión 3.51 de dicho motor). Si vamos a utilizar la biblioteca de ADO, también necesitará tener instalado una versión del MDAC. Mediante las siguiente técnicas que se detallan a continuación, podemos crear un nuevo libro de trabajo y una hoja de cálculo, o bien, podemos añadir nuevas hojas de cálculo a un libro ya existente. Lo curioso de utilizar ambas técnicas es que, aparte de crear una nueva hoja de trabajo, también creará un nuevo rango con el mismo nombre que hayamos asignado a la hoja de trabajo. 8.1. Mediante ADO o DAO utilizando la instrucción CREATE TABLECon el estándar del Lenguaje de Consulta Estructurado (SQL) del motor de base de datos Microsoft Jet, es posible crear nuevas tablas, en nuestro caso, libros de trabajo de Excel, utilizando para ello la instrucción CREATE TABLE del lenguaje de manipulación de datos (DDL) de SQL. Simplemente necesitaremos definir la cadena para crear la tabla, la cuál incluirá el nombre de la tabla (hoja de cálculo) y el nombre y tipo de los campos (columnas) que conformarán la misma. Una vez que la tengamos definida, sólo basta ejecutar el método Execute del objeto Connection (si utilizamos la biblioteca de ADO), o del objeto Database (si trabajamos con la biblioteca de DAO), siempre y cuando dichos objetos se encuentren previamente abiertos. En el siguiente ejemplo, vamos a crear un nuevo libro de trabajo de Excel (Libro10.xls), el cuál creará una nueva hoja de cálculo, y un rango con idéntico nombre, con las columnas necesarias para introducir los datos de nuestros clientes. Mediante la biblioteca de ADO sería así: Dim cnn As ADODB.Connection El inconveniente que tiene éste método para crear un nuevo libro de trabajo, o añadir una nueva hoja de cálculo en un libro ya existente, es que, aunque especifiquemos el tipo de datos, Excel hará caso omiso a los mismos, por lo que todas las celdas tendrán el formato General. Por último indicar que si la hoja de cálculo ya existe, se producirá un error interceptable. 8.2. Mediante una consulta SQL de creación de tablaUna de las cosas que más me fascina del Lenguaje de Consulta Estructurado (SQL), es la facilidad que tiene para crear nuevas tablas (en este caso, hojas de cálculo), mediante una variación de la instrucción SELECT. Simplemente basta con agregarle la cláusula INTO para obtener, de manera rápida y sencilla, una nueva hoja de cálculo con los datos de una tabla de Access o de un archivo de texto delimitado, por poner unos ejemplos. Es lo que se conoce con el nombre de consulta de creación de tabla. El ejemplo utiliza ADO para exportar una tabla de una base de datos Access, aunque perfectamente se puede adaptar a DAO, utilizando para ello la consulta SQL con el método Execute de un objeto Database abierto:
Dim sExcelFileName As String Al utilizar ésta forma de crear una hoja de cálculo, todas las celdas también tendrán el formato General, a excepción de los campos que tengan un tipo de dato Fecha, en los que Excel sí reconocerá dicho formato de celda. 8.3. Utilizando conjuntamente las bibliotecas de ADO y ADOXDisponiendo de una conexión abierta mediante un objeto Connection de la biblioteca de ADO, utilizando el proveedor Microsoft.Jet.OLEDB.4.0 podemos crear un nuevo libro de trabajo, el cual, necesariamente necesitará tener, como mínimo, una hoja de cálculo. El programador que tenga una cierta experiencia con la biblioteca de ADOX, conocerá que mediante el método Append de la colección Tables de un objeto Catalog, podemos crear nuevas tablas y añadirlas a un origen de datos, pero aquí, en lugar de añadirlas a una base de datos, vamos a crear un objeto Table para crear una nueva hoja de cálculo en nuestro libro de Excel.
Dim cnn As ADODB.Connection Reitero nuevamente que todas las celdas tendrán el formato General, aunque se indique un tipo de dato numérico o de fecha, debido a un error de conversión de tipos de datos en Excel. |
||
9. Importar una hoja de cálculo a una base de datos AccessEn el supuesto de que tengamos una base de datos activa, y queremos importar una tabla de otra base de datos externa (en éste caso, una hoja de cálculo de Excel), tendremos que establecer una conexión con la base de datos Activa y recurrir de nuevo a SQL para importar la tabla o rango de celdas desde Excel. En el siguiente ejemplo, vamos a utilizar la biblioteca de ADO, para importar a nuestra base de datos de Access un rango de celdas sin nombre de nuestro archivo de Excel:
Dim sTablaOrigen As String, sTablaDestino As String Nota importante: Pongo especial énfasis en las comillas simples para que el lector observe correctamente la sintaxis utilizada, ya que es sumamente importante incluirlas para delimitar la ruta de acceso al archivo y la versión del ISAM instalable que se va a utilizar, procurando dejar un espacio en blanco entre ambos parámetros. Ni que decir tiene que, al ejecutar el código, procure quitar todos los paréntesis incluidos en la variable sConnect. A continuación, vamos a importar a Access mediante la biblioteca de DAO, un rango de celdas con nombre, donde la primera fila no contiene los nombres de los campos, utilizando para ello una sintaxis diferente en la consulta SQL permitida también por la cláusula IN:
Dim sTablaOrigen As String, sTablaDestino As String Nuevamente hago hincapié en el parámetro HDR, porque de especificarse en la cadena de conexión «HDR=No», los campos de la tabla importada tendrían por nombre F1, F2, ... Fn, y la primera fila de la hoja de cálculo, se convertiría en el primer registro de la tabla importada. Por último comentar que, al igual que se puede importar una hoja de cálculo completa, también se puede importar un rango con nombre, al igual que un rango de celdas sin nombre. |
||
10. Exportar los datos desde ExcelAl igual que en el apartado anterior, también se explicará en éste punto otra forma diferente de poder crear nuevas tablas en nuestra base de datos, pero con la diferencia que, si antes utilizábamos una conexión con la base de datos para ejecutar la consulta de creación de tabla, ahora vamos a establecer la conexión directamente con el libro de trabajo de Excel, para desde allí exportar una hoja de cálculo o un rango de celdas, utilizando para ello otra sintaxis distinta de las anteriores para la consulta SQL de creación de tabla. 10.1. A una base de datos AccessEn esta ocasión se trata de abrir una conexión con la hoja de cálculo de Excel y exportar los datos desde la misma hoja, creando una nueva tabla en una base de datos de Access. Mediante la biblioteca de ADO, vamos a exportar los datos de una hoja de cálculo:
Dim sTablaOrigen As String, sTablaDestino As String En ésta situación, un detalle que deberíamos de tener en cuenta sería la posibilidad de que la base de datos se encuentre protegida mediante una contraseña, por lo que necesariamente tendríamos que especificarla en la cadena de conexión con la base de datos externa, utilizando para ello el parámetro ;PWD, tal y como se muestra en el siguiente ejemplo. Mediante la biblioteca de DAO, sería así:
Dim sTablaOrigen As String, sTablaDestino As String 10.2. A una base de datos SQL ServerA continuación se explicará cómo exportar los datos de un libro de trabajo de Excel a una base de datos SQL Server, utilizando para ello el motor Microsoft Jet y una cadena de conexión ODBC en la sintaxis de la consulta SQL de creación de tabla. El siguiente ejemplo creará mediante la biblioteca de ADO, una nueva tabla (TablaExcel) en la base de datos SQL de ejemplo pubs, con los datos del rango con nombre (Rango1) existente en nuestro libro de trabajo de Excel: Dim cnn As ADODB.Connection Para más información sobre cómo importar datos a SQL Server desde Excel, consulte el siguiente artículo de la Knowledge Base: http://support.microsoft.com/default.aspx?scid=kb;EN-US;321686 |
||
11. Añadir registros a una tabla procedentes de una hoja de cálculo ExcelSi tenemos una tabla creada en Access y queremos añadir los datos existentes en una hoja de cálculo de Excel, podemos insertar los datos mediante la instrucción INSERT INTO, lo que en SQL se conoce como una consulta de datos añadidos o anexados, la cual añade los nuevos registros al final de la tabla o consulta ya existente. En las tablas origen y destino puede especificar una tabla o una consulta. Si especifica una consulta, el motor de base de datos Microsoft Jet añade los registros a cualquiera y a todas las tablas especificadas en la consulta. Si la tabla de destino contiene una clave principal, hay que asegurarse de que se añade un valor único y distinto de Null al campo o campos de la clave principal; de lo contrario, el motor de base de datos no añadirá los registros. También hay que tener en cuenta que, si añade registros a una tabla con un campo de tipo AutoNumérico y desea volver a numerar los registros añadidos, no incluya el campo AutoNumérico en la consulta. Incluya el campo Autonumérico en la consulta sólo si desea conservar los valores originales del campo. Por último recalcar que, si la tabla o consulta no existe en la base de datos de Access, no se podrá añadir registros, debido a la inexistencia de la misma, cosa que por otra parte, es bastante lógica. 11.1. Desde la propia base de datos Access activaSi tenemos una conexión abierta con una base de datos Access, que ya contiene la tabla o consulta donde queremos añadir los registros de la hoja de cálculo, solamente tendríamos que especificar la versión del ISAM a utilizar, el parámetro HDR y la ruta de la hoja de cálculo, así como el origen del rango que deseamos importar, en el parámetro DATABASE. Mediante ADO sería así:
Dim cnnActiva As ADODB.Connection En este supuesto, el parámetro HDR es sumamente importante, porque de especificar el valor No, podríamos tener problemas al añadir los registros, debido a que el motor Jet no sabría dónde insertar los mismos, aparte de que los nombres de las columnas de la hoja de cálculo deben coincidir con los nombres de los campos de la tabla. 11.2. Desde la propia hoja de cálculo ExcelAl contrario que en el punto anterior, a continuación voy a mostrar cómo se añadirían los registros en la tabla de Access, pero desde una conexión con la hoja de cálculo de Excel, de esta forma se observará la utilización de la cláusula IN de SQL, la cual identifica las tablas de cualquier base de datos externa a la que el motor de base de datos Microsoft Jet se puede conectar, como una hoja de cálculo Excel, otros formatos de bases de datos como dBASE o Paradox, así como una base de datos externa de Access. El turno ahora es para DAO:
Dim sTablaOrigen As String, sTablaDestino As String 11.3. Exportar datos de una hoja de cálculo Excel a otra hoja de cálculoEl potencial del ISAM de Excel, unido con el Lenguaje de Consulta Estructurado, nos sirve también para poder añadir filas en una hoja de cálculo con los datos pertenecientes a otra hoja de cálculo, lo mismo que si los registros los agregáramos a una tabla de una base de datos de Access. Simplemente estableceríamos una conexión con la hoja de cálculo, cuyos datos queremos exportar, y pasaríamos en la consulta SQL la versión del ISAM a utilizar y los parámetros HDR y DATABASE. En el siguiente ejemplo, vamos a utilizar la biblioteca de ADO para exportar los datos contenidos en un rango de celdas con nombre, a otra hoja de cálculo externa, indicándole que la primera fila, no contiene los nombres de las columnas. Los registros se insertarán en la primera fila libre de la hoja de cálculo:
Dim sTablaOrigen As String, sTablaDestino As String 11.4. Cómo insertar una fila de datos en una hoja de cálculoEn los ejemplos anteriores hemos utilizado la instrucción INSERT INTO para ejecutar una consulta de datos anexados con múltiples registros. En éste apartado aprenderemos a insertar una nueva fila de datos en nuestra hoja de cálculo, o en un rango de celdas ya existente. Para ello utilizaremos la sintaxis para ejecutar una consulta de datos anexados sobre un único registro o fila, la cuál es la siguiente:
INSERT INTO Destino (Campo1, Campo2, ...,
CampoN) En este caso, hay que especificar el nombre y el valor para cada columna de la fila. Si no se especifica alguna columna no se insertará ningún valor en dichas columnas. Las filas se añadirán al final de la última fila de la hoja de cálculo o del rango de celdas especificado. La instrucción INSERT INTO generalmente se utiliza con el método Execute, tanto del objeto Connection de la biblioteca de ADO, como del objeto Database correspondiente a la biblioteca de DAO. Mediante la biblioteca de ADO, el siguiente ejemplo añadirá una nueva fila a nuestra hoja de cálculo, compuesto por los valores de tres columnas.
Dim cnn As ADODB.Connection Si la primera fila de la hoja de cálculo o del rango de celdas contiene el nombre de las columnas, necesariamente tendremos que especificarlo afirmativamente en el parámetro HDR de la cadena de conexión, porque de lo contrario, obtendremos un error interceptable. La primera vez que ejecutemos éste código con la biblioteca de ADO, o con el driver ODBC de Excel, desde el entorno de desarrollo de Visual Basic, puede que nos aparezca el siguiente mensaje de error: El sistema operativo no admite la secuencia de ordenación seleccionada. Dicho error se debe a un problema del IDE de Visual Basic con SP3 o superior, tal y como se detalla en el siguiente artículo de la Base del Conocimiento: No nos debe de preocupar mucho el error, porque, aparte de que sólo se produce la primera vez que ejecutamos la aplicación en el entorno de desarrollo, una vez que nuestra aplicación esté compilada, no volverá a producirse el citado error. Cuando se produzca el error en el entorno de desarrollo, basta con pulsar el botón Depurar y posteriormente reanudar la ejecución pulsando la tecla F5. En sucesivas ejecuciones no volverá a producirse el error. Cuando se utiliza ADO para insertar o modificar datos en una hoja de cálculo de Excel que no fué creada o modificada usando ADOX, podemos tener problemas a la hora de insertar datos numéricos, ya que éstos aparecerán alineados a la izquierda precedida de una comilla simple. En Excel 2002, los números están marcados con la etiqueta inteligente de advertencia "número se almacenó como texto". Esto puede causar un problema cuando se trabaje posteriormente con los datos, si especialmente el dato se almacenó en Excel para un análisis numérico, tal y como se puede comprobar en el siguiente artículo de la KB: Usar ADOX con datos de Excel desde Visual Basic o VBA A modo de conclusión, ADOX funciona mejor con Excel cuando se crean a la misma vez la hoja de cálculo y sus columnas. Por el contrario, ADO trabaja mejor con los datos de Excel cuando dichos datos se guardaron en la hoja de cálculo que fue creada utilizando ADOX. |
||
12. Actualizando datosTambién nos puede resultar necesario actualizar los registros contenidos en una tabla, ya se encuentren éstos en una base de datos de Access o en una hoja de cálculo de un libro de trabajo de Excel. Para ello ejecutaremos una consulta de actualización mediante la utilización de la instrucción UPDATE, correspondiente al lenguaje de manipulación de datos de SQL, la cual cambia los valores de los campos de una tabla especificada basándose en un criterio específico, y cuya sintaxis es la siguiente:
UPDATE Tabla La instrucción UPDATE generalmente se utiliza con el método Execute, tanto del objeto Connection de la biblioteca de ADO, como del objeto Database correspondiente a la biblioteca de DAO. Es importante resaltar que la instrucción UPDATE no genera ningún conjunto de resultados. Para conocer los datos que se verán afectados por la actualización, previamente habrá que ejecutarse una consulta de selección que utilice los mismos criterios que la consulta de actualización. 12.1. Actualizar todos los registros de un rango de celdas de la hoja de cálculoSi en la sintaxis de la instrucción UPDATE se suprime la cláusula WHERE, todos los registros de la tabla especificada se actualizarán. Suponiendo que tenemos un rango de celdas con nombre llamado Empleados, el salario de todos los trabajadores se verá incrementado en un 3,50%. Mediante la biblioteca ADO sería de la siguiente forma:
Dim sTablaDestino As String, sSQL As String Al igual que se ha especificado un rango de celdas con nombre, también se puede actualizar un conjunto de celdas con solo indicar el rango de las mismas: sTablaDestino = "[WorkSheet1$A1:Z6]" En el supuesto de que sólo quisieramos incrementar el salario de un trabajador en concreto, la sintaxis de la consulta SQL de actualización sería la siguiente: sSQL = "UPDATE " & sTablaDestino & " SET Salario = Salario + (Salario * 3.5/100) WHERE Empleado = 'José Pérez' " 12.2. Actualizar los datos de una columna individualmenteA diferencia del apartado anterior, donde se actualizaban todos los registros de acuerdo a un mismo valor común para todos ellos, también nos puede interesar actualizar los datos de una columna con los valores individuales que tenemos en otra columna de la misma hoja de cálculo, mediante la comparación de un mismo campo común a ambos rangos de celdas. Siguiendo con el mismo ejemplo anterior, vamos a actualizar los salarios individuales de cada uno de los empleados, con los datos que tenemos en un rango de nuestra hoja de cálculo:
Dim sTablaOrigen As String, sTablaDestino As String |
||
13. Establecer una conexión mediante el driver ODBC para ExcelCuando se establece una conexión a una hoja de cálculo mediante el conductor ODBC para Microsoft Excel, éste hace caso omiso del valor FirstRowHasNames establecido en la clave del registro de Windows, que como se ha visto en el punto segundo, es el encargado de establecer el valor por defecto que indica que la primera fila de la hoja de cálculo, se tomará como nombres de las columnas o campos de la tabla. Esto se debe a un bug tal y como reconoce Microsoft en el articulo de la Knowlege Base, y que se puede leer en la siguiente dirección: http://support.microsoft.com/default.aspx?scid=kb;en-us;288343 El problema radica en que el driver ODBC para Microsoft Excel asume por defecto que la primera fila de la hoja de cálculo contiene los nombres de las columnas (nombres del campo), cosa del todo incierta, porque perfectamente se puede tener una hoja de cálculo sin nombres de columnas. Si la primera fila de datos no contiene las cabeceras de las columnas o cualquier otras entradas, el resultado efectivo es que la primera fila de datos desaparecerá, como si de algún truco de magia se tratara, con independencia del valor que aparezca en el registro de Windows, porque el driver ODBC lo habrá tomado como los nombres de los campos. Como tampoco se puede utilizar el parámetro HDR en la cadena de conexión, tal y como sí lo permite el proveedor OLE DB, no queda más remedio que poner especial cuidado a la hora de manipular los datos devueltos por una consulta, o la apertura de un recordset, proveniente de una hoja de cálculo Excel. El siguiente ejemplo demuestra que de nada sirve utilizar el valor FirstRowHasNames en la cadena de conexión. Vamos a establecer una conexión mediante ODBC con un libro de trabajo Excel, abriendo un recordset con la hoja denominada WorkSheet1, y donde se establece que no deseamos utilizar la primera fila como nombre de los campos. Asegúrese que la primera fila de la hoja no figuren los nombres de las columnas:
Dim cnnExcel As ADODB.Connection Si abrimos la ventana Inmediato, se observará el enredo devuelto, ya que el driver ha asignado los nombres de los campos como ha querido; unas veces F1, otras veces, datos correspondientes a la primera fila de la hoja,... etc. Y como primer registro, ha devuelto el valor de la segunda fila de la hoja, por lo que los valores existentes en la primera fila, literalmente han desaparecido, porque simplemente, el driver entiende que son los nombres de los campos. Si queremos ver cierto orden, repítase el ejemplo, pero ésta vez, con una hoja de cálculo que tenga los nombres de las columnas en la primera fila. Se observará que de nada sirve establecer FirstRowHasNames a False o a True. |
||
14. Algunos artículos útiles de Microsoft Knowledge BaseLa siguiente relación de enlaces a Microsoft Knowledge Base, es por gentileza de Douglas Laudenschlager [MS]. Él fue quien me la proporcionó (en el grupo de noticias microsoft.public.es.ado), y al que le estoy agradecido por la información facilitada. De la amplia lista de artículos que me facilitó sobre el tema que versa el presente artículo, a continuación expongo los recursos y temas conocidos que utilizan la biblioteca de datos de ADO para conectarse con Microsoft Excel, aunque muchos de ellos se pueden adaptar perfectamente a la biblioteca de DAO. La mayoría de los artículos de la Knowledge Base que cito (por no decir todos), están en inglés. He traducido los títulos de los artículos a fin de facilitar al usuario hispanohablante una referencia más rápida al contenido de los mismos. En dichos artículos el usuario podrá encontrar una gran información sobre cómo manipular los datos contenidos en una hoja de cálculo Excel con la biblioteca Microsoft ActiveX Data Object. |
||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
NOTA: El contenido de éste artículo es una recopilación de las participaciones del autor en el grupo de noticias en español de Visual Basic (microsoft.public.es.vb), así como de la información disponible en los distintos artículos que la Microsoft Knowledge Base ofrece sobre el tema en cuestión, de los cuales se hace una referencia en el apartado número catorce. La información contenida en este artículo, así como el código fuente incluido en el mismo, se proporciona COMO ESTÁ, sin garantías de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en práctica, utilizar o ejecutar lo explicado, recomendado o sugerido en el presente artículo. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
This article is provided AS IS with no warranties, and confers no rights. You assume all risk for your use. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Enrique Martínez Montejo - 2003 | ||