Excel y bases de datos (Oracle)

Como continuación de posts anteriores, en esta ocasión vamos a analizar como establecer una conexión entre Excel y una base de datos Oracle, que nos permita hacer una consulta devolviendo los datos a Excel. Ya comenté que son diversas las tareas que se pueden efectuar entre una base de datos y Excel. Así que para tener un primer contacto y repasar las diferencias que existen entre unos SGBD y otros, he preferido no adentrarme demasiado en otro tipo de operaciones.

Como hacíamos en otras macros, es necesario como paso previo tener activadas las referencias oportunas: Microsoft ActiveX Data Objects 2.x. Este ejemplo ha sido desarrollado con la versión Oracle® Database Express Edition. Para ello he creado una pequeña tabla “EMPLEADOS” con tres campos ID, NOMBRE y EDAD.

Veamos el código:

Sub excel_oracle()
  ‘Definiendo las variables para la conexión, reordset, columnaumnas y filas
  Dim conexion As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim columna As Integer
  Dim fila As Integer

  ‘creando la conexión a la base de datos y al recordset
  Set conexion = New ADODB.Connection
  Set rs = New ADODB.Recordset

   ‘parámetros de la conexión
  conexion.Open ( _
     “User ID=system” & _
    “;Password=root” & _
    “;Data Source=XE” & _
    “;Provider=MSDAORA.1″)

  ‘sentencia SQL sacando todos los datos de la tabla EMPLEADOS
  rs.Open “select * from EMPLEADOS”, conexion

  ‘vamos a presentar los resultados poniendo el contador a cero
  ”””””””””””””””””””””””””””””””’
  ‘Colocamos los datos empezando por os títulos de las columnas”
  ‘                                                            ”
  ”””””””””””””””””””””””””””””””’
  columna = 0

  ‘ En la primera fila:los nombres de las columnas
  Do While columna < rs.Fields.Count
    Cells(1, columna + 1) = rs.Fields(columna).Name
    columna = columna + 1
  Loop

  ‘Ahora los datos obtenidos de la selección
  fila = 1
  Do While Not rs.EOF
    fila = fila + 1
    columna = 0

    Do While columna < rs.Fields.Count
      Cells(fila, columna + 1) = rs.Fields(columna).Value
      columna = columna + 1
    Loop

    rs.MoveNext
  Loop

End Sub

El fragmento de código referente a la conexión, nos marca los parámetros que se precisan para realizar la misma: el usuario (en este caso system) , la contraseña (root) el origen de los datos (la base de datos XE) y el proveedor   OLEDB de Microsoft para Oracle que es MSDAORA.1.

Después lanzamos la orden SQL y presentamos los datos con dos bucles, uno para la cabecera, y otro para los datos. Y aquí el resultado:

 

Excel y bases de datos (MySQL)

Como continuación del post anterior, en este vamos a tratar la conexión de Excel con el gestor de bases de datos MySQL. Las operaciones que se pueden realizar entre una hoja Excel y una base de datos pueden ser variadas,tanto como para trabajar un tutorial aparte. No obstante se pueden abordar las principales:

  • Crear/eliminar una base de datos.
  • Crear/eliminar tablas y campos.
  • Hacer consultas generales o específicas.
  • Insertar datos o borrarlos.

En el post anterior traté de como crear una base de datos en Access, con sus tablas y campos; y el volcado posterior de datos de Excel hacia Access. Más adelante iré tratando el resto de operaciones que podemos efectuar entre los dos programas de Microsoft, mediante VBA.

Por ahora vamos a analizar como hacer operaciones con MySQL.

Si lo analizan bien, casi siempre efectuámos los mismos pasos:

  1. Debemos crear el espacio de trabajo, donde debemos especificar a Excel el motor que se precisa para hacer la conexión al gestor de bases de datos.
  2. Tenemos que marcar las referencias específicas para que Excel la carge y la tenga disponible en su biblioteca.
  3. Preparamos las variables para:
  • la conexión
  • servidor
  • usuario
  • contraseña
  • nombre de la base con la que trabajar
  • las tablas y campos en su caso.

Una vez tratado los datos, es decir ejecutada la petición, cerrarmos la conexión y vaciamos las variables objeto.

Bien en el caso que nos ocupa y, atendiendo al primer punto, debemos cargar la referencia a la librería Microsoft Active X Data Objects 2.x .

