Excel y bases de datos(Oracle)1 OCTUBRE, 2011DEJA UN COMENTARIOComo continuacin de posts anteriores, en esta ocasin vamos a analizar como establecer una conexin entre Excel y una base de datosOracle,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 hacamos 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 versin Oracle Database Express Edition. Para ello he creado una pequea tabla EMPLEADOS con tres campos ID, NOMBRE y EDAD.

Veamos el cdigo:Sub excel_oracle() Definiendo las variables para la conexin, reordset, columnaumnas y filas Dim conexion As ADODB.Connection Dim rs As ADODB.Recordset Dim columna As Integer Dim fila As Integer creando la conexin a la base de datos y al recordset Set conexion = New ADODB.Connection Set rs = New ADODB.Recordset parmetros de la conexinconexion.Open( _ User ID=system & _ ;Password=root & _ ;Data Source=XE & _ ;Provider=MSDAORA.1) sentencia SQL sacando todos los datos de la tabla EMPLEADOSrs.Openselect * from EMPLEADOS, conexion vamos a presentar los resultados poniendo el contador a cero Colocamos los datos empezando por os ttulos de las columnas columna = 0 En la primera fila:los nombres de las columnas Do While columna Orgenes de datos ODBC.Clic en la pestaa Drivers y bsquelo en la lista:

A continuacin le expongo comentado el cdigo 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 direccin 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 librera Microsoft Active X Data Objects 2.xSe presupone instalado el driver MySQL ODBC 3.51ComenzandoSub excelMySQL()variable para la conexinDim conexion As New ADODB.Connectionidem para el nombre del servidor en este caso estoy empleando una versin en localDim miservidor As Stringahora la base de datos en este caso se llama directorio y la tabla exampleDim bd As Stringvariables para usuario y contrasea (no la creo porque no la tengo en el servidor, en otro caso hay que crearlaDim user As Stringvariables para la tabla que tiene slo tres camposDim i As Long, tabla, nombre, edad, consulta As Stringel recordsetDim rs As ADODB.Recordset-Estableciendo la conexin-miservidor = 127.0.0.1bd = directoriouser = rootSet conexion = New ADODB.Connection con esto utilizamos la conexin indicada ms arribaconexion.OpenDRIVER={MySQL ODBC 3.51 Driver} _& ;SERVER= & miservidor _& ;DATABASE= & bd _& ;UID= & user _& ;OPTION=16427A partir de aqu si todo ha ido bien podemos realizar varias accionesCrear o eliminar tablasAadir camposEn este caso vamos a extraer datos a ExcelSet rs = New ADODB.Recordsetconsulta = SELECT * FROM examplers.Openconsulta, conexion, adOpenStaticWith Worksheets(1).Cells.ClearContents.CopyFromRecordset rsEnd WithCerramos la conexinOn Error Resume Nextrs.CloseSet rs = Nothingconexion.CloseSet conexion = NothingOn Error GoTo 0End SubHabr podido observar una lnea :rs.Openconsulta, conexion, adOpenStatic. Quisiera comentarla un poco.El objeto Recordset es el interface entre los datos obtenidos de nuestras consultas sobre las tablas y nuestras pginas excel. Representa una tabla organizada en filas (registros) y columnas (campos).La propiedades y mtodos 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 elcierreo 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 parmetro :& ;OPTION=16427.Esto nos asegura que los valores numricos de tipo large son interpretados correctamente por Excel.FILED UNDEREXCEL Y BASES DE DATOSTAGGED WITHMYSQLExcel y bases de datos(Access)26 SEPTIEMBRE, 2011DEJA UN COMENTARIOEn este post voy a intentar explicar como utilizar objetos para crearbases de datos,introducir datos, modificar su contenido o crear consultas, medianteVBA Excel.Excel ya lleva incorporada unaserie de funciones para trabajar con bases de datos, que gestionan mltiples 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 losActiveXData Objects(ADO),.Con ADO, un programa puede leer, insertar, editar, o borrar, la informacin contenida dentro de la base de datos. Adems, se puede manipular la propia base para crear nuevas tablas, como tambin 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 versin de ADO, creada por Microsoft, se llamaADO.NET, y se usa en los entornos de programacin de la plataforma.NET, de Microsoft, para manejar bases de datos tanto en Windows como en la Web medianteASP.NET, que es la nueva versin delASPpara laplataforma.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 aHerramientas-Referencias-Microsoft DAO 3.6 Object Library.Cuando cargamos esta referencia se crea un primer objeto del que se derivan los dems con los que vamos a trabajar:DBEngine,a partir del mismo obtendremos los espacios de trabajo (Workspace) donde se encontrarn 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 sera 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. Podramos insertar un botn Crear BD e insertarle el cdigo siguiente:Private Sub CommandButton1_Click()Dim bd As DatabaseSet bd = CreateDatabase(C:\VisualExcel\bd1.mdb, dbLangGeneral)End SubSi no tiene activada la referencia comprobar que no en la seleccin del tipo de variable objeto, no le aparece la opcin Database y por lo tanto tendr un error.Con el mtodo CreateDatabase escribimos como argumentos la ruta y el nombre del fichero a crear. Si no escribimos nada en la extensin tomar el tipo por defecto que es Acces mdb.Si en vez de crear manualmente la carpeta desea automatizarlo debera crear los procedimientos para verificar la ruta / archivo y en su caso crear la BD. Veamos:Procedimiento para crear la carpetaSub NuevaCarpeta(ruta As String, nombrecpt As String)Verificamos que el directorio existeIf Dir(ruta, vbDirectory + vbHidden) Thencomprobamos 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 & \ & nombrecptmensaje: MsgBox (Archivo creado: & ruta & \ & nombrecpt) Else: MsgBox La carpeta ya existe End IfElse: MsgBox Directorio no existenteEnd IfEnd SubY para crear la BDPrivate Sub CommandButton1_Click()Dim bd As DatabaseOn Error Resume NextNuevaCarpeta C:\, VisualExcelSet bd = CreateDatabase(C:\VisualExcel\bd1.mdb, dbLangGeneral)End SubRevise las funciones Medir y Dir, que realmente son comandos de MS-DOS adaptados a VBA.Bien hemos creado la BD pero como comprobar est vaca. 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 segn Access y su longitud.Por ltimo escribiramos las sentencias para aadir 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 DatabaseDim td As TableDefDim fldalias, fldnombre, fldapellidos, flddireccion, fldpoblacion, fldtelefono, fldnacimiento As FieldOn Error Resume Nextcreando la carpeta de ejemploNuevaCarpeta C:\, VisualExcelcreando la base de datosSet bd = CreateDatabase(C:\VisualExcel\bd1.mdb, dbLangGeneral)ahora la tablaSet td = bd.CreateTableDef(MiTabla)creando los camposSet 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)aadiendo los campos a la tablatd.Fields.Append fldaliastd.Fields.Append fldnombretd.Fields.Append fldapellidostd.Fields.Append flddirecciontd.Fields.Append fldpoblaciontd.Fields.Append fldtelefonotd.Fields.Append fldnacimientoaadiendo la tablabd.TableDefs.Append tdEnd SubSi abre ACCESS ver que se ha creado la base de datos, con la tabla y los campos correspondientes. Tambin comprobar que lo ha hecho en modo Access 2000.Una forma alternativa y ms cmoda podra ser crear los campos con un bucleWithwend. En el ejemplo siguiente podr ver cmo adems creamos un campo autoincrementable:Creando la base de datosSub base_datos()creamos las variables objeto para el espacio de trabajoDim ws As Workspacela base de datos a la que llamar bdDim bd As Databasey la nica tabla de la bd que se llamara tdDim td As TableDefpara los campos uno por cada columnaDim id, apellidos, nombre, direccion, pob, tel As Fieldcreando los objetosSet 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 otrosSet id = td.CreateField(Id, dbLong)id.Attributes = dbAutoIncrFieldtd.Fields.Append idcreando y aadiendo el resto de campos de una sla vezWith td.Fields .Append td.CreateField(Alias, dbText, 30) .Append td.CreateField(apellidos, dbText, 30) .Append td.CreateField(nombre, dbText, 30) .Append td.CreateField(direccin, dbText, 50) .Append td.CreateField(poblacin, dbText, 30) .Append td.CreateField(tel, dbLong)End Withaadiendo la tabla a la base de datosbd.TableDefs.Append tdIf Error = ThenMsgBox Base de datos creadaEnd IfEnd SubBien hasta ahora lo que hemos hecho es crear la base, las tablas etc Pero todava no hemos volcado los datos a Access. Veamos como podriamos hacerlo:Exportando los datos con DAOSub exportar_datos()Dim bd As Database, rs As Recordset, r As Long, x As Long, td As TableDefabriendo la base de datosSet bd = OpenDatabase(C:\mi_bd.mdb)abriendo recordsetSet rs = bd.OpenRecordset(MiTabla, dbOpenTable)recogiendo todos los campos en una tablar = 2 empiezo en la fila 2 de la hoja 1Do While Len(Range(A & r).Formula) > 0repetir hasta la primera celda vaca de la columna AWith rs .AddNew .Fields(Alias) = Range(B & r).Value .Fields(apellidos) = Range(C & r).Value .Fields(nombre) = Range(D & r).Value .Fields(direccin) = Range(E & r).Value .Fields(poblacin) = Range(F & r).Value .Fields(tel) = Range(G & r).Value .UpdateEnd Withr = r + 1Loopx = rs.RecordCountcerramosrs.CloseSet rs = Nothingbd.CloseSet bd = Nothing If Error = ThenMsgBox Exportacin correcta se han creado & x & registros.End IfEnd SubSi en algn momento dado quisiramos eliminar la base usaramos la funcinKill:Para elminarlaSub eliminar_bd()Kill (C:\mi_bd.mdb)End Sub