Indice: 1. El parámetro HDR y la supresión de los encabezados 2. Limitaciones a tener en cuenta 3. Vinculación de hojas de cálculo de Microsoft Excel 3.1. Vincular con ADOX 3.2. Vincular con DAO 4. Abrir una hoja de cálculo de Microsoft Excel 4.1. Mediante ADO visualizando la información en un DataGrid 4.2. Mediante DAO mostrando los datos en un DBGrid 5. Cómo crear una hoja de cálculo Excel mediante SQL 6. Importar una hoja de cálculo a una base de datos Access 7. Importar los datos existentes en una hoja de cálculo Excel 7.1. Desde la propia base de datos Access activa 7.2. Añadir registros a la tabla desde la propia hoja de cálculo Excel 8. Establecer una conexión mediante el driver ODBC para Excel Utilizar objetos de acceso a datos con hojas de cálculo de Excel ================================================ Mediante los controladores IISAM, 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. En éste artículo voy a utilizar los dos objetos de acceso a datos más utilizados (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 la que corresponde con las últimas versiones de Microsoft Excel. 1. 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 entónces 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 incluídos o no en el primer registro de la tabla u objeto "recordset", a fin de evitar sorpresas desagradables. El siguiente ejemplo, demuestra una conexión ADO con un libro de trabajo de Excel. (Nótese las comillas dobles en la cadena de conexión, porque se diferencia de DAO en cuánto a su utilización): Dim cnn As New ADODB.Connection ' Abro una conexión con una hoja de cálculo, la ' cual utiliza la primera fila como nombres de los ' campos 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;") 2. 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 Sí No Excel 4.0 Sí 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. 3. Vinculación de hojas de cálculo de Microsoft Excel -------------------------------------------------- 3.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 tant, 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 New ADODB.Connection Dim cat As ADOX.Catalog Dim tbl As ADOX.Table ' Establezco la conexión con la base de datos actual 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 3.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 4. Abrir una hoja de cálculo de Microsoft Excel -------------------------------------------- 4.1. Mediante ADO visualizando la información en un DataGrid ---------------------------------------------------------- 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.» 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. A continuación, y mediante ADO, vamos a escribir un procedimiento que mostrará en un control DataGrid, la selección efectuada que deseamos abrir: Public Sub OpenExcelSheetWithADO (ByVal Option As Integer) Dim cnn As New ADODB.Connection Dim rs As New 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. 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 With rs .CursorLocation = adUseClient .CursorType = adOpenDynamic .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 4.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, abro el correspondiente rango 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 5. Cómo crear una hoja de cálculo Excel mediante SQL ---------------------------------------------------- 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. 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 New ADODB.Connection ' Datos por defecto sExcelFileName = "C:\Mis documentos\Libro1.xls" sWorksheetName = "WorkSheet1" sTableName = "Socios" ' Abro la base de datos 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 & "]" 6. 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 externa, y recurrir de nuevo a SQL para exportar la tabla desde dicha base de datos. Es decir, abrimos la base u hoja de cálculo, de donde queremos coger los datos, y exportamos la misma creando una nueva tabla en una base de datos Access. Más que importar sería exportar datos desde Excel para crear una nueva tabla en una base de datos Access. Mediante ADO sería así: Dim sTablaOrigen As String, sTablaDestino As String Dim sConnect As String, sSQL As String Dim cnnExterna As New ADODB.Connection ' Establezco la conexión con la base de datos externa cnnExterna.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]" ' Exporto la tabla a una base de datos Access sConnect = "'C:\Mis documentos\Bd1.mdb'" sSQL = "SELECT * INTO " & sTablaDestino & " IN " & sConnect & " FROM " & sTablaOrigen cnnExterna.Execute sSQL ' Cierro la conexión cnnExterna.Close Y mediante la biblioteca de DAO, sería de ésta manera: 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:\Mis documentos\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 sConnect = "'C:\Mis documentos\Bd1.mdb'" sSQL = "SELECT * INTO " & sTablaDestino & " IN " & sConnect & " FROM " & sTablaOrigen db.Execute sSQL ' Cierro la base de datos db.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. 7. Importar los datos existentes en 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 importar los datos mediante la instrucción INSERT INTO, lo que en SQL se conoce como una consulta de datos añadidos, la cual anexa los nuevos registros al final de la tabla o consulta ya existente. La tabla origen o 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. 7.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 cnnAccess As New 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:\Mis documentos\Libro1.xls]." & sTablaOrigen ' Conexión con la base de datos Accesss With cnnAccess .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Mis documentos\Bd1.mdb;" .Open sSQL = "INSERT INTO " & sTablaDestino & " SELECT * FROM " & sConnect ' Añadimos los registros .Execute sSQL, , adCmdText 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. 7.2. Añadir registros a la tabla 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», 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 db As Database Dim sTablaOrigen As String, sTablaDestino As String Dim sConnect As String, sSQL As String sTablaDestino = "[Nombre de la Tabla Access]" sTablaOrigen = "[Rango1]" sConnect = "'C:\Mis documentos\Bd1.mdb'" ' Abro la hoja de cálculo Set db = OpenDatabase("C:\Mis documentos\Libro1.xls", False, False, "Excel 8.0; HDR=Yes;") ' Añadimos los registros sSQL = "INSERT INTO " & sTablaDestino & " IN " & sConnect & " SELECT * FROM " & sTablaOrigen db.Execute sSQL 8. 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 primer punto, 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;Q288343 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. -- Enrique Martínez «Softjaen» Septiembre, 2002