Lo más probable es que no tenga descargado los drivers para la conexión con MySQL. Para ello vaya a esta dirección “MySQL ODBC 3.51 Driver”. Le aconsejo que escoga Windows (x86, 32-bit), MSI Installer Connector-ODBC  para 32-bit y en caso de 64 Windows (x86, 64-bit), MSI Installer Connector-ODBC. Una vez descargadodoble clic en el MSI y escoga instalación típica.Una vez terminada la instalación, puede verificar la misma en Panel de Control>Herramientas Administrativas>Orígenes de datos ODBC. Clic en la pestaña Drivers y búsquelo en la lista:

A continuación le expongo comentado el código para volcar los datos de una base de datos de MySQL a Excel.  En el ejemplo la base que se ha empleado se llama “directorio” y la tabla “example”. Se ha utilizado un servidor local, con lo que la dirección del host es la 127.0.0.1. Para poder practicar le aconsejo que inserte una nueva base de datos, sino tiene una ya creada, con una tabla y unos pocos datos.

 
‘Para empezar y como paso previo cargamos la referencia a la librería Microsoft Active X Data Objects 2.x
‘Se presupone instalado el driver MySQL ODBC 3.51

‘Comenzando

Sub excelMySQL()
‘variable para la conexión
Dim conexion As New ADODB.Connection

‘idem para el nombre del servidor en este caso estoy empleando una versión en local
Dim miservidor As String

‘ahora la base de datos en este caso se llama directorio y la tabla example
Dim bd As String

