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

 

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: