trabajar con ado, dao y excel

Trabajar con ADO, DAO y Excel

http://www.mvp-access.es/softjaen/articulos/excel/ado_dao_excel.htm

Trabajar con ADO, DAO y ExcelPor Enrique Martnez Montejo SoftJan [MS MVP - VB]

ltima revisin: 15/08/2004

ndice

1. 2. 3. 4. 5. 6.

Utilizar objetos de acceso a datos con hojas de clculo de Excel El parmetro HDR y la supresin de los encabezados Los parmetros IMEX y MAXSCANROWS Cmo especificar los nombres de las hojas y rangos Limitaciones a tener en cuenta Vinculacin de hojas de clculo de Microsoft Excel 6.1. Vincular con ADOX 6.2. Vincular con DAO 7. Abrir una hoja de clculo de Microsoft Excel 7.1. Mediante ADO visualizando la informacin en un DataGrid 7.2. Mediante DAO mostrando los datos en un DBGrid 7.3. Configurar un control de datos de ADO en tiempo de ejecucin 7.4. Conocer el nombre de las columnas de Excel 8. Cmo crear un nuevo libro de trabajo de Excel 8.1. Mediante ADO o DAO utilizando la instruccin CREATE TABLE 8.2. Mediante una consulta SQL de creacin de tabla 8.3. Utilizando conjuntamente las bibliotecas de ADO y ADOX 9. Importar una hoja de clculo a una base de datos Access 10.Exportar los datos desde Excel 10.1. A una base de datos de Access 10.2. A una base de datos SQL Server 11.Aadir registros a una tabla procedentes de una hoja de clculo Excel 11.1. Desde la propia base de datos Access activa 11.2. Desde la propia hoja de clculo Excel 11.3. Exportar datos de una hoja de clculo Excel a otra hoja de clculo 11.4. Cmo insertar una fila de datos en una hoja de clculo 12.Actualizando datos 12.1. Actualizar todos los registros de un rango de celdas de la hoja de clculo 12.2. Actualizar los datos de una columna individualmente 13.Establecer una conexin mediante el driver ODBC para Excel 14.Algunos artculos tiles de Microsoft Knowledge Base

1. Utilizar objetos de acceso a datos con hojas de clculo de Excel Mediante los controladores ISAM (Mtodo de acceso secuencial indexado) instalables, podemos abrir y manipular la informacin contenida en una hoja de clculo Excel lo mismo que si se tratara de una tabla de una base de datos cualquiera.

1 de 26

12/11/2011 16:50



Antes de comenzar, quiero hacer hincapi en la necesidad de tener instalados previamente en nuestro sistema los controladores ISAM instalables (IISAM) para Microsoft Excel, porque de lo contrario obtendremos el mensaje de error "Imposible encontrar el ISAM instalable". Por tanto, si al instalar Visual Basic no se instalaron dichos controladores, ejecute el programa de instalacin de Visual Basic y seleccione los controladores apropiados para proceder a su instalacin. Durante el proceso de instalacin, el controlador IISAM escribe la informacin de configuracin en el Registro de Windows, estableciendo unos valores predeterminados que posteriormente podemos modificar su comportamiento utilizando para ello cualquiera de los mtodos siguientes: Utilizando el editor del Registro (no recomendado). Utilizando las funciones API de acceso al Registro, antes de establecer la conexin con el origen de datos (requiere el conocimiento de dichas funciones API). Mediante la cadena de conexin, en el momento de acceder a los datos. Es el mtodo recomendado debido a que no modificar ningn valor del registro, porque los valores establecidos en la cadena, slo sern vlidos para un archivo de datos en particular. Este es el mtodo que se utilizar a lo largo del presente artculo. Hay tres formas de acceder a los datos de un archivo de hoja de clculo o libro de trabajo de Miicrosoft Excel: vinculando la hoja de clculo o el rango de celdas; abriendo dichos datos directamente o importando dichos datos a una tabla de una base de datos u otro origen de datos, incluyendo otro archivo de Excel. Asimismo, existe la posibilidad de exportar los datos de una tabla u otro origen de datos, bien creando una nueva hoja de clculo, bien aadiendo los registros a una hoja ya existente. Por ltimo, indicar que en este artculo se utilizaran las dos bibliotecas de acceso a datos ms utilizadas con Visual Basic (DAO y ADO), para tener acceso a hojas de clculo y libros de trabajo de Microsoft Excel, utilizando solamente la versin del ISAM Excel 8.0, por ser el formato correspondiente a las ltimas versiones de Microsoft Excel.

2. El parmetro HDR y la supresin de los encabezados Al instalar los IISAM para Excel en nuestro sistema, por defecto se establece un valor que indica que la primera fila de la hoja de clculo se tomar como nombres de las columnas o campos de la tabla. Esto se debe al valor FirstRowHasNames de la clave del registro de Windows \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel, o la correspondiente al motor Jet 3.5, el cul toma el valor DWORD de 01. Si no queremos utilizar como nombres de campos los datos contenidos en la primera fila de la hoja de clculo, simplemente bastara con abrir el registro, y cambiar su valor a 00, utilizndose entonces como nombres de campos, F1, F2,.., Fn, que representaran al primer campo, segundo campo, ..., ltimo campo. Pero si se utiliza sta ltima opcin, hay que tener presente que los datos de la primera fila de la hoja de clculo pasarn a ser los datos contenidos en el primer registro o fila de la tabla. Para no tener que acceder y manipular las claves del registro de Windows existe el parmetro HDR, el cual anula el valor existente en el registro, debido a que en cada intento de conexin podemos modificar su valor, por lo que su utilizacin es altamente recomendable. Simplemente hay que establecer HDR a Yes|No para que utilice o no, los datos de la primera fila de la hoja como nombres de los campos, independientemente del valor existente en la clave del registro de Windows. Si en la cadena de conexin no se utiliza el parmetro HDR, se tomar el valor existente en el registro, por tanto es obligacin del programador verificar si el nombre de los campos estn incluidos o no en el primer registro de la tabla u objeto Recordset, a fin de evitar sorpresas desagradables.

2 de 26

12/11/2011 16:50



En el siguiente ejemplo se establece una conexin mediante la biblioteca de ADO, con un libro de trabajo de Excel. Slo si se indica el parmetro HDR en la propiedad Extended Properties, no se olvide de usar comillas dobles, cuando haga referencia al valor de dicha propiedad, porque se diferencia de la biblioteca de DAO en cunto a su utilizacin, y su omisin provocar que recibamos el mensaje de error No se pudo encontrar el archivo ISAM instalable:

Dim cnn As ADODB.Connection ' Abro una conexin con una hoja de clculo, la ' cual utiliza la primera fila como nombres de los ' campos Set cnn = New ADODB.Connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Libro1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes;"" "

A continuacin, el mismo ejemplo pero utilizado con DAO:

Dim db As Database ' Abro la hoja de clculo, pero no utilizo la primera ' fila de la hoja como nombres de los campos Set db = OpenDatabase("C:\Libro1.xls", False, False, "Excel 8.0;HDR=No;")

Es importante sealar que los parmetros de la cadena de conexin deben estar separados por punto y coma (;). Asimismo, procure no dejar un espacio en blanco en la cadena de conexin antes de especificar algn parmetro correspondiente al ISAM de Excel.

3. Los parmetros IMEX y MAXSCANROWS A veces puede ocurrir que una columna de nuestra hoja de clculo contenga datos numricos 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 limitacin 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 mayora de los valores probados. El valor de las filas escaneadas puede modificarse mediante la inclusin del parmetro MaxScanRows en las propiedades extendidas de la cadena de conexin. 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;" & _ "Data Source=C:\Libro1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes;MaxScanRows=16;"" "

3 de 26

12/11/2011 16:50



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 dilogo para la configuracin del origen de datos. Sin embargo, debido a un bug del driver ODBC, especificar el parmetro Rows to Scan (MaxScanRows) no tiene ningn 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 ms informacin consulte el siguiente artculo 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 ms remedio que aadir en las propiedades extendidas de la cadena de conexin, el parmetro IMEX=1, sobre todo si vamos a utilizar el mtodo 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 Importacin, 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, quizs haya tambin 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 numricos, 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 IMPORTACIN, por lo que los resultados obtenidos pueden ser impredecibles si intenta aadir o actualizar datos utilizando ste modo. Los posibles valores que puede contener IMEX son: 0 - modo de Exportacin 1 - modo de Importacin 2 - modo de Vinculacin (completa capacidad de actualizacin) Con un ejemplo se entender mejor la explicacin anterior. Imagine que tenemos un rango de celdas con nombre con los siguientes datos:Text K25OP 65KIO 65874 K34DLE ADEK98 47845,89 WSDE 25/03/77 DE58 $1.478,25 $87.458,96 25.698,47 Date 23/12/85 12/03/99 K94ADR KDI03 Currency $123,25 $12.547,69 KDFJ8 1.544,58 HY609 Double 1.254,45 Entero Long 58.945 125.478 698.745

En las distintas columnas existen tipos de datos diferentes, con la alineacin horizontal caracterstica de Excel segn el tipo de dato que contenga la celda. Si no se utiliza el parmetro IMEX=1, estos seran los resultados de la exportacin representados en un control DataGrid:Text K25OP 65KIO Date 23/12/85 12/03/99 Currency 123,25 12547,69 1544,58 Double 1254,45 Entero Long 58945 125478 698745

4 de 26

12/11/2011 16:50



ADEK98 1478,25 WSDE 25/03/77 87458,96 25698,47

Como se puede observar, existen celdas cuyo valor es Null, ya que no se han exportado ningn dato debido a la mezcla de tipos de datos contenidas en ellas. Los tipos de datos de los campos del objeto Recordset subyacente sern del tipo de campo mayoritario que contenga la columna de la hoja de clculo de Excel. Quiero recalcar nuevamente, debido a su importancia, que si el usuario utiliza la biblioteca de datos DAO, slo se devolver la primera fila del rango de celdas. Por el contrario, si utilizamos el modo de Importacin, estableciendo el parmetro 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:Text K25OP 65KIO 65874 ADEK98 47845,89 WSDE 25/03/77 Date 23/12/85 12/03/99 K34DLE DE58 1478,25 87458,96 25698,47 Currency 123,25 12547,69 K94ADR Double 1254,45 KDFJ8 1544,58 KDI03 Entero Long 58945 125478 698745 HY609

4. Cmo especificar los nombres de las hojas y rangos Para tener acceso a los datos contenidos en una hoja de clculo o en un libro de trabajo, tendremos que especificar el subconjunto de datos que deseamos utilizar. Dependiendo de la versin de Microsoft Excel disponible, podremos acceder a los siguientes subconjuntos de datos: - Archivos de hoja de clculo: Hoja completa Un rango de celdas con nombre Un rango de celdas sin nombre - Libro de trabajo: Hoja de clculo individual Un rango de celdas con nombre de cualquier parte del libro de trabajo Un rango sin nombre de una hoja de clculo individual En cuanto a los argumentos DATABASE y source que hay que proporcionar en la cadena de conexin, las convenciones que se utilizan para especificar los nombres de las hojas y rangos son los que se detallan a continuacin: Para tener acceso a ... Hoja de clculo completa Parmetros DATABASE source

Tipo de archivo Hoja de clculo Libro de trabajo

Versiones de Excel 3.0 y 4.0 5.0, 7.0 y 8.0

