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

 

La Shell de Windows

La SHELL de Windows es la interfaz gráfica de Microsoft Windows. Es donde se recoge toda la interfaz del usuario: el escritorio, la barra de tareas, explorador, etc.

Cuando usamos el sistema operativo como usuarios finales, trabajamos generalmente con aplicaciones, bien para navergar en internet, para redactar documentos, para hacer cálculos, etc.

Los programadores ,por contra,  tienen necesidades más avanzadas que los usuarios finales . Los programadores necesitan para compilar y ejecutar programas y automatizar tareas repetitivas.

La mayoría de los usuarios estamos familiarizados con la interfaz gráfica de usuario o GUI. La mayoría de los entornos GUI muestran un escritorio que se rellena con ventanas , iconos y menús . Se utiliza un puntero (dirigido por un ratón o trackball) para seleccionar los elementos en el escritorio. Estas interfaces son a veces llamados WIMP interfaces, después de las primeras letras de las palabras W_ indow, i_ con, m_ enu, y p_ ointer.

La otra interfaz de usuario es la shell de comandos . Un intérprete de comandos no es más que una ventana sin ningún tipo de decoración gráfica. Los comandos se muestran como texto. No hay ningún icono, y se puede usar el ratón sólo en circunstancias limitadas.

Uuna consola de comandos no es una buena interfaz de usuario para los usuarios ocasionales. Sin embargo, los usuarios experimentados encuentran que el shell de comandos, es indispensable para muchas tareas.

Vamos a ir poco a poco viendo cómo podemos agilizar nuestras macros implementando comandos de la shell de Windows wn nuestros códigos.

Por ejemplo en la siguiente macro vamos a crear un botón al que le vamos a asociar el código para abrir el bloc de notas:

1. Abra un libro nuevo.
2. Inicie el cuadro de controles.(Menú>Ver>barra de herramientas>Cuadro de controles)
3. Seleccione y dibuje un botón.
4. En tiempo de diseño haga doble click sobre el mismo e inserte  entre Private Sub CommandButton1_Click()  y End Sub el código

Dim RetVal
RetVal = Shell(«C:\Archivos de programa\Windows NT\Accesorios\Wordpad.exe», 1)    ‘ Ejecuta bloc de notas
AppActivate RetVal

Salga del modo diseño (clic en el icono escuadra ) vaya a excel y haga clic en el botón. Si lo ha hecho correctamente se le abrirá el bloc de notas.

Analizando el código, lo primero que hemos hecho es declara una variable tipo variant para recoger el número que identifica la tarea del programa que ejecuta la función Shell.

Esta función tiene además los argumentos siguientes:

• En primer lugar el nombre del programa a ejecutar y su ubicación exacta.
• En segundo lugar un número asociadoal estilo de la ventana en que se va a ejecutar el programa, que puede presentar los valors siguientes:

vbHide	0	Se oculta Windows y se pasa el foco a la ventana oculta. La constante vbHide no se aplica a plataformas Macintosh.
vbNormalFocus	1	Windows recupera el foco y vuelve a su posición y tamaño original.
vbMinimizedFocus	2	Windows se muestra como un icono con foco.
vbMaximizedFocus	3	Windows se maximiza con foco.
vbNormalNoFocus	4	Windows vuelve al tamaño y posición más recientes. La ventana activa actual permanece activa.
vbMinimizedNoFocus	6	Windows se muestra como un icono. La ventana activa actual permanece activa.

Si quisiéramos mostrar / acceder a la ventana de comandos deberíamos ejecutar la macro siguiente:

Sub muestra_cmd()
Dim RetornaVal
RetornaVal = Shell(«C:\WINDOWS\system32\cmd.exe», 1)

End Sub

Intente ejecutar un ejemplo similar como el anterior haciendo que se muestre mediante un botón la calculadora de Windows.

 

Excel y WebMail

Como continuación a la interoperatividad de Excel con los servicios de correos, en el presente post trataré de explicar como podemos mandar un libro, una hoja o un pequeño texto escrito en una hoja excel, usando correo webmail.

La primera macro nos va acomprobar el sistema de correo WebMail que tenemos instalado o si no tenemos ninguno:

Sub sistema_decorreo_instalado()
Select Case Application.MailSystem
    Case xlMAPI
        MsgBox «Está utilizando  Microsoft Mail»
    Case xlPowerTalk
        MsgBox «El sistema de correos es PowerTalk»
    Case xlNoMailSystem
        MsgBox «No tiene ningún sistema de correos instalado»
End Select
End Sub

En principio establecemos un flujo de control (Select Case) que va a verificar que clientes de correos tenemos en el equipo, y esto lo hacemos con la propiedad Application.MailSystem, esta es de sólo lectura y soporta tres constantes xlMAPI que verifica si estamos usando un Internet Message Access Protocol (IMAP) , si usamos el sistema de comandoz por voz de Microsoft PowerTalk o no tenemos nada instalado.

A continuación vamos a ver como podríamos enviar una copia de la hoja activa por correo webmail, en mi caso utilizando WebMail de Microsoft 2011:

Sub EnviarHojaPorCorreoElectrónico()
    ActiveSheet.Copy
    ActiveWorkbook.SendMail Recipients:=»fulanito@loquesea.com», Subject:=»Envío de hoja de Excel»
    ActiveWorkbook.Close savechanges:=False
End Sub

Bueno como podrá ver no tiene mucha complicación, hemos utilizado el método SendMail Recipients (algo así como » enviar correo al buzón»…) y poco más.

Supongamos que ahora tenemos una hoja con un texto escrito, y desamos enviar ese texto en concreto. Supongamos que en la celda E1 hemos escrito la dirección del destinatario y en la E2 la del remitente. Aquí va el código:

Function EnviarMails_CDO() As Boolean

    ‘ Creo la variable de objeto CDO
    Dim Email As CDO.Message
    Dim Autentificacion As Boolean

    ‘ ahora doy vida al objeto
    Set Email = New CDO.Message

    ‘indicamos los datos del servidor:
    Email.Configuration.Fields(cdoSMTPServer) = «smtp.gmail.com»
    Email.Configuration.Fields(cdoSendUsingMethod) = 2
    ‘indicamos el nro de puerto. por defecto es el 25, pero gmail usa el 465. hay otro

    Email.Configuration.Fields.Item _
        («http://schemas.microsoft.com/cdo/configuration/smtpserverport») = CLng(465)
    ‘aqui dejamos en claro si el servidor que usamos requiere o nó autentificación.
    ‘1=requiere, 0=no requiere. Para gmail, entonces, 1
    Email.Configuration.Fields.Item(«http://schemas.microsoft.com/cdo/» & _
                «configuration/smtpauthenticate») = Abs(1)
    ‘segundos para el tiempo maximo de espera. aconsejo no modificarlo:
    Email.Configuration.Fields.Item _
        («http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout») = 30

    ‘aqui defino como True (verdadera) a la autentificación para el envío de mails.
    Autentificacion = True
    ‘ahora configuramos las opciones de login de gmail:
    If Autentificacion Then
        ‘nombre de usuario
        Email.Configuration.Fields.Item _
            («http://schemas.microsoft.com/cdo/configuration/sendusername») = «fulanito@gmail.com»
        ‘contraseña
        Email.Configuration.Fields.Item _
            («http://schemas.microsoft.com/cdo/configuration/sendpassword») = «micontraseña de correo»
        ‘si el servidor utiliza SSL (secure socket layer). en gmail: True
       Email.Configuration.Fields.Item _
            («http://schemas.microsoft.com/cdo/configuration/smtpusessl») = True
    End If

        ‘a partir de ahora tomaremos los datos incluidos en el la hoja de excel:
        ‘ Dirección del Destinatario
        Email.To = Trim([e1].Value)

        ‘ Dirección del remitente
        Email.From = Trim([e2].Value)

        ‘ Asunto del mensaje
        Email.Subject = Trim([e3].Value)

        ‘ Cuerpo del mensaje
        Email.TextBody = Trim([e4].Value)

        ‘Ruta del archivo adjunto
        If [e5].Value <> vbNullString Then
            Email.AddAttachment (Trim([e5].Value))
        End If

        ‘antes de enviar actualizamos los datos:
        Email.Configuration.Fields.Update
        ‘colocamos un capturador de errores, por las dudas:
        On Error Resume Next
        ‘enviamos el mail
        Email.Send
        ‘si el numero de error es 0 (o sea, no existieron errores en el proceso),
        ‘hago que la función retorne Verdadero
        If Err.Number = 0 Then
           EnviarMails_CDO = True
        Else
          ‘caso contrario, muestro un MsgBox con la descripcion y nro de error
           MsgBox «Se produjo el siguiente error: » & Err.Description, vbCritical, «Error nro » & Err.Number
        End If

        ‘destruyo el objeto, para liberar los recursos del sistema
        If Not Email Is Nothing Then
            Set Email = Nothing
        End If
        ‘libero posibles errores
        On Error GoTo 0
    End Function

En principio para que pueda funcionar ha de activar la siguiente referencia: Microsoft (CDO for Windows 2000) . Para ver bien los comentarios y comprender el código debería insertarlo en un módulo y hacer las pruebas.

Quizás lo que cueste un poco más de entender son las líneas referentes a las configuraciones de los campos de correo. Necesitamos decirle al sistema como están configurados los campos referentes al remitente, destinatario etc. Y para ello debemos indicarle donde puede encontrar la información pertinente. Bien pues de la misma manera que a veces precisamos acudir a una biblioteca a buscar información de un tema en concreto, aquí hacemos lo mismo. Por ello en primer lugar debemos abrir la biblioteca de objetos CDO , biblioteca donde se encuentra las referencias que el sistema necesita sobre las interfaces de los programas de correos. Eso es lo que hacemos con la línea:

Email.Configuration.Fields.Item _
            («http://schemas.microsoft.com/cdo/configuration/sendusername»)

y siguientes.

En muchos casos no es necesario especificar esas líneas ya que CDO utilizaría por defecto el gestor de correo incluido en la máquina.

Siguiendo con los ejemplos, en la siguiente macro vamos mandar las celdas que seleccionemos de una hoja:

‘Este procedimeinto envía las celdas seleccionadas en el cuerpo del mensaje
‘Seleccione unas cuantas celdas antes de enviar el mensaje

Sub CDO_enviando_un_rango()
    Dim rng As Range
    Dim iMsg As Object
    Dim iConf As Object
    Dim Flds As Variant

    Set iMsg = CreateObject(«CDO.Message»)
    Set iConf = CreateObject(«CDO.Configuration»)

       iConf.Load -1    ‘ CDO valores por defecto
       Set Flds = iConf.Fields
        With Flds
        .Item(«http://schemas.microsoft.com/cdo/configuration/smtpusessl») = True
        .Item(«http://schemas.microsoft.com/cdo/configuration/smtpauthenticate») = 1
        .Item(«http://schemas.microsoft.com/cdo/configuration/sendusername») = «fulanito@gmail.com»
        .Item(«http://schemas.microsoft.com/cdo/configuration/sendpassword») = «contraseñadefulanito»
        .Item(«http://schemas.microsoft.com/cdo/configuration/smtpserver») = «smtp.gmail.com»

        .Item(«http://schemas.microsoft.com/cdo/configuration/sendusing») = 2
        .Item(«http://schemas.microsoft.com/cdo/configuration/smtpserverport») = 25
        .Update
    End With

    Set rng = Nothing
    On Error Resume Next

    Set rng = Selection.SpecialCells(xlCellTypeVisible)

    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox «La selección o no es un rango o la hoja está protegida» & _
               vbNewLine & «corrigalo e inténtelo de nuevo», vbOKOnly
        Exit Sub
    End If

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    With iMsg
        Set .Configuration = iConf
        .To = «fulanito@gmail.com»
        .CC = «»
        .BCC = «»
        .From = «»»FULANITO»» <fulanito@gmail.com>»
        .Subject = «Esto es una prueba»
        .HTMLBody = RangetoHTML(rng)
        .Send
    End With

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Hay que prestar especial cuidado con los firewalls e incluso con los antivirus. Debe verificar que no le están bloqueando las salidas.

En esta macro utilizamos una función VBA propia de Outlook pero que se traslada mediante CDO a Excel :HTMLBody = RangetoHTML(rng) es decir es una función que convierte a código HTML el rango que hayamos seleccionado.

EXCEL Y OUTLOOK

En el presente post vamos a examinar como trabajar conjuntamente con Outlook y Excel, implementando códigos en VBA. Si bien en la actualidad son cada vez más los usuarios de servicios de clouding y por ende correo web, todavía los clientes mail ocupan un espacio de uso  nada despreciable en muchos ordenadores.

Para automatizar Outlook, primero debe tener acceso a la modelo de objetos de Outlook.   Es decir, para manipular objetos de Outlook, primero debe ser capaz de «verlos».   En general hay dos maneras de exponer un modelo de objetos para su aplicación Excel, conocido como el enlace anticipado y el enlace OLB.

«Enlace» se refiere a la exposición del modelo de objetos del cliente a la aplicación host.   En nuestro caso, eso significa que la exposición del modelo de objetos de Outlook para nuestra aplicación en VBA para Excel, el   modelo de objetos de Outlook se almacena en un OLB (biblioteca de objetos) de archivos.

Para poder tener acceso a la librería de objetos de Outlook procederemos de la misma forma que con Word. Abra el edito VBA y desde la barra de menús Herramientas>Referencias>Microsoft Outlook x.0 (donde x.0 puede ser desde el 9.0 al 14.0, para los ejmplos de este post utilizaremos 14.0).

Vamos a utilizar una macro para ver si se ha establecido correctamente la referencia al objeto. Si todo va bien, se debe de arrancar Outlook y nos saldrá un MsgBox confirmándolo:

Sub probando_referencia()
Dim outlookApp As Outlook.Application
 Set outlookApp = New Outlook.Application
 MsgBox outlookApp.Name
End Sub

Otra forma:

Sub probando_referencia_bis()
    Dim outlookApp As Object

    Set outlookApp = CreateObject(«Outlook.Application»)

    MsgBox outlookApp.Name
End Sub

Utilizamos CreateObject cuando deseamos crear una nueva instancia de Outlook y GetObject para hacer referencia a una instancia en ejecución de Outlook.  El siguiente código utiliza el enlace para mostrar la carpeta predeterminada de la agenda de la instancia existente de Outlook y crea una instancia si no hay una ya existente:

Sub Mostrar_Calendario()
    ‘declaración de objetos
    Dim outlookApp As Object
    Dim olNs As Object

    ‘tratamiento en caso de error
    On Error Resume Next

    ‘recogemos la referencia al objeto Outlook
    Set outlookApp = GetObject(, «Outlook.Application»)

    ‘tratamiento en caso de error que no se pueda crear el objeto
    If Err.Number = 429 Then
        Set outlookApp = CreateObject(«Outlook.application»)
    End If

    On Error GoTo 0
    ‘obteniendo el calendario de la carpeta por defecto
    Set olNs = outlookApp.GetNamespace(«MAPI»)
    ‘si no aparece activamos el explorador
    If outlookApp.ActiveExplorer Is Nothing Then
        outlookApp.Explorers.Add _
            (olNs.GetDefaultFolder(9), 0).Activate
    Else
    ‘en caso contrario lo mostramos
        Set outlookApp.ActiveExplorer.CurrentFolder = _
            olNs.GetDefaultFolder(9)
        outlookApp.ActiveExplorer.Display
    End If
    ‘vaciamos las variables de objeto
    Set olNs = Nothing
    Set outlookApp = Nothing

End Sub

Aunque en posteriores posts trataré el tema de los errores, a fin de poder tener un listado de  errores «previsibles» en ejecución desde el editor pulse F1 (ayuda) y busque
Errores interceptables, podrá ver y analizar diferentes tipos de errores con el número que VBA le asocia.

Algo más complicado sería automatizar el envío de e-mails desde Excel utilizando Outlook. Bueno para el nivel de este blogg_curso quizás es algo avanzado, pero intente leer los comentarios y comprender el código:

Sub mandar_rango_por_correo()
    ‘declaramos las variables
    ‘el rango de origen Source
    Dim Source As Range
    ‘el libro a enviar como miLibro
    Dim miLibro As Workbook
    Dim wb As Workbook
    ‘nombre de la ruta del fichero temporal
    Dim TempFilePath As String

    ‘nombre del fichero temporal
    Dim TempFileName As String

    ‘variables de apoyo para la extensión y formato de fichero
    Dim FileExtStr As String
    Dim FileFormatNum As Long

    ‘variables para crear los objetos aplicación y correo
    Dim OutApp As Object
    Dim OutMail As Object

    ‘inicializamos las variables
    Set Source = Nothing

    ‘si hay error salta a la linea siguiente
    On Error Resume Next

    ‘seleccionamos el rango
    Set Source = Range(«A1:K50»).SpecialCells(xlCellTypeVisible)

    ‘desactivamos cualquier controlador de errores
    On Error GoTo 0

    If Source Is Nothing Then
        MsgBox «El origen no es un rango o la hoja está protegida. » & _
               «Por favor corrígalo «, vbOKOnly
        Exit Sub
    End If
    ‘aceleramos la ejecución del código
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ‘añadimos un nuevo libro con una sola hoja en el mismo
    ‘de esa forma mandamos una copia del original
    Set wb = ActiveWorkbook
    Set miLibro = Workbooks.Add(xlWorksheet)

    ‘copiamos el origen
    Source.Copy
    With miLibro.Sheets(1) ‘ahora en la hoja 1
        .Cells(1).PasteSpecial Paste:=8 ‘pego el ancho de la columna (a 8)
        .Cells(1).PasteSpecial Paste:=xlPasteValues ‘pego los valores
        .Cells(1).PasteSpecial Paste:=xlPasteFormats ‘pego el formato
        .Cells(1).Select
        Application.CutCopyMode = False
    End With

    ‘utilizo la función Environ para recoger una variable de Windows y asigno el nombre
    TempFilePath = Environ$(«temp») & «\»
    TempFileName = «Selection de » & wb.Name & » » _
                 & Format(Now, «dd-mmm-yy h-mm-ss»)

    ‘según la versión coloco la extensión del fichero
    If Val(Application.Version) < 12 Then
        ‘ Caso de Excel 2000 ó 2003 .xls
        FileExtStr = «.xls»: FileFormatNum = -4143
    Else
        ‘ Caso de  Excel 2007 ó 2010.
        FileExtStr = «.xlsx»: FileFormatNum = 51
    End If

    ‘entrando con Outlook
    Set OutApp = CreateObject(«Outlook.Application»)
    Set OutMail = OutApp.CreateItem(0)

    ‘salvo mi fichero
    With miLibro
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        With OutMail
        ‘rellenando los datos del envío
            .To = «enriquecabell@gmail.com»
            .CC = «»
            .BCC = «»
            .Subject = «Libro de prueba»
            .Body = «Bueno aquí va adjunto el fichero»
            .Attachments.Add miLibro.FullName
            .Send
        End With
        On Error GoTo 0
        .Close SaveChanges:=False
    End With
    ‘ya como no lo necesitamos nos cargamos los ficheros temporales
    Kill TempFilePath & TempFileName & FileExtStr

    ‘vaciando variables
    Set OutMail = Nothing
    Set OutApp = Nothing
    ‘poniendo la casa en orden
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

Excel y Word

Todas las aplicaciones Office de Microsoft utillizan el mismo lenguaje VBA; la diferencia entre ellos es su modelo de objeto. esto facilita que cualquier el acceso entre ellas siempre y cuando estén instaladas.

Para el caso que nos ocupa, cómo acceder a Word mediante excel, precisamos acceder a la biblioteca de objetos de Word:

biblioteca de objetos

Un archivo con una extensión de nombre de archivo .OLB que proporciona a los controladores de Automatización OLE (como Visual Basic) información sobre los objetos de Automatización OLE disponibles. Puede utilizar el Examinador de objetos para examinar el contenido de una biblioteca de objetos y obtener información sobre los objetos que proporciona.

Para acceder a dicha biblioteca podemos hacerlo de dos formas:

• Mediante vinculación previa: la referencia al objeto se realiza cuando se compila el programa.
• Mediante vinculación posterior: en tiempo de ejecución.

Vinculación previa

El código se ejecuta más rápido, ya que se realiza una referencia al objeto antes de que se escriba el código. Para ello debemos añadir la biblioteca a la aplicación caso de que no esté disponible. Para ello:

1. Seleccionar Herramientas>Referencias.
2. Activar Micrsosoft Word 11.o Object Library.
3. Clic en Aceptar.

Si no se encuentra la biblioteca es que Word no está instalado. Si encuentra otra versión es que hay otra versión diferente de Word instalada.

Veamos un ejemplo de cómo abrir un documento Word desde Excel denominado testWord.doc y que lo tenemos en C:\ existente :

Sub prueba_dos_word()
‘Cramos una variable para la aplicación y otro para el documento
Dim wdApp As Word.Application
Dim wdDoc As Document

‘invocamos la aplicación y abrimos el documento
Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(ThisWorkbook.Path & «\testWord.doc»)
wdApp.Visible = True

‘vaciamos las variables
Set wdApp = Nothing
Set wdDoc = Nothing

End Sub

Si la versión de Word a la que se hacer referencia no existe, aparecerá un error de aplicación.

Vinculación posterior

Con este tipo de vinvulación creamos un objeto que hace referencia a la aplicación Word  antes de vincular con la biblioteca Word.

El siguiente ejemplo abre y hace visible el documento anterior:

Sub prueba_word()
‘debemos declarar una variable de objeto
Dim varDoc As Object

‘creamos el objeto
Set varDoc = CreateObject(«Word.application»)

varDoc.Visible = True ‘hacemos visible el documento
Sheets(«Hoja1»).Range(«A1:B1»).Copy ‘selecionamos y copiamos el rango señalado
varDoc.Documents.Add ‘añadimos hojas al documento
varDoc.Selection.Paste ‘pegamos la selección
varDoc.activedocument.SaveAs ThisWorkbook.Path & «/» & «testWord.doc» ‘guardamos el documento en el directorio raiz como…
varDoc.Documents.Close ‘cerramos  varDoc.Quit
Application.CutCopyMode = False
End Sub

La diferencia si la prueba es notable. Con este método no sabemos lo que está ocurriendo. No aparecen las ayudas visuales cuando se hace referencia a los objetos de Word.

Abrir documentos de Word

Para abrir un documento existente hay que utilizar el método Open. Admite varios parámetros entre ellos Read Only y AddtoRecentFile. Por ejemplo el siguiente código abre un documento existente en forma sólo lectura e impide que el archivo se añada a la lista de archivos recientes, bajo el menú Archivo:

Sub abrir_arc_word()

Documents.Open Filename:=»C:\testWord.doc», ReadOnly:=True, AddToRecentFiles:=False

End Sub

XML y Excel (Ejemplo práctico)

En el presente post vamos a reproducir un ejemplo práctico de Microsoft de como tratar con datos XML en Excel.

1. Descargue descargue el archivo de ejemplo XML.
2. Agregará una asignación a una hoja de cálculo y a continuación la llenará con los datos importados.

Empiece preparando la hoja de cálculo

1. Inicie Excel y abra el archivo de ejemplo.

Cuando aparezca el cuadro de diálogo Abrir XML, haga clic en Utilizar el panel de tareas Origen XML y en Aceptar.

2. En la celda A1, escriba Empleado y en la celda A3, escriba Administrador.
3. En la celda C1, escriba Departamento, y en la celda C3, escriba Fecha de presentación.
4. Aplique el formato que desee al texto para resaltarlo.

Agregue la asignación

1. Arrastre el elemento ns1:EmployeeName del panel de tareas Origen XML a la celda B1 y, a continuación, arrastre el elemento ns1:Department a la celda D1.
2. Arrastre el elemento ns1:ManagerName a la celda B3 y, a continuación, arrastre el elemento ns1:Date a la celda D3.
3. En ns1:ExpenseItem, seleccione ns1:Description y arrástrelo a la celda B5.
4. Aún en ns1:ExpenseItem: arrastre el elemento ns1:Account a la celda C5, arrastre ns1:Date a la celda D5 y, a continuación, arrastrens1:Total a la celda E5.
5. Guarde el archivo. Esto agregará la asignación al archivo y enlazará los datos del archivo de origen a la asignación.

Ahora ya podrá…

Importar datos a la asignación

1. Seleccionar cualquiera de las celdas asignadas. En el menú Datos, elija XML y, a continuación, haga clic en Importar.

Aparecerá el cuadro de diálogo Importar XML.

2. Use la lista Buscar en para localizar el archivo de ejemplo y haga clic en Importar.

Excel rellenará los campos asignados. Observe que Excel aplica automáticamente el formato de lista XML a los elementos repetitivos (en este caso, los elementos de gasto) en la asignación.

XML y Excel

Una de las ventajas a partir de la versión 2003 de Excel  es la mejora en la gestión de datos XML.

XML es un sistema estándar de codificación de información. Los programas que utilizan el formato XML pueden intercambiar fácilmente sus datos, ya que responden a una misma lógica interna.

Los documentos XML son ficheros de texto que contienen la información organizada en forma de árbol: cada rama puede tener unos atributos propios y servir de base para otras ramas. Además, los documentos XML se pueden transformar (por ejemplo, a formato HTML, para mostrar la información en una página web), o combinar: un tronco con todas sus ramas puede pasar a ser una rama de otro árbol mayor. Un ejemplo de documento XML abreviado podría ser:

   <juegos>
     <juego nombre="escondite" espacio="exteriores">
       <jugadores min="2" max="16" />
       <edades min="3" max="indefinido" />
       <descripcion>
          Los jugadores se reparten en dos equipos y...
       </descripcion>
     </juego>
     <juego nombre="ajedrez" espacio="interiores">
       .... etc.
     </juego>
   </juegos>

XML tiene unas reglas que hay que preservar y un alfabeto. Vamos a analizar como covertir un libro desde Excel a HTML y vieceversa utilizando XML.

Reglas

• Todo elemento de datos debe empezar y terminar con una etiqueta idéntica. Los nombres de etiquetas son sensibles a mayúsculas y minúsculas.
• El archivo XML debe empezar y terminar con una etiqueta raíz. En el archivo sólo puede existir una etiqueta raiz.
• Es válido tener una etiqueta vacía.
• Si se anidan etiquetas, la interior debe estar centrada antes de cerrar la exterior.

Alfabeto

Cómo consigue Excel leer correctamente los datos: existen dos tipos adicionales de archivo que mejoran a los archivos XML.

Los archivos XSD son archivos de esquema que definen la relación entre los datos y los requisitos de validación de los mismos. Los archivos XSL se denominan transformaciones o soluciones. Una transformación define cómo se deberían mapear los campos en el archivo XML en su documento.

Convertir HTML a EXCEL y viceversa con XML

Podemos guardar cualquier archivo xls en formato xlm. Los gráficos y los códigos vba no obstante se perderán, no así los formatos.

Para abrir un archivo XML:

Sub Abrir_XML()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML(«c:\ejemplo.xml»)

End Sub

 

Como lista podemos usar el siguiente código:

Sub Abrir_XML_FicheroLista()

Dim oWX As Workbook

Set oWX = Workbooks.OpenXML(Filename:=»c:\ejemplo.xml», LoadOption:=XlXmlLoadOption.xlXmlLoadImportToList)

End Sub

 

Trabajando con varias propiedades

Dado que la mayoría de objetos tienen diversas propiedades, a veces, necesitaremos efectuar varias acciones sobre un mismo objeto. Si tenemos un objeto con un nombre demasiado largo, a veces,  resulta engorroso tener que escribir una y otra vez dicho nombre para asociarle el método o propiedad que desamos asignar. A fin de facilitar un poco las cosas VBA nos ofrece utilizar la estructura With…End With.

Por ejemplo imagine que deseamos poner una fila entera en negrita, doble subrayado, fuente grande y color especial, el código prodría ser el siguiente:

Range(«A1:K1»).Select

Selection.Font.Bold=True

Selection.Font.Size=12

Selection.Font.ColorIndex=5

Selection.Font.Underline=xlUnderlineStyleDoubleAccounting

 

Podemos reescribirlo con la sintáxis expuesta teniendo en cuenta que todo lo que hace referencia al objeto en sí empieza con un punto:

With Range («A1:K1»).Font

.Bold=True

.Size=12

.ColorIndex=5

.Underline=xlUnderlineStyleDoubleAccounting

End With

Formularios: cuadro de lista y cuadro combinado

Un cuadro de lista y un cuadro combinado son controles similares pero con ligeras diferencias y sirven para permitir al usuario elegir entre varios elementos o insertar el valor que desee en una lista.

Por ejemplo:

Cuadro de lista. Muestra una lista de uno o más elementos de texto de entre los cuales puede elegir el usuario.

Cuadro combinado. Combina un cuadro de texto con un cuadro de lista para crear un cuadro de lista desplegable. Un cuadro combinado es más compacto que un cuadro de lista, pero requiere que el usuario haga clic en la flecha abajo para mostrar la lista de elementos. Utilice un cuadro combinado para permitir que el usuario escriba una entrada o elija un solo elemento de una lista. El control muestra el valor actual en el cuadro de texto, independientemente de cómo se haya insertado ese valor.

 

La propiedad predeterminada de un control ListBox es Value.

El evento predeterminado de un control ListBox es Click.

El siguiente ejemplo agrega y elimina el contenido de un control ListBox utilizando los métodos AddItem y RemoveItem y las propiedades ListIndex y ListCount.

Para utilizar este ejemplo, copie este código de ejemplo en la parte Declaraciones de un formulario. Asegúrese de que el formulario contiene:

• Un control ListBox llamado ListBox1.
• Dos controles CommandButton llamados CommandButton1 y CommandButton2.

 

Ejemplo:

 

Private Sub Command1_Click()
If Text1 = «» Then
MsgBox «Debe ingresar un nombre para poder agregar un elemento», vbQuestion + vbOKOnly, «Datos incompletos»
‘Salimos de la rutina ya que no se ha ingresado nada en el control text1
Exit Sub
End If

‘Agregamos el contenido del Text1 en el control List1
List1.AddItem Text1
End Sub

Private Sub Command2_Click()
‘Si la lista no está vacía entonces podemos eliminar
If List1.ListIndex <> -1 Then
‘Eliminamos el elemento que se encuentra seleccionado
List1.RemoveItem List1.ListIndex
End If
End Sub

 

Formularios: Check buttom / Option Buttom

El control CheckBox, o casilla de verificación, permite elegir una opción (activada/desactivada, True/False) que el usuario puede establecer o anular haciendo click.

Si un control CheckBox es dependiente de un origen de datos, al cambiar el valor, éste cambia el valor de su origen. Un control CheckBox deshabilitado muestra el valor actual, pero está atenuado y no permite realizar cambios al valor desde la interfaz de usuario.

La propiedad predeterminada de un control CheckBox es Value.

El evento predeterminado de un control CheckBox es Clic.

Control CheckBox. Ejemplo de las propiedades Enabled y Locked

El siguiente ejemplo demuestra las propiedades Enabled y Locked y cómo se complementan entre sí. Este ejemplo expone cada propiedad independientemente con un control CheckBox, para que observe los valores individuales y combinados. Este ejemplo también incluye un segundo control TextBox para que pueda cortar y pegar información entre los controles TextBox y comprueba las actividades admitidas por los valores de estas propiedades.

Nota Puede copiar la selección al Portapapeles utilizando CTRL+C y pegar utilizando CTRL+V.

Para utilizar este ejemplo, copie este código de ejemplo en la parte Declaraciones de un formulario. Asegúrese de que el formulario contiene:

• Un control TextBox llamado TextBox1.
• Dos controles CheckBox llamados CheckBox1 y CheckBox2.
• Un segundo control TextBox llamado TextBox2.

 

 

 

 

Private Sub CheckBox1_Change()

TextBox2.Text = «TextBox2»

TextBox1.Enabled = CheckBox1.Value

End Sub

 

Private Sub CheckBox2_Change()

TextBox2.Text = «TextBox2»

TextBox1.Locked = CheckBox2.Value

End Sub

 

Private Sub UserForm_Initialize()

TextBox1.Text = «TextBox1»

TextBox1.Enabled = True

TextBox1.Locked = False

CheckBox1.Caption = «Enabled»

CheckBox1.Value = True

CheckBox2.Caption = «Locked»

CheckBox2.Value = False

TextBox2.Text = «TextBox2»

End Sub

 

Formularios (Frame)

Es un contenedor de otros controles. Los agrupa para que resulten facilmente identificables para el usuario.

Para agrupar controles, en primer lugar trace el control Frame y, a continuación, meta los controles dentro de Frame. De este modo podrá mover al mismo tiempo el Frame y los controles que contiene. Si traza un control fuera del Frame y, a continuación, intenta moverlo dentro de éste, el control se colocará sobre el Frame, pero no pertenecerá a el.  Es decir, si es un OptionButton este se comportará como si estuviese fuera del Frame, aunque físicamente esté dentro de el.

El evento predeterminado de un control Frame es Click.

Crear un formulario

Para crear un formulario personalizado abriremos el editor VBA  e iremos a insertar->UserForm. Verá como en la ventana de código aparee ahora un pequeño formulario vacío y aparece el cuadro de herramientas.

A la derecha poedrá ver la ventana de propiedades. No obstante si no la visualiza pulse f4 o vaya a menú->ver->ventana de propiedades. También puede pulsar sobre el icono correspondiente en la barra de menús.

En dicha ventana podrá ver las propiedades inherentes al objeto form, siendo las que más le ineterese las siguientes:

Name->indica el nombre con el que vamos a identificar el objeto. Generalmente suele emplearse cualquier nombre significativo con el prefijo frm. Por ejemplo frmempleados

Caption->el nombre que va a visualizar el objeto en el caso del formulario arriba a la izquierda.

Border/ ->Back Color-> los colores de los bordes y el fondo.

Font->con que tipo de fuente se va a escribir en el formulario.

Enable-> por defecto a true si inicialmente queremos programar que el objeto no se vea debemos cambiarlo a false.

En el formulario podemos mediante arrastrar y soltar colocar diferentes controles desde la caja de herramientas o cuadro de controles. Aquí tenemos una lista de los principales controles:

Para definir la posición de un control, hay que modificar las propiedades left y top que nos indican la distancia entre el control y la esquina superior izquierda del formulario.

Para una explicación multimedia de como crear un formulario en VBA Excel puedes ver este vídeo .

Formularios

Los formularios  en general son documentos impresos o en línea diseñados  con un  formato y estructura estándar que facilita la captura, la organización y la edición de la información.

Los formularios  contienen controles,  objetos que muestran datos o hacen que sea más fácil para los usuarios entrar o editar los datos, realizar una acción o seleccionar una opción. En general, los controles facilitan el uso de los formularios. Algunos ejemplos de controles comunes son los cuadros de lista, los botones de opción y los botones de comando. Los controles también pueden ejecutar macros asignadas y responder a eventos, tales como clics del mouse, mediante la ejecución de código de VBA.

Resumen de controles de formulario

Nombre del botón	Ejemplo	Descripción
	Etiqueta		Identifica el propósito de una celda o un cuadro de texto, o muestra texto descriptivo (como títulos, leyendas, imágenes) o breves instrucciones.
	Cuadro de grupo		Agrupa controles relacionados en una unidad visual en un rectángulo con una etiqueta opcional. Generalmente, se agrupan botones de opción, casillas de verificación o contenido estrechamente relacionado.
	Botón		Ejecuta una macro que realiza una acción cuando un usuario hace clic en él. Los botones también se conocen como botones de comando.
	Casilla de verificación		Activa o desactiva un valor que representa una selección inequívoca entre opuestos. Puede seleccionar más de una casilla de verificación en una hoja de cálculo o en un cuadro de grupo. Una casilla de verificación puede tener uno de tres estados: activada, desactivada y mixta, lo que significa una combinación de los estados activada y desactivada (como en una selección múltiple).
	Botón de opción		Permite una única elección dentro de un conjunto limitado de opciones que se excluyen mutuamente; un botón de opción generalmente está contenido en un cuadro de grupo o un marco. Un botón de opción puede tener uno de tres estados: activado, desactivado y mixto, lo que significa una combinación de los estados activado y desactivado (como en una selección múltiple). Los botones de opción también se conocen como botones de radio.
	Cuadro de lista		Muestra una lista de uno o más elementos de texto de entre los cuales puede elegir el usuario. Use un cuadro de lista para mostrar grandes cantidades de opciones que varían en número o contenido. Existen tres tipos de cuadros de lista: Un cuadro de lista de selección única permite solamente una elección. En este caso, un cuadro de lista se asemeja a un grupo de botones de opción, a excepción de que un cuadro de lista puede controlar un gran número de elementos de manera más eficiente. Un cuadro de lista de selección múltiple permite una elección o elecciones contiguas (adyacentes). Un cuadro de lista de selección extendida permite una elección, elecciones y no contiguas, o inconexas.
	Cuadro combinado		Combina un cuadro de texto con un cuadro de lista para crear un cuadro de lista desplegable. Un cuadro combinado es más compacto que un cuadro de lista pero requiere que el usuario haga clic en la flecha abajo para mostrar una lista de elementos. Use un cuadro combinado para permitir que un usuario escriba una entrada o elija solamente un elemento de la lista. El control muestra el valor actual en el cuadro de texto, sin importar el modo en que dicho valor se haya proporcionado.
	Barra de desplazamiento		Se desplaza por un intervalo de valores cuando el usuario hace clic en las flechas de desplazamiento o arrastra el cuadro de desplazamiento. Además, se puede mover por una página (en un intervalo preestablecido) de valores haciendo clic en el área entre el cuadro de desplazamiento y cualquiera de las flechas de desplazamiento. Generalmente, el usuario también puede escribir un valor de texto directamente en un cuadro de texto o una celda asociados.
	Control de número		Aumenta o disminuye un valor, como un incremento numérico, una hora o una fecha. Para incrementar el valor, es necesario hacer clic en la flecha arriba; para disminuirlo, se debe hacer clic en la flecha abajo. Generalmente, el usuario también puede escribir un valor de texto directamente en un cuadro de texto o una celda asociados.

El objeto Range

Dentro de la coleción de objetos de Excel y tal como había expuesto anteriormente Range es casi la unidad básica con la que podemos trabajar. Y digo casi porque dentro de Range podemos encontrar un objeto como es Cells (celdas). A qué nos referimos cuando hablamos de Range:

• A un conjunto de celdas.
• A una celda en concreto.
• A toda una fila / columna.
• A celdas de otras hojas.

Como propiedades el objeto Range tiene las siguientes:

• ActiveCell->representa la primera celda activa de la ventana activa (windowactive) o especificada.
• Areas->todos los rangos de una seleción múltiple.
• Cells->representa a una celda o clección de ellas.
• Columns->representa las columnas de la hoja activa,o especificada, o del rango especificado.
• Entire column/row->la columna/fila  entera dentro del rango.
• End->la celda situada al final del rango especificado.
• Offset->la celda desplazada según un nº específico de fila/columna.

Formas de seleccionar celdas con Range:

Una celda sólo:

Range(«A1»).Select

Un conjunto de celdas contiguas dentro de la misma hoja:
Range(«A1:G5»).Select

Para seleccionar celdas no contiguas:
Range(«A1,A5,B4»).Select

Para seleccionar celdas no contiguas mas un conjunto contiguo:
Range(«A1,A5,B4:B8»).Select

Otro ejemplo:

La siguiente tabla muestra algunas referencias de estilo A1 utilizando el método Range.

Referencia Significado
Range(«A1») Celda A1
Range(«A1:B5») Celdas de la A1 a la B5
Range(«C5:D9,G9:H16») Selección de varias áreas
Range(«A:A») Columna A
Range(«1:1») Fila uno
Range(«A:C») Columnas de la A a la C
Range(«1:5») Filas de la uno a la cinco
Range(«1:1,3:3,8:8») Filas uno, tres y ocho
Range(«A:A,C:C,F:F») Columnas A, C y F

De todas las propiedades descritas tal vez una de las más usadas sea Offset, que nos permite seleccionar celdas contiguas a la celda activa según una determinada posición de filas y clumnas:

expresión.Offset(RowOffset, RowOffset)

RowOffset      Variant opcional. El número de filas (positivo, negativo o cero) que indica el desplazamiento del rango. Los valores positivos desplazan hacia abajo y los negativos, hacia arriba. El valor predeterminado es 0.

ColumnOffset      Variant opcional. El número de columnas (positivo, negativo o cero) que indica el desplazamiento del rango. Los valores positivos desplazan a la derecha y los negativos, hacia la izquierda. El valor predeterminado es 0.

ActiveCell.Offset(0,0)->nos determina la posición actual

Ejemplo:

Sub ejemplo()
‘ejemplos del empleo de range y cells
‘escribo Hola en varias celdas de la hoja 2
Worksheets(«Hoja2»).Range(«A1:B5») = «Hola»
‘lo mismo pero en celdas discontinuas
Worksheets(1).Range(«F1», «G5») = «Hi»
‘ahora en la hoja 2
Worksheets(«Hoja2»).Range(«J1, K3, H5») = «AAA»
ActiveCell.Offset(2, 2) = «Me desplazo»
End Sub

Si establece una variable de objeto para un objeto Range, puede manipular fácilmente el rango utilizando el nombre de la variable.

El siguiente procedimiento crea la variable de objeto miRango y, a continuación, asigna la variable al rango A1:D5 de la Hoja1 del libro activo. Las instrucciones posteriores modifican las propiedades del rango, sustituyendo el nombre de la variable por el objeto del rango.

Sub Aleatorio()  
Dim miRango As Range
Set miRango = Worksheets(«Hoja1»).Range(«A1:D5»)
miRango.Formula = «=ALEATORIO()»
miRango.Font.Bold = True
End Sub

Utilice la función Array para identificar un grupo de hojas. El siguiente ejemplo selecciona tres hojas del libro activo.

Sub Varias()  
Worksheets(Array(«Hoja1», «Hoja2», «Hoja4»)).Select
End Sub

Utilice la propiedad Rows o Columns para trabajar con filas o columnas enteras. Estas propiedades devuelven un objeto Range que representa un rango de celdas. En el siguiente ejemplo, Rows(1) devuelve la fila uno de la Hoja1. A continuación, la propiedad Bold del objeto Font del rango se establece en True.

Sub FilaNegrita()
Worksheets(«Hoja1»).Rows(1).Font.Bold = True
End Sub

La siguiente tabla muestra algunas referencias de fila y columna, utilizando las propiedades Rows y Columns.

Referencia Significado
Rows(1) Fila uno
Rows Todas las filas de la hoja de cálculo
Columns(1) Columna uno
Columns(«A») Columna uno
Columns Todas las columnas de la hoja de cálculo

Para trabajar con varias filas o columnas al mismo tiempo, cree una variable de objeto y utilice el método Union, combinando varias llamadas a la propiedad Rows o Columns. El siguiente ejemplo cambia a negrita el formato de las filas uno, tres y cinco de la hoja de cálculo uno del libro activo.

Sub VariasFilas()
Worksheets(«Hoja1»).Activate
Dim miUnión As Range
Set miUnión = Union(Rows(1), Rows(3), Rows(5))
miUnión.Font.Bold = True
End Sub
Método abreviado para hacer referencia a rangos

Podemos utilizar también corchetes cuando hacemos referencias a rangos. Utilizando corchetes, así:

 

En vez de Range(«D5») podemos utilizar [D5]

En vez de Range(«A1:D5») podemos utilizar [A1:D5]

Range(«A1:D5″,»G6:I17»)   —>[A1:D5,G6:I17]

El objeto WorkSheet(hoja)

El objeto Worksheets representa a las hojas de un libro de Excel. El acceso al mismo puede ser:

• La hoja activa–> ActiveSheet
• Llamada a la hoja 1–>WorkSheets(«Hoja1»)
• Llamada a la primera hoja de la colección–>WorkSheets(1)
• Llamada a la última hoja de la colección.–>WorkSheets(WorkSheets.Count)
• Llamada a una hoja de otro libro.–>

Workbooks(«Libro1»).Worksheets(«Hoja1»)
Workbooks(«Libro2»).Worksheets(«Hoja1»)

Propiedades: a excepción de «name» todas son de lectura.

• .Name–>Nombre de la hoja.
• .Index–>Numero de la hoja dentro del índice numérico de la colección.
• .Count–>Devuelve el número de hojas dentro de la colección.
• .Next/previous–>Devuelve la hoja siguiente/anterior.
• .Used Range–>Devuelve un objeto range con el rango de la hoja que tiene datos.

Ejemplo: la siguiente macro devuelve el nombre de las hojas del libro.

Sub Muestra_mis_hojas()
    Dim mihoja As Worksheet

    For Each mihoja In Worksheets
        MsgBox mihoja.Name
    Next mihoja

End Sub

Eventos:

• .Activate–>activa la hoja
• .calculate–>reclacula la hoja especificada.
• .Delete–>Borra la hoja.
• .Protect–>Protege la hoja señalada.

Ejemplo: macro para proteger la hoja activa.

Sub ProtectSheet()

    ActiveSheet.Protect

End Sub

El objeto APPLICATION

El objeto Application representa la aplicación completa Microsoft Excel. Como todo objeto tiene una serie de propiedades :

Propiedad	Devuelve
Application.	UserName	Nombre del usuario
	OrganizationName	Nombre de la empresa
	OperatingSystem	Sistema operativo
	Version	Versión de MS Excel
	ProductCode	Código de MS Excel
	StandardFont	Fuente por defecto
	StandardFontSize	Tamaño fuente por defecto
	DecimalSeparator	Carácter separador de miles
	ActivePrinter	Impresora por defecto
	DefaultFilePath	Ruta de acceso por defecto
	UserLibraryPath	Ruta a carpeta Add-Ins

Ejemplo de macros del objeto Application: en el siguiente ejemplo listamos algunas de las propiedades descritas.

Sub ejemplo()

Dim i As Byte
Dim primero, ultimo As Variant
Dim uno, dos, tres, cuatro, cinco As String
Dim matriz

uno = Application.UserName
dos = Application.OperatingSystem
tres = Application.ActiveWorkbook.Name
cuatro = Application.Version
cinco = Application.LibraryPath

matriz = Array(uno, dos, tres, cuatro, cinco)

For i = 1 To 5
Cells(i, 1) = matriz(i)
Next
End Sub

Los eventos del objeto Application se suelen dar generlamente al crear o abrir un libro, cuando cambia una hoja, o se crea o modifica una tabla dinámica.

NewWorkbookSheetActivate SheetBeforeDoubleClick SheetBeforeRightClick SheetCalculate SheetChange SheetDeactivate SheetFollowHyperlink SheetSelectionChange SheetPivotTableUpdate WindowActivate WindowDeactivate

WindowResizeWorkbookActivate WorkbookAddinInstall WorkbookAddinUninstall WorkbookBeforeClose WorkbookBeforePrint WorkbookBeforeSave WorkbookDeactivate WorkbookNewSheet WorkbookOpen WorkbookPivotTableCloseConnection WorkbookPivotTableOpenConnection

Ejemplo: Crearemos un libor nuevo con ese nombre.
Sub NuevoLibro()

Workbooks.Add
Application.ActiveWorkbook.SaveAs («Mi_nuevo_libro»)
End Sub

 

Abrir un libro existente: para abrir un libro utilizaremos la siguiente Macro.

      Sub AbrirLibro()

         Workbooks.Open («C:\Mis documentos\Ejemplo.xls»)

      End Sub