Trabajar con ADO, DAO y Excel

Most Valuable Professional - España  
 

Por Enrique Martínez Montejo
[MS MVP - VB]

 
 

Última revisión: 15/08/2004

   
 
 
 
 
Índice  
 
1. Utilizar objetos de acceso a datos con hojas de cálculo de Excel  
2. El parámetro HDR y la supresión de los encabezados  
3. Los parámetros IMEX y MAXSCANROWS  
4. Cómo especificar los nombres de las hojas y rangos  
5. Limitaciones a tener en cuenta  
6. Vinculación de hojas de cálculo de Microsoft Excel  
6.1. Vincular con ADOX  
6.2. Vincular con DAO  
7. Abrir una hoja de cálculo de Microsoft Excel  
7.1. Mediante ADO visualizando la información 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 ejecución  
    7.4. Conocer el nombre de las columnas de Excel  
8. Cómo crear un nuevo libro de trabajo de Excel  
    8.1. Mediante ADO o DAO utilizando la instrucción CREATE TABLE  
    8.2. Mediante una consulta SQL de creación de tabla  
    8.3. Utilizando conjuntamente las bibliotecas de ADO y ADOX  
9. Importar una hoja de cálculo 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. Añadir registros a una tabla procedentes de una hoja de cálculo Excel  
11.1. Desde la propia base de datos Access activa  
11.2. Desde la propia hoja de cálculo Excel  
11.3. Exportar datos de una hoja de cálculo Excel a otra hoja de cálculo  
    11.4. Cómo insertar una fila de datos en una hoja de cálculo  
12. Actualizando datos  
12.1. Actualizar todos los registros de un rango de celdas de la hoja de cálculo  
12.2. Actualizar los datos de una columna individualmente  
13. Establecer una conexión mediante el driver ODBC para Excel  
14. Algunos artículos útiles de Microsoft Knowledge Base  
         
         
 

1. Utilizar objetos de acceso a datos con hojas de cálculo de Excel

Mediante los controladores ISAM (Método de acceso secuencial indexado) instalables, podemos abrir y manipular la información contenida en una hoja de cálculo Excel lo mismo que si se tratara de una tabla de una base de datos cualquiera.

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 instalación de Visual Basic y seleccione los controladores apropiados para proceder a su instalación.

Durante el proceso de instalación, el controlador IISAM escribe la información de configuración en el Registro de Windows, estableciendo unos valores predeterminados que posteriormente podemos modificar su comportamiento utilizando para ello cualquiera de los métodos siguientes:

  • Utilizando el editor del Registro (no recomendado).
  • Utilizando las funciones API de acceso al Registro, antes de establecer la conexión con el origen de datos (requiere el conocimiento de dichas funciones API).
  • Mediante la cadena de conexión, en el momento de acceder a los datos. Es el método recomendado debido a que no modificará ningún valor del registro, porque los valores establecidos en la cadena, sólo serán válidos para un archivo de datos en particular. Este es el método que se utilizará a lo largo del presente artículo.

Hay tres formas de acceder a los datos de un archivo de hoja de cálculo o libro de trabajo de Miicrosoft Excel: vinculando la hoja de cálculo 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 cálculo, bien añadiendo los registros a una hoja ya existente.

Por último, indicar que en este artículo se utilizaran las dos bibliotecas de acceso a datos más utilizadas con Visual Basic (DAO y ADO), para tener acceso a hojas de cálculo y libros de trabajo de Microsoft Excel, utilizando solamente la versión del ISAM «Excel 8.0», por ser el formato correspondiente a las últimas versiones de Microsoft Excel.

 
     
     
 

2. El parámetro HDR y la supresión 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 cálculo 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 cuál 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 cálculo, simplemente bastaría con abrir el registro, y cambiar su valor a «00», utilizándose entonces como nombres de campos, F1, F2,.., Fn, que representarían al primer campo, segundo campo, ..., último campo. Pero si se utiliza ésta última opción, hay que tener presente que los datos de la primera fila de la hoja de cálculo pasarán 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 parámetro HDR, el cual anula el valor existente en el registro, debido a que en cada intento de conexión podemos modificar su valor, por lo que su utilización 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 conexión no se utiliza el parámetro HDR, se tomará el valor existente en el registro, por tanto es obligación del programador verificar si el nombre de los campos están incluidos o no en el primer registro de la tabla u objeto Recordset, a fin de evitar sorpresas desagradables.