‘variables para usuario y contraseña (no la creo porque no la tengo en el servidor, en otro caso hay que crearla
Dim user As String

‘variables para la tabla que tiene sólo tres campos
Dim i As Long, tabla, nombre, edad, consulta As String

‘el recordset
Dim rs As ADODB.Recordset

‘———————————————————————-

‘Estableciendo la conexión

‘———————————————————————-

miservidor = “127.0.0.1”
bd = “directorio”
user = “root”

Set conexion = New ADODB.Connection ‘ con esto utilizamos la conexión indicada más arriba

conexion.Open “DRIVER={MySQL ODBC 3.51 Driver}” _
& “;SERVER=” & miservidor _
& “;DATABASE=” & bd _
& “;UID=” & user _
& “;OPTION=16427″

‘————————————————————————

‘A partir de aquí si todo ha ido bien podemos realizar varias acciones
‘Crear o eliminar tablas
‘Añadir campos
‘En este caso vamos a extraer datos a Excel
‘————————————————————————

Set rs = New ADODB.Recordset
consulta = “SELECT * FROM example”
rs.Open consulta, conexion, adOpenStatic

With Worksheets(1).Cells
.ClearContents
.CopyFromRecordset rs
End With

‘Cerramos la conexión
On Error Resume Next
rs.Close
Set rs = Nothing
conexion.Close
Set conexion = Nothing
On Error GoTo 0

End Sub

Habrá podido observar una línea :  rs.Open consulta, conexion, adOpenStatic. Quisiera comentarla un poco. El objeto Recordset es el interface entre los datos obtenidos de nuestras consultas sobre las tablas y nuestras páginas excel. Representa una tabla organizada en filas (registros) y columnas (campos). La propiedades y métodos de Recordsets pasan por entender el funcionamiento de otros dos objetos claves:

El  cursor  o puntero que nos permite desplazarnos por los registros del recordset. Dependiendo del tipo elegido determinaremos los desplazamientos y cambios realizables en los datos.

El tipo de cursor lo definiremos mediante la propiedad CursorType, los posibles valores son:

El otro objeto es el cierre o tipo de bloqueo que efectuaremos en la base de datos cuando modifiquemos un recordset, a fin de evitar que dos o mas usuarios accedan a modificar un mismo registro a la vez.

El tipo de cierre lo definiremos mediante la propiedad LockType, los posibles valores son:

Por último habrá podido observar el parámetro : & “;OPTION=16427″. Esto nos asegura que los valores numéricos de tipo large son interpretados correctamente por Excel.

Excel y bases de datos (Access)

En este post voy a intentar explicar como utilizar objetos para crear bases de datos, introducir datos, modificar su contenido o crear consultas, mediante VBA Excel.

Excel ya lleva incorporada una serie de funciones para trabajar con bases de datos, que gestionan múltiples tareas para el usuario. Si queremos, no obstante, automatizar y potenciar estas tareas debemos hacer uso de VBA.

Antes que nada es necesario aclarar algunos conceptos claves.

Las aplicaciones utilizan unos mecanismos para conectarse a bases de datos (en adelante BD). Son los ActiveX Data Objects (ADO),.

Con ADO, un programa puede leer, insertar, editar, o borrar, la información contenida dentro de la base de datos. Además, se puede manipular la propia base para crear nuevas tablas, como también alterar o eliminar las ya existentes, entre otras cosas.

ADO sustituyó a DAO y a RDO (para BD remotas)  que eran los sistemas previos que se usaban.

La última versión de ADO, creada por Microsoft, se llama ADO.NET, y se usa en los entornos de programación de la plataforma .NET, de Microsoft, para manejar bases de datos tanto en Windows como en la Web mediante ASP.NET, que es la nueva versión del ASP para la plataforma.NET.

Comenzaremos para aquellos que trabajan con versiones antiguas de BD, trabajando con DAO.

Para utilizar los objetos de acceso a datos, lo primero que debemos hacer es crear una referencia a estos objetos. Para ello recuerde dentro del editor VBA vamos a  Herramientas-Referencias-Microsoft DAO 3.6  Object Library.

Cuando cargamos esta referencia se crea un primer objeto del que se derivan los demás con los que vamos a trabajar: DBEngine, a partir del mismo obtendremos los espacios de trabajo  (Workspace) donde se encontrarán cada una de las BD (database).

Una vez creada la BD obtendremos las TableDef por cada tabla que creemos con sus respectivos campos (Fields) y el índice de referencia de cada tabla (Index).

Al objeto de poder realizar los ejemplos sería conveniente que cree una carpeta por ejemplo C:\VisualExcel.

Supongamos que tenemos un libro con unos datos del que vamos a crear una base de datos. Podríamos insertar un botón “Crear BD” e insertarle el código siguiente:

 

Private Sub CommandButton1_Click()

Dim bd As Database

Set bd = CreateDatabase(“C:\VisualExcel\bd1.mdb”, dbLangGeneral)

End Sub

 

Si no tiene activada la referencia comprobará que no en la selección del tipo de variable objeto, no le aparece la opción Database y por lo tanto tendrá un error.

Con el método CreateDatabase escribimos como argumentos la ruta y el nombre del fichero a crear. Si no escribimos nada en la extensión tomará el tipo por defecto que es Acces “mdb”.

 

Si en vez de crear manualmente la carpeta desea automatizarlo debería crear los procedimientos para verificar la ruta / archivo y en su caso crear la BD. Veamos:

 

Procedimiento para crear la carpeta

Sub NuevaCarpeta(ruta As String, nombrecpt As String)

‘Verificamos que el directorio existe

If Dir(ruta, vbDirectory + vbHidden) <> “” Then

 ‘comprobamos que en dicha ruta no existe ya una carpeta con el mismo nombre aunque esté oculta

    If Dir(ruta & “\” & nombrecpt, vbDirectory + vbHidden) = “” Then

‘ si todo es correcto la creamos en la ruta y con el nombre especificados

    MkDir ruta & “\” & nombrecpt

mensaje:     MsgBox (“Archivo creado: ” & ruta & “\” & nombrecpt)

    Else: MsgBox “La carpeta ya existe”

   End If

Else: MsgBox “Directorio no existente”

End If

End Sub

 

Y para crear la BD

Private Sub CommandButton1_Click()

Dim bd As Database

On Error Resume Next

NuevaCarpeta “C:\”, “VisualExcel”

Set bd = CreateDatabase(“C:\VisualExcel\bd1.mdb”, dbLangGeneral)

End Sub

 

 

Revise las funciones Medir y Dir, que  realmente son comandos de MS-DOS adaptados a VBA.

 

Bien hemos creado la BD pero como comprobará está vacía. Por lo tanto hemos de crear las tablas, los campos y los índices.

 

 

Para las tablas emplearemos el TableDef y para los campos Field como tipos.

Para crear los campos, al igual que en Access debemos indicar el nombre y su tipo.

 

Para crear la tabla emplearemos :

Set  td= bd.CreateTableDef(“Nombre_para_la_tabla”)

 

Y para los campos, algo similar:

Set fldnombre= td.CreateField(“Nombre”,dbTex,8) es decir el nombre del campo, el tipo según Access y su longitud.

 

Por último escribiríamos las sentencias para añadir los campos a la tabla, y la tabla a la base de datos.

 

Veamos como queda todo junto:

 

Private Sub CommandButton1_Click()

Dim bd As Database

Dim td As TableDef

Dim fldalias, fldnombre, fldapellidos, flddireccion, fldpoblacion, fldtelefono, fldnacimiento As Field

 

On Error Resume Next

‘creando la carpeta de ejemplo

NuevaCarpeta “C:\”, “VisualExcel”

‘creando la base de datos

Set bd = CreateDatabase(“C:\VisualExcel\bd1.mdb”, dbLangGeneral)

 

‘ahora la tabla

Set td = bd.CreateTableDef(“MiTabla”)

 

‘creando los campos

Set fldalias = td.CreateField(“Alias”, dbText, 8)

Set fldnombre = td.CreateField(“Nombre”, dbText, 30)

Set fldapellidos = td.CreateField(“Apellidos”, dbText, 50)

Set flddireccion = td.CreateField(“Direccion”, dbText, 50)

Set fldpoblacion = td.CreateField(“Poblacion”, dbText, 50)

Set fldtelefono = td.CreateField(“Telefono”, dbInteger, 9)

Set fldnacimiento = td.CreateField(“Fecha_nacimiento”, dbDate)

 

‘añadiendo los campos a la tabla

td.Fields.Append fldalias

td.Fields.Append fldnombre

td.Fields.Append fldapellidos

td.Fields.Append flddireccion

td.Fields.Append fldpoblacion

td.Fields.Append fldtelefono

td.Fields.Append fldnacimiento

 

‘añadiendo la tabla

bd.TableDefs.Append td

 

End Sub

 

Si abre ACCESS verá  que se ha creado la base de datos, con la tabla y los campos correspondientes. También comprobará que lo ha hecho en modo Access 2000.

Una forma alternativa y más cómoda podría ser crear los campos con un bucle With…wend. En el ejemplo siguiente podrá ver cómo además creamos un campo autoincrementable:

‘Creando la base de datos
Sub base_datos()
‘creamos las variables objeto para el espacio de trabajo
Dim ws As Workspace

‘la base de datos a la que llamaré bd
Dim bd As Database
‘y la única tabla de la bd que se llamara td
Dim td As TableDef
‘para los campos uno por cada columna
Dim id, apellidos, nombre, direccion, pob, tel As Field
‘creando los objetos
Set ws = DBEngine.Workspaces(0)
Set bd = ws.CreateDatabase(“C:\mi_bd”, dbLangGeneral)
Set td = bd.CreateTableDef(“MiTabla”)

‘necesito crear un campo contador para ello lo creo aparte de los otros
Set id = td.CreateField(“Id”, dbLong)
id.Attributes = dbAutoIncrField
td.Fields.Append id
‘creando y añadiendo  el resto de campos de una sóla vez
With td.Fields

    .Append td.CreateField(“Alias”, dbText, 30)
    .Append td.CreateField(“apellidos”, dbText, 30)
    .Append td.CreateField(“nombre”, dbText, 30)
    .Append td.CreateField(“dirección”, dbText, 50)
    .Append td.CreateField(“población”, dbText, 30)
    .Append td.CreateField(“tel”, dbLong)
End With

‘añadiendo la tabla a la base de datos

bd.TableDefs.Append td

If Error = “” Then
MsgBox “Base de datos creada”

End If

End Sub

Bien hasta ahora lo que hemos hecho es crear la base, las tablas etc Pero todavía no hemos volcado los datos a Access.  Veamos como podriamos hacerlo:

‘Exportando los datos con DAO
Sub exportar_datos()
Dim bd As Database, rs As Recordset, r As Long, x As Long, td As TableDef

‘abriendo la base de datos
Set bd = OpenDatabase(“C:\mi_bd.mdb”)

‘abriendo recordset
Set rs = bd.OpenRecordset(“MiTabla”, dbOpenTable)

‘recogiendo todos los campos en una tabla
r = 2 ‘empiezo en la fila 2 de la hoja 1

Do While Len(Range(“A” & r).Formula) > 0
‘repetir hasta la primera celda vacía de  la columna A

With rs
    .AddNew
    .Fields(“Alias”) = Range(“B” & r).Value
    .Fields(“apellidos”) = Range(“C” & r).Value
    .Fields(“nombre”) = Range(“D” & r).Value
    .Fields(“dirección”) = Range(“E” & r).Value
    .Fields(“población”) = Range(“F” & r).Value
    .Fields(“tel”) = Range(“G” & r).Value

    .Update
End With
r = r + 1

Loop

x = rs.RecordCount

‘cerramos
rs.Close
Set rs = Nothing

bd.Close
Set bd = Nothing

  If Error = “” Then
MsgBox “Exportación correcta se han creado ” & x & ” registros.”

End If

End Sub

Si en algún momento dado quisiéramos eliminar la base usaríamos la función Kill:

‘Para elminarla
Sub eliminar_bd()
Kill (“C:\mi_bd.mdb”)

End Sub