ruta de acceso del archivo de [nombre_archivo#xls] la hoja de clculo ruta de acceso del archivo del libro de trabajo, incluyendo el [nombre_hoja$] nombre de dicho archivo

5 de 26

12/11/2011 16:50



Rango de celdas con nombre

Todos

Todas

ruta de acceso del archivo de la hoja, incluyendo el nombre de dicho archivo

[rango_con_nombre]

ruta de acceso del archivo de la hoja, incluyendo el nombre [A1:Z50] Rango de de dicho archivo celdas sin ruta de acceso del archivo del nombre Libro de 5.0, 7.0 y libro de trabajo, incluyendo el [nombre_hoja$A1:Z50] trabajo 8.0 nombre de dicho archivo El valor del rango especificado no puede exceder el nmero mximo de filas, columnas u hojas de la hoja de clculo o del libro de trabajo. Hoja de clculo 3.0 y 4.0

5. Limitaciones a tener en cuenta Los controladores ISAM del motor de base de datos Microsoft Jet son compatibles con las siguientes versiones de Microsoft Excel: Versin ISAM Excel 3.0 Excel 4.0 Hoja de clculo individual S S Libro de trabajo de mltiples hojas No No Versin de Microsoft Excel Excel 5.0 Excel 8.0 Slo para Microsoft Excel 5.0 y 7.0 (95)

Para libros de trabajo de hojas mltiples de Microsoft Excel 8.0 (97), 9.0 (2000) y 10.0 (2002) Tambin hay otras operaciones que no se pueden realizar en las hojas de clculo ni en los libros de trabajo de Microsoft Excel mediante los controladores ISAM correspondientes: No se puede eliminar filas de las hojas de clculo ni de los libros de trabajo. Se puede borrar datos de celdas individuales de una hoja de clculo, siempre y cuando no contenga frmulas. No se puede crear ndices en las hojas ni en los libros de trabajo. Si una hoja de clculo o un libro de trabajo estn codificados (tienen establecida una contrasea), no se podr abrir ninguno de ellos, aunque se suministre el parmetro PWD en la cadena de conexin con la contrasea correcta, salvo que de la coincidencia que dicha hoja o libro estn actualmente abiertos en Microsoft Excel, cuando se establezca una conexin con la hoja de clculo o el libro de trabajo. Por tanto, si tiene pensado utilizar una hoja de clculo o un libro de trabajo para vincularlos o abrirlos mediante los objetos de acceso a datos, no establezca ninguna contrasea, ni a la hoja de clculo ni al libro de trabajo. 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 clculo de Excel. Estas limitaciones incluyen, pero no se limitan: Tamao de la hoja de clculo: 65.536 filas y 256 columnas Contenido de la celda (texto): 32.767 caracteres Hojas y Nombres en un libro de trabajo: limitado por la memoria disponible

6. Vinculacin de hojas de clculo de Microsoft Excel

6 de 26

12/11/2011 16:50



6.1. Vincular con ADOX La biblioteca de ADO no permite vincular una hoja de clculo Excel a una base de datos Access, para ello tenemos que ayudarnos de la biblioteca Microsoft ADO Ext. 2.7 for DLL and Security, ms conocida por ADOX, la cual es una extensin de los objetos y del modelo de programacin 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 clculo Excel, a una base de datos Access 2000:

Public Sub LinkExcelSheetWithADO Dim cnn As ADODB.Connection Dim cat As ADOX.Catalog Dim tbl As ADOX.Table ' Establezco la conexin con la base de datos actual Set cnn = New ADODB.Connection With cnn .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Mis documentos\Bd1.mdb;" .Open End With ' Abro un catlogo Set cat = New ADOX.Catalog cat.ActiveConnection = cnn ' Creo la nueva tabla Set tbl = New ADOX.Table tbl.Name = "Tabla Vinculada de Excel" Set tbl.ParentCatalog = cat ' Establezco las propiedades para crear el vnculo With tbl .Properties("Jet OLEDB:Create Link") = True .Properties("Jet OLEDB:Link Provider String") = "Excel 8.0;DATABASE=" & _ "C:\Mis documentos\Libro1.xls;HDR=Yes" .Properties("Jet OLEDB:Remote Table Name") = "Nombre_Rango" End With ' Aado la tabla a la coleccin 'Tables' cat.Tables.Append tbl End Sub

6.2. Vincular con DAO Para vincular una hoja de clculo de Microsoft Excel a una base de datos Access, hay que utilizar el mtodo 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 clculo 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 clculo se tratar como un registro de la tabla, no como un encabezado que contiene los nombres de los campos, debido a que el parmetro HDR de la cadena de conexin est establecido a No:

7 de 26

12/11/2011 16:50



Public Sub LinkExcelSheetWithDAO Dim db As Database Dim td As TableDef ' Abro la base de datos de Access Set db = OpenDatabase("C:\Mis documentos\Bd1.mdb") ' Creo un objeto TableDef. Set td = db.CreateTableDef("Tabla de Access vinculada") ' Establezco la informacin de conexin. td.Connect = "Excel 8.0;HDR=No;Database=C:\Mis documentos\Libro1.xls" td.SourceTableName = "WorkSheet1$A1:M50" ' Anexo el objeto TableDef para crear el vnculo db.TableDefs.Append td End Sub

7. Abrir una hoja de clculo de Microsoft Excel 7.1. Mediante ADO visualizando la informacin en un DataGrid Hay 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 versin del proveedor necesaria que hay que utilizar para abrir una hoja de clculo Excel, es la versin 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 ejecucin se recibir el siguiente mensaje de error: No se pudo encontrar el ISAM instalable. A continuacin, y mediante ADO, vamos a escribir un procedimiento que mostrar en un control DataGrid el conjunto de registros devueltos dependiendo de la seleccin de datos efectuada:

Public Sub OpenExcelSheetWithADO (ByVal Option As Integer) Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset ' Abro una conexin con una hoja de clculo ' Si se desea utilizar los encabezados, hay que escribir ' la instruccin HDR con las comillas dobles. Set cnn = New ADODB.Connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Mis documentos\Libro1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes;""" ' La conexin tambin se puede establecer mediante... ' cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};" & _ "DBQ=C:\Mis documentos\Libro1.xls"

8 de 26

12/11/2011 16:50



' Abro un recordset, seleccionando un rango de datos Set rs = New ADODB.Recordset With rs .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockOptimistic End With Select Case Option Case 1 ' Una hoja completa rs.Open "SELECT * FROM [WorkSheet1$]", cnn, , , adCmdText Case 2 ' Un rango con nombre rs.Open "SELECT * FROM [Tabla1]", cnn, , , adCmdText Case 3 ' Un rango de celdas sin nombre rs.Open "SELECT * FROM [WorkSheet1$A1:M50]", cnn, , , adCmdText End Select ' Propiedades del control DataGrid With DataGrid1 .AllowDelete = True .AllowAddNew = True .AllowUpdate = True ' Establezco el origen de datos del DataGrid Set .DataSource = rs End With End Sub

7.2. Mediante DAO mostrando los datos en un DBGrid En este ejemplo, vamos a utilizar un simple control Data para que automticamente se llene el control DBGrid, por tanto, en tiempo de diseo es necesario establecer la propiedad DataSource del control DBGrid con el nombre de un control Data, ya que el control DBGrid no admite automatizacin.

Public Sub OpenExcelSheetWithDAO (ByVal Option As Integer) Dim db As Database Dim rs As DAO.Recordset ' Abro la hoja de clculo Set db = OpenDatabase("C:\Mis documentos\Libro1.xls", False, False, "Excel 8.0;HDR=Yes;") ' Dependiendo de la opcin seleccionada, abrimos el rango correspondiente Select Case Option Case 1 ' Una hoja completa Set rs = db.OpenRecordset("SELECT * FROM [WorkSheet1$]", dbOpenDynaset) Case 2 ' Un rango con nombre Set rs = db.OpenRecordset("SELECT * FROM Tabla1", dbOpenDynaset) Case 3 ' Un rango de celdas sin nombre Set rs = db.OpenRecordset("SELECT * FROM [WorkSheet1$A1:M50]",

9 de 26

12/11/2011 16:50



dbOpenDynaset) End Select ' Establezco el recordset del control Data Set Data1.Recordset = rs End Sub

7.3. Configurar un control de datos de ADO en tiempo de ejecucin Personalmente opino que la mejor manera de configurar un control determinado es establecer sus propiedades en tiempo de ejecucin, de esta forma obtendremos un cdigo limpio y fcil 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 diseo, lo mejor es utilizar el mtodo Form_Load de nuestro formulario para modificar los valores por defecto del control que vamos a utilizar. De esta forma, podremos revisar el cdigo 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 ejecucin requiere quizs un poco ms de tiempo debido en parte a la escritura del propio cdigo, pero ese tiempo aadido se ver recompensado con creces cuando deseemos modificar algn 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 ejecucin, por lo que slo necesitar aadir dichos controles al formulario y pegar el siguiente cdigo en la seccin Declaraciones:

Private Sub Form_Load() ' Configuramos el control de datos With Adodc1 .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Mis documentos\Libro1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes;""" .RecordSource = "[WorkSheet1$A1:M50]" .CommandType = adCmdTable End With ' Enlazamos el control DataGrid Set DataGrid1.DataSource = Adodc1 ' Enlazamos los dos controles TextBox With Text1 Set .DataSource = Adodc1 ' Origen de datos .DataField = "Campo1" ' Nombre del campo End With With Text2 Set .DataSource = Adodc1 .DataField = "Campo2" End With End Sub

Si han ejecutado el cdigo, habrn podido observar lo fcil y limpio que ha resultado

10 de 26

12/11/2011 16:50



configurar todo en tiempo de ejecucin, 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 slo cambiar los valores de Data Source o RecordSource, respectivamente, podemos reutilizar el cdigo. 7.4. Conocer el nombre de las columnas de Excel Si tenemos una hoja de clculo, 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 coleccin Fields de dicho objeto. El siguiente ejemplo utiiza la biblioteca de ADO, fcilmente 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 Dim rs As ADODB.Recordset Dim fld As ADODB.Field ' Establecemos una conexin con el libro de trabajo Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source = C:\Mis documentos\Libro1.xls" .Properties("Extended Properties") = "Excel 8.0;HDR=Yes" .Open End With ' Creamos un nuevo objeto Recordset Set rs = New ADODB.Recordset With rs Set .ActiveConnection = cnn ' Indicamos el nombre de la hoja .Source = "[Hoja1$A1:M50]" .Open End With ' Comprobamos el nombre y el tipo de datos de los campos For Each fld In rs.Fields MsgBox fld.Name, , fld.Type Next

En ste caso s es importante utilizar y establecer a Yes el parmetro HDR en la cadena de conexin, porque de lo contrario obtendramos como nombres de campos F1, F2, ... Fn, en el supuesto de que indicramos el valor No, o bien, el valor de la clave FirstRowHasNames del registro de Windows est establecida a 00.

8. Cmo crear un nuevo libro de trabajo de Excel Sin lugar a dudas, la forma ms fcil y sencilla de crear un nuevo libro de trabajo es a travs del propio programa Microsoft Excel. Pero puede ser que el usuario final de nuestra aplicacin 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 tcnica que se conoce como automatizacin para crear un nuevo libro de trabajo, con sus correspondientes hojas de clculo. Si deseamos crear una aplicacin que interacte con Excel, con independencia de que el

11 de 26

12/11/2011 16:50



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 sern los archivos msexcl40.dll (para la versin del motor Jet 4.0), o msexcl35.dll (para la versin 3.51 de dicho motor). Si vamos a utilizar la biblioteca de ADO, tambin necesitar tener instalado una versin del MDAC. Mediante las siguiente tcnicas que se detallan a continuacin, podemos crear un nuevo libro de trabajo y una hoja de clculo, o bien, podemos aadir nuevas hojas de clculo a un libro ya existente. Lo curioso de utilizar ambas tcnicas es que, aparte de crear una nueva hoja de trabajo, tambin crear un nuevo rango con el mismo nombre que hayamos asignado a la hoja de trabajo. 8.1. Mediante ADO o DAO utilizando la instruccin CREATE TABLE Con el estndar 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 instruccin CREATE TABLE del lenguaje de manipulacin de datos (DDL) de SQL. Simplemente necesitaremos definir la cadena para crear la tabla, la cul incluir el nombre de la tabla (hoja de clculo) y el nombre y tipo de los campos (columnas) que conformarn la misma. Una vez que la tengamos definida, slo basta ejecutar el mtodo 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 cul crear una nueva hoja de clculo, y un rango con idntico nombre, con las columnas necesarias para introducir los datos de nuestros clientes. Mediante la biblioteca de ADO sera as:

Dim cnn As ADODB.Connection Dim SQL As String ' Establecemos la conexin con el nuevo libro ' de trabajo que vamos a crear Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "C:\Mis documentos\Libro10.xls" .Properties("Extended Properties") = "Excel 8.0" .Open End With ' Creamos la sintaxis SQL para crear la hoja de trabajo SQL = "CREATE TABLE Clientes (IdCliente LONG, [Nombre Cliente] TEXT," & _ "Domicilio TEXT, [Fecha de Alta] DATETIME)" ' Ejecutamos la consulta SQL cnn.Execute SQL ' Cerramos la conexin y liberamos los recursos asociados cnn.Close Set cnn = Nothing

El inconveniente que tiene ste mtodo para crear un nuevo libro de trabajo, o aadir una nueva hoja de clculo 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 tendrn el formato General.

12 de 26

12/11/2011 16:50



Por ltimo indicar que si la hoja de clculo ya existe, se producir un error interceptable. 8.2. Mediante una consulta SQL de creacin de tabla Una de las cosas que ms me fascina del Lenguaje de Consulta Estructurado (SQL), es la facilidad que tiene para crear nuevas tablas (en este caso, hojas de clculo), mediante una variacin de la instruccin SELECT. Simplemente basta con agregarle la clusula INTO para obtener, de manera rpida y sencilla, una nueva hoja de clculo 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 creacin 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 mtodo Execute de un objeto Database abierto:

Dim Dim Dim Dim

sExcelFileName As String sWorkSheetName As String sTableName As String cnn As ADODB.Connection

' Datos por defecto sExcelFileName = "C:\Mis documentos\Libro1.xls" sWorkSheetName = "WorkSheet1" sTableName = "Socios" ' Abro la base de datos Set cnn = New ADODB.Connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Mis documentos\Bd1.mdb;" ' Creo una hoja de clculo nueva mediante la instruccin SELECT...INTO cnn.Execute _ "SELECT * INTO [Excel 8.0;DATABASE=" & sExcelFileName & "].[" & _ sWorkSheetName & "] FROM " & "[" & sTableName & "]"

Al utilizar sta forma de crear una hoja de clculo, todas las celdas tambin tendrn el formato General, a excepcin 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 ADOX Disponiendo de una conexin 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 mnimo, una hoja de clculo. El programador que tenga una cierta experiencia con la biblioteca de ADOX, conocer que mediante el mtodo Append de la coleccin Tables de un objeto Catalog, podemos crear nuevas tablas y aadirlas a un origen de datos, pero aqu, en lugar de aadirlas a una base de datos, vamos a crear un objeto Table para crear una nueva hoja de clculo en nuestro libro de Excel.

Dim cnn As ADODB.Connection Dim cat As ADOX.Catalog Dim tbl As ADOX.Table ' Establecemos una conexin, especificando slo la ruta ' donde vamos a crear el nuevo libro de trabajo

13 de 26

12/11/2011 16:50



Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source = C:\Mis documentos\Libro10.xls" .Properties("Extended Properties") = "Excel 8.0;HDR=Yes" .Open End With ' Creo un nuevo objeto Catalog Set cat = New ADOX.Catalog cat.ActiveConnection = cnn ' Creamos un nuevo objeto "Table" Set tbl = New ADOX.Table ' Genero una nueva hoja de trabajo With tbl .Name = "Clientes" ' Aadimos los nombres de las columnas ' de la primera fila .Columns.Append "IdCliente", adInteger .Columns.Append "Nombre", adVarWChar .Columns.Append "CIF", adVarWChar End With ' Aadimos la tabla al catlogo cat.Tables.Append tbl ' Cerramos la conexin y liberamos los recursos asociados cnn.Close Set cnn = Nothing

Reitero nuevamente que todas las celdas tendrn el formato General, aunque se indique un tipo de dato numrico o de fecha, debido a un error de conversin de tipos de datos en Excel.

9. Importar una hoja de clculo a una base de datos Access En 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 clculo de Excel), tendremos que establecer una conexin 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 Dim sConnect As String, sSQL As String Dim cnnActiva As ADODB.Connection ' Establezco la conexin con la base de datos de Access, ' la cual ser la base de datos Activa Set cnnActiva = New ADODB.Connection cnnActiva.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _

14 de 26

12/11/2011 16:50