En el siguiente ejemplo se establece una conexión mediante la biblioteca de ADO, con un libro de trabajo de Excel. Sólo si se indica el parámetro 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 cuánto a su utilización, y su omisión provocará que recibamos el mensaje de error No se pudo encontrar el archivo ISAM instalable:

Dim cnn As ADODB.Connection

' Abro una conexión con una hoja de cálculo, 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 continuación, el mismo ejemplo pero utilizado con DAO:

Dim db As Database

' Abro la hoja de cálculo, 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 señalar que los parámetros de la cadena de conexión deben estar separados por punto y coma (;). Asimismo, procure no dejar un espacio en blanco en la cadena de conexión antes de especificar algún parámetro correspondiente al ISAM de Excel.

 
     
     
 

3. Los parámetros IMEX y MAXSCANROWS

A 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;" & _
              "Data Source=C:\Libro1.xls;" & _
              "Extended Properties=""Excel 8.0;HDR=Yes;MaxScanRows=16;"" "

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:

0 - modo de Exportación
1 - modo de Importación
2 - modo de Vinculación (completa capacidad de actualización)

Con un ejemplo se entenderá mejor la explicación anterior. Imagine que tenemos un rango de celdas con nombre con los siguientes datos:

Text Date Currency Double Entero Long
K25OP 23/12/85 $123,25 1.254,45 58.945
65KIO 12/03/99 $12.547,69 KDFJ8 125.478
65874 K34DLE K94ADR 1.544,58 698.745
ADEK98 DE58 KDI03 HY609
47845,89 $1.478,25
WSDE 25/03/77 $87.458,96 25.698,47

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:

Text Date Currency Double Entero Long
K25OP 23/12/85 123,25 1254,45 58945
65KIO 12/03/99 12547,69 125478
1544,58 698745
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 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:

Text Date Currency Double Entero Long
K25OP 23/12/85 123,25 1254,45 58945
65KIO 12/03/99 12547,69 KDFJ8 125478
65874 K34DLE K94ADR 1544,58 698745
ADEK98 DE58 KDI03 HY609
47845,89 1478,25
WSDE 25/03/77 87458,96 25698,47

 
     
     
 

4. Cómo especificar los nombres de las hojas y rangos

Para tener acceso a los datos contenidos en una hoja de cálculo o en un libro de trabajo, tendremos que especificar el subconjunto de datos que deseamos utilizar. Dependiendo de la versión de Microsoft Excel disponible, podremos acceder a los siguientes subconjuntos de datos:

- Archivos de hoja de cálculo:

  • Hoja completa
  • Un rango de celdas con nombre
  • Un rango de celdas sin nombre

- Libro de trabajo:

  • Hoja de cálculo individual
  • Un rango de celdas con nombre de cualquier parte del libro de trabajo
  • Un rango sin nombre de una hoja de cálculo individual

En cuanto a los argumentos DATABASE y source que hay que proporcionar en la cadena de conexión, las convenciones que se utilizan para especificar los nombres de las hojas y rangos son los que se detallan a continuación:

Para tener acceso a ...

Tipo de archivo Versiones de Excel Parámetros
DATABASE source
Hoja de cálculo completa Hoja de cálculo 3.0 y 4.0 ruta de acceso del archivo de la hoja de cálculo [nombre_archivo#xls]
Libro de trabajo 5.0, 7.0 y 8.0 ruta de acceso del archivo del libro de trabajo, incluyendo el nombre de dicho archivo [nombre_hoja$]
Rango de celdas con nombre Todos Todas ruta de acceso del archivo de la hoja, incluyendo el nombre de dicho archivo [rango_con_nombre]
Rango de celdas sin nombre Hoja de cálculo 3.0 y 4.0 ruta de acceso del archivo de la hoja, incluyendo el nombre de dicho archivo [A1:Z50]
Libro de trabajo 5.0, 7.0 y 8.0 ruta de acceso del archivo del libro de trabajo, incluyendo el nombre de dicho archivo [nombre_hoja$A1:Z50]

El valor del rango especificado no puede exceder el número máximo de filas, columnas u hojas de la hoja de cálculo o del libro de trabajo.

 
     
     
 

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:

Versión ISAM Hoja de cálculo individual Libro de trabajo de múltiples hojas
Excel 3.0 No
Excel 4.0 No
  Versión de Microsoft Excel
Excel 5.0 Sólo para Microsoft Excel 5.0 y 7.0 (95)
Excel 8.0 Para libros de trabajo de hojas múltiples de Microsoft Excel 8.0 (97), 9.0 (2000) y 10.0 (2002)

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:

  • No se puede eliminar filas de las hojas de cálculo ni de los libros de trabajo.
  • Se puede borrar datos de celdas individuales de una hoja de cálculo, siempre y cuando no contenga fórmulas.
  • No se puede crear índices en las hojas ni en los libros de trabajo.
  • Si una hoja de cálculo o un libro de trabajo están codificados (tienen establecida una contraseña), no se podrá abrir ninguno de ellos, aunque se suministre el parámetro PWD en la cadena de conexión con la contraseña correcta, salvo que de la coincidencia que dicha hoja o libro estén actualmente abiertos en Microsoft Excel, cuando se establezca una conexión con la hoja de cálculo o el libro de trabajo. Por tanto, si tiene pensado utilizar una hoja de cálculo o un libro de trabajo para vincularlos o abrirlos mediante los objetos de acceso a datos, no establezca ninguna contraseña, ni a la hoja de cálculo 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 cálculo de Excel. Estas limitaciones incluyen, pero no se limitan:

  • Tamaño de la hoja de cálculo: 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. Vinculación de hojas de cálculo de Microsoft Excel

6.1. Vincular con ADOX

La 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

    Dim cnn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    ' Establezco la conexión 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 catálogo
    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 vínculo
    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

    ' Añado la tabla a la colección 'Tables'
    cat.Tables.Append tbl

End Sub

6.2. Vincular con DAO

Para 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
    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 información de conexión.
    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 vínculo
    db.TableDefs.Append td

End Sub

 
     
     
 

7. Abrir una hoja de cálculo de Microsoft Excel

7.1. Mediante ADO visualizando la información 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 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)

    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset

    ' Abro una conexión con una hoja de cálculo
    ' Si se desea utilizar los encabezados, hay que escribir
    ' la instrucción 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 conexión también se puede establecer mediante...
    ' cnn.Open "DRIVER={Microsoft Excel Driver (*.xls)};" & _
                                      "DBQ=C:\Mis documentos\Libro1.xls"

    ' 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 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)

    Dim db As Database
    Dim rs As DAO.Recordset

    ' Abro la hoja de cálculo
    Set db = OpenDatabase("C:\Mis documentos\Libro1.xls", False, False, "Excel 8.0;HDR=Yes;")

    ' Dependiendo de la opción 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]", 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 ejecución

Personalmente 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()

    ' 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 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 Excel

Si 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
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field

' Establecemos una conexión 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 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 Excel

Sin 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 TABLE

Con 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
Dim SQL As String

' Establecemos la conexión 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 conexión y liberamos los recursos asociados
cnn.Close

Set cnn = Nothing

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 tabla

Una 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
Dim sWorkSheetName As String
Dim sTableName As String
Dim 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 cálculo nueva mediante la instrucción SELECT...INTO
cnn.Execute _
    "SELECT * INTO [Excel 8.0;DATABASE=" & sExcelFileName & "].[" & _
         sWorkSheetName & "] FROM " & "[" & sTableName & "]"

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 ADOX

Disponiendo 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
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

' Establecemos una conexión, especificando sólo la ruta
' donde vamos a crear el nuevo libro de trabajo
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"
     ' Añadimos los nombres de las columnas
     ' de la primera fila
     .Columns.Append "IdCliente", adInteger
     .Columns.Append "Nombre", adVarWChar
     .Columns.Append "CIF", adVarWChar
End With

' Añadimos la tabla al catálogo
cat.Tables.Append tbl

' Cerramos la conexión y liberamos los recursos asociados
cnn.Close

Set cnn = Nothing

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 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 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
Dim sConnect As String, sSQL As String
Dim cnnActiva As ADODB.Connection

' Establezco la conexión 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;" & _
                         "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 conexión
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 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
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 paréntesis dejando sólo las comillas simples
' También 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 conexión
dbActiva.Close

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 Excel

Al 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 Access

En 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
Dim sConnect As String, sSQL As String
Dim cnnActiva As ADODB.Connection

' Establezco la conexión 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 conexión
cnnActiva.Close

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
Dim sConnect As String, sSQL As String
Dim db As Database

' Abro la hoja de cálculo
Set db = OpenDatabase("C:\Libro1.xls", False, False, "Excel 8.0; HDR=Yes;")

sTablaDestino = "[Tabla Importada desde Excel]"
sTablaOrigen = "[WorkSheet1$]"

' Exporto la tabla a una base de datos Access, especificando la ruta y la contraseña
' de la base de datos, y con la sintaxis que se muestra. Atención a las comillas simples
' en color rojo
sConnect = "''[;DATABASE=C:\Mis documentos\Bd1.mdb;PWD=Contraseña]"
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 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
Dim lNumRegAfect As Long
Dim strSQL As String