"Data Source=C:\Mis documentos\bd1.mdb;" sTablaDestino = "[Tabla Importada desde Excel]" sTablaOrigen = "[WorkSheet1$A1:M50]" ' Importo la tabla a la base de datos Activa sConnect = "(')C:\Mis documentos\Libro1.xls(') (')Excel 8.0;HDR=Yes;(')" sSQL = "SELECT * INTO " & sTablaDestino & " FROM " & sTablaOrigen & " IN " & sConnect cnnActiva.Execute sSQL ' Cierro la conexin cnnActiva.Close

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 versin del ISAM instalable que se va a utilizar, procurando dejar un espacio en blanco entre ambos parmetros. Ni que decir tiene que, al ejecutar el cdigo, procure quitar todos los parntesis incluidos en la variable sConnect. A continuacin, 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 tambin por la clusula IN:

Dim sTablaOrigen As String, sTablaDestino As String Dim sConnect As String, sSQL As String Dim dbActiva As Database ' Abro la base de datos de Access, ' la cual ser la base de datos Activa Set dbActiva = OpenDatabase("C:\Mis documentos\Bd1.mdb") sTablaDestino = "[Rango importado desde Excel]" sTablaOrigen = "[Rango1]" ' Importo la tabla a la base de datos Activa ' Elimine los parntesis dejando slo las comillas simples ' Tambin se puede utilizar dos pares de comillas dobles ("")("") sConnect = "(')(')[Excel 8.0; HDR=No; DATABASE=C:\Mis documentos\Libro1.xls;]" sSQL = "SELECT * INTO " & sTablaDestino & " FROM " & sTablaOrigen & " IN " & sConnect dbActiva.Execute sSQL ' Cierro la conexin dbActiva.Close

Nuevamente hago hincapi en el parmetro HDR, porque de especificarse en la cadena de conexin HDR=No, los campos de la tabla importada tendran por nombre F1, F2, ... Fn, y la primera fila de la hoja de clculo, se convertira en el primer registro de la tabla importada. Por ltimo comentar que, al igual que se puede importar una hoja de clculo completa, tambin se puede importar un rango con nombre, al igual que un rango de celdas sin nombre.

15 de 26

12/11/2011 16:50



10. Exportar los datos desde Excel Al igual que en el apartado anterior, tambin 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 utilizbamos una conexin con la base de datos para ejecutar la consulta de creacin de tabla, ahora vamos a establecer la conexin directamente con el libro de trabajo de Excel, para desde all exportar una hoja de clculo o un rango de celdas, utilizando para ello otra sintaxis distinta de las anteriores para la consulta SQL de creacin de tabla. 10.1. A una base de datos Access En esta ocasin se trata de abrir una conexin con la hoja de clculo 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 clculo:

Dim sTablaOrigen As String, sTablaDestino As String Dim sConnect As String, sSQL As String Dim cnnActiva As ADODB.Connection ' Establezco la conexin con la base de datos externa Set cnnActiva = New ADODB.Connection cnnActiva.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Mis documentos\Libro1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes;""" sTablaDestino = "[Tabla Importada desde Excel]" sTablaOrigen = "[WorkSheet1$]" ' Construimos la consulta SQL sConnect = " 'C:\Mis documentos\Bd1.mdb' " sSQL = "SELECT * INTO " & sTablaDestino & " IN " & sConnect & " FROM " & sTablaOrigen ' Exportamos la tabla a una base de datos Access cnnActiva.Execute sSQL ' Cierro la conexin cnnActiva.Close

En sta situacin, un detalle que deberamos de tener en cuenta sera la posibilidad de que la base de datos se encuentre protegida mediante una contrasea, por lo que necesariamente tendramos que especificarla en la cadena de conexin con la base de datos externa, utilizando para ello el parmetro ;PWD, tal y como se muestra en el siguiente ejemplo. Mediante la biblioteca de DAO, sera as:

Dim sTablaOrigen As String, sTablaDestino As String Dim sConnect As String, sSQL As String Dim db As Database ' Abro la hoja de clculo Set db = OpenDatabase("C:\Libro1.xls", False, False, "Excel 8.0; HDR=Yes;") sTablaDestino = "[Tabla Importada desde Excel]"

16 de 26

12/11/2011 16:50



sTablaOrigen = "[WorkSheet1$]" ' Exporto la tabla a una base de datos Access, especificando la ruta y la contrasea ' de la base de datos, y con la sintaxis que se muestra. Atencin a las comillas simples ' en color rojo sConnect = "''[;DATABASE=C:\Mis documentos\Bd1.mdb;PWD=Contrasea]" sSQL = "SELECT * INTO " & sTablaDestino & " IN " & sConnect & " FROM " & sTablaOrigen db.Execute sSQL ' Cierro la base de datos db.Close

10.2. A una base de datos SQL Server A continuacin se explicar cmo 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 conexin ODBC en la sintaxis de la consulta SQL de creacin 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 Dim lNumRegAfect As Long Dim strSQL As String ' Abrimos una conexin con el libro de trabajo Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source=C:\Mis documentos\Libro1.xls" .Properties("Extended Properties") = "Excel 8.0" .Open End With ' Importamos utilizando una cadena ODBC strSQL = "SELECT * INTO [ODBC;Driver={SQL Server};" & _ "Server=Nombre_Servidor_SQL;Database=pubs;" & _ "UID=Nombre_Usuario;PWD=Contrasea].TablaExcel " & _ "FROM [Rango1]" ' Ejecutamos la consulta cnn.Execute strSQL, lNumRegAfect, adExecuteNoRecords MsgBox "Nmero de registros afectados: " & lNumRegAfect ' Cerramos la conexin cnn.Close

Para ms informacin sobre cmo importar datos a SQL Server desde Excel, consulte el siguiente artculo de la Knowledge Base: http://support.microsoft.com/default.aspx?scid=kb;EN-US;321686

17 de 26

12/11/2011 16:50



11. Aadir registros a una tabla procedentes de una hoja de clculo Excel Si tenemos una tabla creada en Access y queremos aadir los datos existentes en una hoja de clculo de Excel, podemos insertar los datos mediante la instruccin INSERT INTO, lo que en SQL se conoce como una consulta de datos aadidos o anexados, la cual aade 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 aade 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 aade 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 aadir los registros. Tambin hay que tener en cuenta que, si aade registros a una tabla con un campo de tipo AutoNumrico y desea volver a numerar los registros aadidos, no incluya el campo AutoNumrico en la consulta. Incluya el campo Autonumrico en la consulta slo 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 aadir registros, debido a la inexistencia de la misma, cosa que por otra parte, es bastante lgica. 11.1. Desde la propia base de datos Access activa Si tenemos una conexin abierta con una base de datos Access, que ya contiene la tabla o consulta donde queremos aadir los registros de la hoja de clculo, solamente tendramos que especificar la versin del ISAM a utilizar, el parmetro HDR y la ruta de la hoja de clculo, as como el origen del rango que deseamos importar, en el parmetro DATABASE. Mediante ADO sera as:

Dim cnnActiva As ADODB.Connection Dim sTablaOrigen As String, sTablaDestino As String Dim sConnect As String, sSQL As String sTablaDestino = "[Nombre de la Tabla Access]" sTablaOrigen = "[WorkSheet1$A1:M50]" sConnect = "[Excel 8.0;HDR=Yes;DATABASE=C:\Libro1.xls]." & sTablaOrigen ' Construimos la consulta SQL sSQL = "INSERT INTO " & sTablaDestino & " SELECT * FROM " & sConnect ' Conexin con tu base de datos Accesss Set cnnActiva = New ADODB.Connection With cnnActiva .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Mis documentos\Bd1.mdb;" .Open ' Aadimos los registros .Execute sSQL, , adCmdText .Close End With

En este supuesto, el parmetro HDR es sumamente importante, porque de especificar el valor No, podramos tener problemas al aadir los registros, debido a que el motor Jet no sabra dnde insertar los mismos, aparte de que los nombres de las columnas de la hoja de

18 de 26

12/11/2011 16:50



clculo deben coincidir con los nombres de los campos de la tabla. 11.2. Desde la propia hoja de clculo Excel Al contrario que en el punto anterior, a continuacin voy a mostrar cmo se aadiran los registros en la tabla de Access, pero desde una conexin con la hoja de clculo de Excel, de esta forma se observar la utilizacin de la clusula 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 clculo 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 Dim sConnect As String, sSQL As String Dim db As Database sTablaDestino = "[Tabla Importada desde Excel]" sTablaOrigen = "[WorkSheet1$]" sConnect = " 'C:\Mis documentos\Bd1.mdb' " ' Construmos la consulta SQL sSQL = "INSERT INTO " & sTablaDestino & " IN " & sConnect & " SELECT * FROM " & sTablaOrigen ' Abro la hoja de clculo Set db = OpenDatabase("C:\Libro1.xls", False, False, "Excel 8.0; HDR=Yes;") ' Aadimos los registros db.Execute sSQL ' Cierro la conexin db.Close

11.3. Exportar datos de una hoja de clculo Excel a otra hoja de clculo El potencial del ISAM de Excel, unido con el Lenguaje de Consulta Estructurado, nos sirve tambin para poder aadir filas en una hoja de clculo con los datos pertenecientes a otra hoja de clculo, lo mismo que si los registros los agregramos a una tabla de una base de datos de Access. Simplemente estableceramos una conexin con la hoja de clculo, cuyos datos queremos exportar, y pasaramos en la consulta SQL la versin del ISAM a utilizar y los parmetros 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 clculo externa, indicndole que la primera fila, no contiene los nombres de las columnas. Los registros se insertarn en la primera fila libre de la hoja de clculo:

Dim sTablaOrigen As String, sTablaDestino As String Dim sConnect As String, sSQL As String Dim cnn As ADODB.Connection sTablaDestino = "[WorkSheet2$]" sTablaOrigen = "[Rango1]" ' IMPORTANTE: A la hora de ejecutar el cdigo, elimine los parntesis. ' Se han incluido para que el lector observe la utilizacin de comillas simples

19 de 26

12/11/2011 16:50



sConnect = "(')(') [Excel 8.0;HDR=No;DATABASE=C:\Libro2.xls]" ' Construimos la consulta sSQL = "INSERT INTO " & sTablaDestino & " IN " & sConnect & " SELECT * FROM " & sTablaOrigen ' Establezco la conexin con la hoja de calculo activa Set cnn = New ADODB.Connection With cnn .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=G:\VBNews\Libro1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=No;""" .Execute sSQL, , adCmdText .Close End With

11.4. Cmo insertar una fila de datos en una hoja de clculo En los ejemplos anteriores hemos utilizado la instruccin INSERT INTO para ejecutar una consulta de datos anexados con mltiples registros. En ste apartado aprenderemos a insertar una nueva fila de datos en nuestra hoja de clculo, 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 cul es la siguiente: INSERT INTO Destino (Campo1, Campo2, ..., CampoN) VALUES (Valor1, Valor2, ..., ValorN) 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 ningn valor en dichas columnas. Las filas se aadirn al final de la ltima fila de la hoja de clculo o del rango de celdas especificado. La instruccin INSERT INTO generalmente se utiliza con el mtodo 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 aadir una nueva fila a nuestra hoja de clculo, compuesto por los valores de tres columnas.

Dim cnn As ADODB.Connection Dim SQL As String ' Establecemos una conexin con el libro de trabajo Set cnn = New ADODB.Connection With cnn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source = C:\Mis documentos\Libro1.xls" .Properties("Extended Properties") = "Excel 8.0;HDR=Yes" .Open End With ' Construimos la consulta SQL de datos aadidos ' para un slo registro SQL = "INSERT INTO [Hoja1$] (IdSocio, Nombre, [Fecha Alta]) " & _ "VALUES (1277, 'Gonzlez Gonzlez, Pedro', #05/23/2003#)" ' Ejecutamos la consulta cnn.Execute SQL ' Cerramos la conexin y liberamos los recursos asociados

20 de 26

12/11/2011 16:50



cnn.Close Set cnn = Nothing

Si la primera fila de la hoja de clculo o del rango de celdas contiene el nombre de las columnas, necesariamente tendremos que especificarlo afirmativamente en el parmetro HDR de la cadena de conexin, porque de lo contrario, obtendremos un error interceptable. La primera vez que ejecutemos ste cdigo 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 ordenacin 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 artculo de la Base del Conocimiento: Error de secuencia de ordenacin al abrir por primera vez un Recordset ADODB junto con un archivo de Excel No nos debe de preocupar mucho el error, porque, aparte de que slo se produce la primera vez que ejecutamos la aplicacin en el entorno de desarrollo, una vez que nuestra aplicacin est compilada, no volver a producirse el citado error. Cuando se produzca el error en el entorno de desarrollo, basta con pulsar el botn Depurar y posteriormente reanudar la ejecucin 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 clculo de Excel que no fu creada o modificada usando ADOX, podemos tener problemas a la hora de insertar datos numricos, ya que stos aparecern alineados a la izquierda precedida de una comilla simple. En Excel 2002, los nmeros estn marcados con la etiqueta inteligente de advertencia "nmero 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 anlisis numrico, tal y como se puede comprobar en el siguiente artculo de la KB: Usar ADOX con datos de Excel desde Visual Basic o VBA A modo de conclusin, ADOX funciona mejor con Excel cuando se crean a la misma vez la hoja de clculo y sus columnas. Por el contrario, ADO trabaja mejor con los datos de Excel cuando dichos datos se guardaron en la hoja de clculo que fue creada utilizando ADOX.

12. Actualizando datos Tambin 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 clculo de un libro de trabajo de Excel. Para ello ejecutaremos una consulta de actualizacin mediante la utilizacin de la instruccin UPDATE, correspondiente al lenguaje de manipulacin de datos de SQL, la cual cambia los valores de los campos de una tabla especificada basndose en un criterio especfico, y cuya sintaxis es la siguiente: UPDATE Tabla SET Campo1=Valor1, Campo2=Valor2, ... CampoN=ValorN WHERE criterio La instruccin UPDATE generalmente se utiliza con el mtodo 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 instruccin UPDATE no genera ningn conjunto de resultados. Para conocer los datos que se vern afectados por la actualizacin, previamente habr que ejecutarse una consulta de seleccin que utilice los mismos criterios que la consulta de actualizacin.

21 de 26

12/11/2011 16:50



12.1. Actualizar todos los registros de un rango de celdas de la hoja de clculo Si en la sintaxis de la instruccin UPDATE se suprime la clusula WHERE, todos los registros de la tabla especificada se actualizarn. 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 sera de la siguiente forma:

Dim sTablaDestino As String, sSQL As String Dim cnn As ADODB.Connection ' Establezco la conexin con la hoja de clculo Set cnn = New ADODB.Connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Mis documentos\Libro1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes;""" sTablaDestino = "[Empleados]" ' Actualizamos todos los registros de un campo con un valor especfico sSQL = "UPDATE " & sTablaDestino & " SET Salario = Salario + (Salario * 3.5/100)" ' Ejecutamos la consulta cnn.Execute sSQL ' Cierro la conexin cnn.Close

Al igual que se ha especificado un rango de celdas con nombre, tambin se puede actualizar un conjunto de celdas con solo indicar el rango de las mismas:

sTablaDestino = "[WorkSheet1$A1:Z6]"

En el supuesto de que slo quisieramos incrementar el salario de un trabajador en concreto, la sintaxis de la consulta SQL de actualizacin sera la siguiente:

sSQL = "UPDATE " & sTablaDestino & " SET Salario = Salario + (Salario * 3.5/100) WHERE Empleado = 'Jos Prez' "

12.2. Actualizar los datos de una columna individualmente A diferencia del apartado anterior, donde se actualizaban todos los registros de acuerdo a un mismo valor comn para todos ellos, tambin nos puede interesar actualizar los datos de una columna con los valores individuales que tenemos en otra columna de la misma hoja de clculo, mediante la comparacin de un mismo campo comn 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 clculo:

Dim sTablaOrigen As String, sTablaDestino As String

22 de 26

12/11/2011 16:50



Dim sSQL As String Dim cnn As ADODB.Connection ' Establezco la conexin con la base de datos externa Set cnn = New ADODB.Connection cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Mis documentos\Libro1.xls;" & _ "Extended Properties=""Excel 8.0;HDR=Yes;""" sTablaOrigen = "[Datos_Actualizados]" sTablaDestino = "[Empleados]" ' Actualizamos los registros de una columna con los registros de otra tabla sSQL = "UPDATE " & sTablaDestino & "," & sTablaOrigen & " SET " & sTablaDestino & ".Salario = " & sTablaOrigen & ".Salario WHERE " & sTablaDestino & ".Nombre = " & sTablaOrigen & ".Nombre" ' Ejecutamos la consulta cnnActiva.Execute sSQL ' Cierro la conexin cnnActiva.Close

13. Establecer una conexin mediante el driver ODBC para Excel Cuando se establece una conexin a una hoja de clculo 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 clculo, 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 direccin: 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 clculo contiene los nombres de las columnas (nombres del campo), cosa del todo incierta, porque perfectamente se puede tener una hoja de clculo 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 algn 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 parmetro HDR en la cadena de conexin, tal y como s lo permite el proveedor OLE DB, no queda ms 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 clculo Excel. El siguiente ejemplo demuestra que de nada sirve utilizar el valor FirstRowHasNames en la cadena de conexin. Vamos a establecer una conexin 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. Asegrese que la primera fila de la hoja no figuren los nombres de las columnas:

Dim cnnExcel As ADODB.Connection

23 de 26

12/11/2011 16:50



Dim rs As ADODB.Recordset ' Conexin con la hoja de clculo mediante el ' conductor (driver) ODBC para Excel Set cnnExcel = New ADODB.Connection With cnnExcel .Provider = "MSDASQL" .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _ "DBQ=C:\Mis documentos\Libro1.xls;" & _ "FirstRowHasNames=0;" .Open End With ' Abrimos el objeto Recordset Set rs = New ADODB.Recordset With rs Set .ActiveConnection = cnnExcel .Source = "[WorkSheet1$]" .Open End With ' Escribimos los valores y nombres de los campos ' del primer registro devuelto For x = 0 To rs.Fields.Count - 1 Debug.Print "Nombre Campo: " & rs.Fields(x).Name, "Valor: " & rs.Fields(x).Value Next ' Cerramos el recordset y la conexin rs.Close cnnExcel.Close

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, reptase el ejemplo, pero sta vez, con una hoja de clculo 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 artculos tiles de Microsoft Knowledge Base La siguiente relacin 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 informacin facilitada. De la amplia lista de artculos que me facilit sobre el tema que versa el presente artculo, a continuacin 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 mayora de los artculos de la Knowledge Base que cito (por no decir todos), estn en ingls. He traducido los ttulos de los artculos a fin de facilitar al usuario hispanohablante una referencia ms rpida al contenido de los mismos. En dichos artculos el usuario podr encontrar una gran informacin sobre cmo manipular los datos contenidos en una hoja de clculo Excel con la biblioteca Microsoft ActiveX Data Object.

24 de 26

12/11/2011 16:50



Artculo General 257819 303814 278973 195951

Tipo

Descripcin

HOWTO Usar ADO con datos de Excel desde Visual Basic o VBA HOWTO Usar ADOX con datos de Excel desde Visual Basic o VBA EJEMPLO El ejemplo ExcelADO demuestra cmo utilizar ADO para leer/escribir datos en un libro de trabajo de Excel HOWTO Consultar y actualizar datos de Excel utilizando ADO desde ASP

Transferir Datos a Excel 247412 INFO Mtodos para transferir datos a Excel desde Visual Basic 295646 HOWTO Transferir datos desde una fuente de datos de ADO a Excel with ADO 246335 HOWTO Transferir datos a Excel desde un Recordset de ADO con automatizacin 319951 HOWTO Transferir datos a Excel utilizando SQL Server DTS 306125 HOWTO Importar datos desde SQL Server a Microsoft Excel 321686 HOWTO Importar datos a SQL Server desde Excel Temas Conocidos (los primeros de la lista son los ms recientes) 319998 BUG Ocurre el error Memory Leak cuando consulta una hoja de trabajo abierta de Excel utilizando ADO 316809 BUG No hay error de conexin de ADO cuando no se encuentra un libro de trabajo de Excel 314763 FIX ADO inserta datos equivocados dentro de columnas de Excel 316475 PRB Mensaje de error "La operacin debe utilizar una consulta actualizable" cuando accede a Excel a travs de ODBC 300948 BUG TABLE_TYPE incorrecto es devuelto por las hojas de trabajo de Excel 294410 ACC2002 Los valores nulos son remplazados con los datos del siguiente campo cuando se exporta a Excel 293828 BUG El tamao del archivo de excel crece cuando edita un Recordset de ADO 288343 BUG El driver ODBC de Excel hace caso omiso del valor establecido en FirstRowHasName o en la configuracin del encabezado 194124 PRB Excel devuelve valores como NULL usando el mtodo OpenRecordset de DAO 189897 XL97 Datos truncados a 255 caracteres con el driver ODBC de Excel 246167 PRB Error de secuencia de ordenacin al abrir por primera vez un Recordset ADODB junto con un archivo de Excel 109376 Se produce error de conversin de tipos cuando importa datos desde Microsoft Excel

NOTA: El contenido de ste artculo es una recopilacin de las participaciones del autor en el grupo de noticias en espaol de Visual Basic (microsoft.public.es.vb), as como de la informacin disponible en los distintos artculos que la Microsoft Knowledge Base ofrece sobre el tema en cuestin, de los cuales se hace una referencia en el apartado nmero catorce. La informacin contenida en este artculo, as como el cdigo fuente incluido en el mismo, se proporciona COMO EST, sin garantas de ninguna clase, y no otorga derecho alguno. Usted asume cualquier riesgo al poner en prctica, utilizar o ejecutar lo explicado, recomendado o sugerido en el presente artculo.

25 de 26

12/11/2011 16:50



This article is provided AS IS with no warranties, and confers no rights. You assume all risk for your use.

Enrique Martnez Montejo - 2003

26 de 26

12/11/2011 16:50

trabajar con ado, dao y excel

Documents