' Abrimos una conexión 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=Contraseña].TablaExcel " & _
"FROM [Rango1]"

' Ejecutamos la consulta
cnn.Execute strSQL, lNumRegAfect, adExecuteNoRecords

MsgBox "Número de registros afectados: " & lNumRegAfect

' Cerramos la conexión
cnn.Close

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 Excel

Si 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 activa

Si 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
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

' Conexión 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

    ' Añadimos los registros
    .Execute sSQL, , adCmdText

    .Close
End With

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 Excel

Al 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
Dim sConnect As String, sSQL As String
Dim db As Database

sTablaDestino = "[Tabla Importada desde Excel]"
sTablaOrigen = "[WorkSheet1$]"
sConnect = " 'C:\Mis documentos\Bd1.mdb' "

' Construímos la consulta SQL
sSQL = "INSERT INTO " & sTablaDestino & " IN " & sConnect & " SELECT * FROM " & sTablaOrigen

' Abro la hoja de cálculo
Set db = OpenDatabase("C:\Libro1.xls", False, False, "Excel 8.0; HDR=Yes;")

' Añadimos los registros
db.Execute sSQL

' Cierro la conexión
db.Close

11.3. Exportar datos de una hoja de cálculo Excel a otra hoja de cálculo

El 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
Dim sConnect As String, sSQL As String
Dim cnn As ADODB.Connection

sTablaDestino = "[WorkSheet2$]"
sTablaOrigen = "[Rango1]"

' IMPORTANTE: A la hora de ejecutar el código, elimine los paréntesis.
' Se han incluido para que el lector observe la utilización de comillas simples
sConnect = "(')(') [Excel 8.0;HDR=No;DATABASE=C:\Libro2.xls]"

' Construimos la consulta
sSQL = "INSERT INTO " & sTablaDestino & " IN " & sConnect & " SELECT * FROM " & sTablaOrigen

' Establezco la conexión 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. Cómo insertar una fila de datos en una hoja de cálculo

En 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)
    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á 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
Dim SQL As String

' Establecemos una conexión 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 añadidos
' para un sólo registro
SQL = "INSERT INTO [Hoja1$] (IdSocio, Nombre, [Fecha Alta]) " & _
          "VALUES (1277, 'González González, Pedro', #05/23/2003#)"

' Ejecutamos la consulta
cnn.Execute SQL

' Cerramos la conexión y liberamos los recursos asociados
cnn.Close
Set cnn = Nothing

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:

Error de secuencia de ordenación 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 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 datos

Tambié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
    SET Campo1=Valor1, Campo2=Valor2, ... CampoN=ValorN
    WHERE criterio

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álculo

Si 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
Dim cnn As ADODB.Connection

' Establezco la conexión con la hoja de cálculo
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 específico
sSQL = "UPDATE " & sTablaDestino & " SET Salario = Salario + (Salario * 3.5/100)"

' Ejecutamos la consulta
cnn.Execute sSQL

' Cierro la conexión
cnn.Close

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 individualmente

A 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
Dim sSQL As String
Dim cnn As ADODB.Connection

' Establezco la conexión 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 conexión
cnnActiva.Close

 
     
     
 

13. Establecer una conexión mediante el driver ODBC para Excel

Cuando 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
Dim rs As ADODB.Recordset

' Conexión con la hoja de cálculo 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 conexión
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, 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 Base

La 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.

 
     
 
Artículo Tipo Descripción
General    
257819   HOWTO Usar ADO con datos de Excel desde Visual Basic o VBA
303814   HOWTO Usar ADOX con datos de Excel desde Visual Basic o VBA
278973   EJEMPLO El ejemplo ExcelADO demuestra cómo utilizar ADO para leer/escribir datos en un libro de trabajo de Excel
195951   HOWTO

Consultar y actualizar datos de Excel utilizando ADO desde ASP

Transferir Datos a Excel
247412   INFO Métodos 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 automatización
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 más 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 conexión 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 operación debe utilizar una consulta actualizable" cuando accede a Excel a través 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 tamaño 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 configuración del encabezado
194124   PRB Excel devuelve valores como NULL usando el método OpenRecordset de DAO
189897   XL97 Datos truncados a 255 caracteres con el driver ODBC de Excel
246167   PRB Error de secuencia de ordenación al abrir por primera vez un Recordset ADODB junto con un archivo de Excel
109376     Se produce error de conversión de tipos cuando importa datos desde Microsoft Excel
       

 
     
     
 

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