Procesar serie de Archivos

Uno de los usos mas comunes que le damos a las macros es el de repetir una operación un determinado numero de veces. El ejemplo que exponemos a continuación nos muestra como manejar diferentes archivos almacenados en el disco.

Lo primero que hacemos es dar las especificaciones que deben de cumplir los archivos que queremos manejar, en este caso de texto.

Como nota importante, al iniciar el procedimiento y al no saber que cantidad de archivos nos puede dar la búsqueda, utilizamos la instrucción ReDim para redimensionar la matriz preservando (Preserve) los datos.

Sub ProcesoMatriz()

Dim ArchivoSpec As String

Dim i As Integer

Dim NombreArchivo As String

Dim ListaArchivos() As String

Dim ArchivosEncontrados As Integer

ArchivoSpec = ThisWorkbook.Path & "\" & "*.txt"

NombreArchivo = Dir(ArchivoSpec)

    If NombreArchivo <> "" Then

        ArchivosEncontrados = 1

        ReDim Preserve ListaArchivos(1 To ArchivosEncontrados)

        ListaArchivos(ArchivosEncontrados) = NombreArchivo

    Else

        MsgBox "No se encontraron archivos que coinciden con " & ArchivoSpec

        Exit Sub

    End If

   

    Do

        NombreArchivo = Dir

        If NombreArchivo = "" Then Exit Do

        ArchivosEncontrados = ArchivosEncontrados + 1

        ReDim Preserve ListaArchivos(1 To ArchivosEncontrados)

        ListaArchivos(ArchivosEncontrados) = NombreArchivo & "*"

        Loop

       

    For i = 1 To ArchivosEncontrados

        Call ProcesaArchivo(ListaArchivos(i))

    Next i

End Sub

La función Dir nos devuelve  el nombre de un archivo, directorio o carpeta que coincide con el patrón o atributo de archivo especificado.

En este caso, importamos el archivo e introducimos una serie de formulas de resumen que describen los datos del archivo.

Los archivos que cumplen la especificación dada se almacenan en la Matriz ListaArchivos, el procedimiento utiliza el bucle For – Next para procesarlos. Dentro del bucle llamamos al procedimiento ProcesarArchivo.

El procedimiento ProcesarArchivo  importa el archivo y luego inserta las cinco formulas.

Sub ProcesaArchivo(NombreArchivo As String)

Workbooks.OpenText Filename:=NombreArchivo

Range("D1").Value = "A"

Range("D2").Value = "B"

Range("D3").Value = "C"

Range("E1:E3").Formula = "=COUNTIF(A:A,D1)"

Range("F1:F3").Formula = "=SUMAIF(A:A,D1,B:B)"

End Sub

El método Workbooks.OpenText Carga y redistribuye un archivo de texto en forma de libro nuevo con una sola hoja que contiene los datos redistribuidos del archivo de texto.

Cualquier duda al respecto de lo comentado en el post no dudéis en exponerlo.

Función Shell de VBA

La función Shell de VBA nos facilita considerablemente el inicio de otros programas. En el siguiente ejemplo que presento a continuación nos permite iniciar la aplicación Command Prompt (cmd).

Sub AbrirAplicacion()

Dim programa As String

Dim Ide As Double

    On Error Resume Next

programa = "cmd.exe"

Ide = Shell(programa, vbNormalFocus)

    If Err <> 0 Then

        MsgBox "No se puede iniciar " & programa, vbCritical, " Error"

    End If

End Sub

La función Shell nos devuelve el número de identificación de la tarea para la aplicación. Posteriormente utiliza este número para activar la aplicación, el segundo argumento determina la forma de mostrar la aplicación, en este caso vbNormalFocus  hace que la aplicación se ejecuta normalmente y reciba el enfoque.

Si la función Shell no funciona genera un error, por eso empleamos una declaración On Error para mostrar el mensaje de “No se puede iniciar cmd.exe”.

Es importante saber que mientras se ejecuta la aplicación que hemos iniciado si el procedimiento tiene mas instrucciones estas continúan ejecutándose, es decir no tiene un intervalo de tiempo constante entre cada evento.

Comunicación con Access

Al igual que hicimos con Word en una publicación anterior esta vez lo haremos con Access utilizando como referencia “microsoft activex data objects 6.0 library” en mi caso, aunque vosotros podréis tener una versión inferior si utilizáis Excel 2007.

Cada biblioteca utiliza memoria y reduce el rendimiento, no utilicemos bibliotecas que sabemos que no son necesarias para nuestra aplicación.

Después de especificar la biblioteca, estamos listos para iniciar el código que nos comunicara con la base de datos.

Para que nos sea más sencillo reconocer cada una de los objetos y propiedades que utilizamos analizaremos paso a paso (análisis forense) cada línea de código.

Abrimos el procedimiento.

Sub ExportarDB()

Declaramos los objetos ADO (ActiveX Data Objects ) que utilizaremos.

Dim Datos As New ADODB.Recordset

El objeto Recordset de ADO se utiliza para mantener un conjunto de registros de una tabla de base de datos.

En ADO, este objeto es el más importante y el más usado para manipular los datos de una base de datos.

Dim Conexion As New ADODB.Connection

El objeto connection de ADO se utiliza para crear una conexión abierta a un origen de datos, a través de esta conexión, se puede acceder y manipular una base de datos.

Dim CaminoConexion As String

Dim InstruccionSQL As String

Desactivamos la actualización de la pantalla...

Application.ScreenUpdating = False

Seleccionamos la hoja donde copiaremos los datos.

   

Hoja1.Select  

Seleccionamos todos los datos de la tabla “Datos” con una instrucción SQL.

 

InstruccionSQL = "SELECT Datos.* FROM Datos"

CaminoConexion = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ThisWorkbook.Path & "\DBDatos.accdb"

La propiedad Provider (proveedor) establece o devuelve el nombre del proveedor.

La propiedad Source especifica una expresión de cadena que representa al objeto, que en este caso es la dirección de la DB donde tomara los datos.

   

Conexion.Open ConnectionString:=CaminoConexion

ConnectionString establece o devuelve los datos utilizados para crear una conexión con una fuente de datos.

Establecida la conexión abrimos la tabla donde tenemos los datos.

   

Datos.Open Source:=InstruccionSQL, ActiveConnection:=Conexion

If Datos.EOF = True Then

La función EOF devuelve False hasta que se haya llegado al final del archivo.

MsgBox "No se encontraron datos"

Else

Range("A3").CopyFromRecordset Data:=Datos

CopyFromRecordset copia el contenido de un objeto Recordset ADO o DAO en una hoja de cálculo…

Hoja1.Range("A:E").Columns.AutoFit

Esta línea autoajusta las columnas a los datos exportados de la DB

End If

Liberamos la memoria de datos.....

   

Conexion.Close Set Conexion = Nothing

Set Datos = Nothing

End Sub

Y ya tendríamos los datos listos en Excel para utilizarlos.

Animación en Excel

Junto a la creación de código VBA para los cálculos matemáticos y financieros, también es posible crear algunas divertidas aplicaciones en Excel VBA, incluyendo juegos y animación. Aunque los programadores profesionales pueden no estar interesados ​​en escribir aplicaciones, merece la pena que probar como un hobby y por satisfacción personal.

La animación se puede lograr cambiando la posición de un objeto continuamente mediante un procedimiento bucle. Dos propiedades o funciones que son necesarios para cambiar las posiciones o las coordenadas del objeto son las propiedades izquierda y superior. La propiedad Left especifica la distancia entre el borde izquierdo del objeto en píxeles del borde izquierdo de la pantalla y la propiedad Top especifica la distancia del borde superior del objeto de la frontera superior de la pantalla.

Por ejemplo, el siguiente código hace que el objeto se mueva de izquierda a derecha y luego de vuelta a la izquierda de nuevo en repetidas ocasiones.　

Sub Animacion1()

repetir:

With ActiveSheet.Shapes.Range(Array("1 Picture"))

.Left = .Left + 1

DoEvents

If .Left > 200 Then .Left = 1

End With

GoTo repetir

End Sub

Si desea mover el objeto hacia arriba y hacia abajo, cambiar el código anterior por el siguiente:

Sub Animacion2()

repetir:

With ActiveSheet.Shapes.Range(Array("1 Picture"))

.Top = .Top + 1

DoEvents 

If .Top > 200 Then .Top = 1

End With

GoTo repetir

End Sub

Manejo de cadenas

Todas las funciones de manejo de cadenas en Visual Basic, como Left, Right, Instr, M i d y Len se puede utilizar en el editor de VB de nuestro Excel. El siguiente ejemplo ilustra el uso de todas estas funciones.

La función InStr

InStr es una función que busca y devuelve la posición de una cadena en una frase.

Private Sub cadena_InStr()

Dim frase As String

Dim Posición As Integer

frase = InputBox("Inserte una frase…")

Posición = InStr(frase, InputBox("Indique la letra de la que quiere averiguar su posición"))

MsgBox Posición

End Sub

La función  Left  (izquierda)

Left es una función que extrae los caracteres de una frase, empezando por la izquierda.

Left (frase, 4) significa que cuatro caracteres son extraídos del valor de variable frase, a partir de la posición más a la izquierda.

Private Sub CadenaLeft()

Dim frase As String

frase = "Esta es la prueba de la function Left"

MsgBox Left(frase, 4)

End Sub
　

Este código devuelve la cadena "Esta" de la frase "Esta es la prueba de la function Left"

La función Right (derecha)

Right es una función que extrae los caracteres de una frase, comenzando por la derecha.

Right (frase, 5) significa que cinco caracteres son extraídos de la variable frase, a partir de la posición más a la derecha.

Private Sub CadenaRight()

Dim frase As String

frase = "Esta es la prueba de la función Right"

MsgBox Right(frase, 5)

End Sub

Este código devuelve la cadena " Right " de la frase " Esta es la prueba de la función Right”

La función Mid

Mid es una función que extrae una cadena de una frase, a partir de la posición especificada por el segundo parámetro en el soporte.

Mid (frase, 8,3) significa que una serie de tres caracteres son extraídos de la frase, a partir de la 8 ª posición de la izquierda, incluido el espacio vacío.

Private Sub CadenaMid()

Dim frase As String

frase = "Esta es la prueba de la función Mid"

MsgBox Mid(frase, 12, 6)

End Sub

Este código devuelve la cadena "prueba" de la frase "Esta es la prueba de la función Mid"

La función Len

Len es una función que devuelve la longitud de una frase (incluido el espacio vacío entre los dos)

      Private Sub CadenaLen()

      Dim frase As String

      frase = "Esta es la prueba de la función Len"

      MsgBox Len(frase)

      End Sub

En este caso nos dará como resultado 35.

Comunicación con Word

OLE (Object Linking and Embdding) es una tecnología que nos permite manipular objetos de otra aplicación directamente desde VBA Excel.

Para poder funcionar se necesita un servidor, llamado servidor OLE.  Este servidor no es más que una aplicación que nos permite manipular sus objetos.

Para tener acceso a los objetos de la aplicación con la que queremos interactuar se debe de referenciar de la siguiente manera:

Nos vamos a la opción Referencias del menú Herramientas y nos aparece el siguiente cuadro.

En él nos aparecen todos los servidores guardados con los que podemos trabajar, en este caso seleccionamos Microsof Word 12.0 Object Library  y   Microsoft ActiveX Data Objects 2.8 Library, puedes encontrarte con alguna versión superior dependiendo de la versión Office que utilices.

En el siguiente procedimiento veremos como pegar un gráfico de una hoja Excel  en un documento Word para presentarlo.

Sub CopiarWord()

Dim appWord As New Word.Application

Dim docWord As New Word.Document

Dim rng As Range

' Agregamos un nuevo documento....

With appWord

    .Visible = True

    Set docWord = .Documents.Add

    .Activate

End With

 With appWord.Selection

    ' Le damos formato al documento......

    .HomeKey unit:=wdLine, Extend:=wdExtend

    .ParagraphFormat.Alignment = wdAlignParagraphCenter

    .Font.Size = 18

    With .Font

    .Name = "Verdana"

    .Size = 18

    .Bold = True

    .Italic = False

    .Smallcaps = True

    End With

' Copiar el grafico de excel.......

ActiveSheet.ChartObjects(1).Activate

ActiveChart.ChartArea.Select

ActiveChart.ChartArea.Copy

' Pegamos el grafico......

.TypeParagraph

.TypeParagraph

.Paste

End With

With docWord

'Guardamos el documento en la misma carpeta que el fichero Excel..

.SaveAs ThisWorkbook.Path & "\Prueba.doc"

' vista previa antes de imprimir

.PrintPreview

End With

Set appWord = Nothing

End Sub

Objetos Chart

Una Gráfica está formada por multitud de objetos y cada uno de éstos con sus propiedades y métodos que le son propios. Esto implica que la manipulación de Gráficas en Excel con VBA puede ser tarea compleja.

En Excel podemos ubicar  un Gráfico en dos sitios:

• Como Objeto incrustado en una Hoja de Cálculo.

Public Sub EmbeddedChart()

  Dim mychart As Chart

  Dim rng As Range

  Set rng = Worksheets(1).Range("A2:E6")

  Set mychart = ActiveSheet.Shapes.AddChart(xlColumn).Chart

  mychart.SetSourceData Source:=rng

End Sub

• En una Hoja Gráfica.

Sub ChartSheet()

  Dim mychart As Chart

  Set mychart = ActiveWorkbook.Charts.Add

  mychart.SetSourceData Source:=Worksheets(1).Range("A2:E6"),_

PlotBy:=xlRows

End Sub

Jerarquía de Objetos Chart:

Este objeto Worksheet contiene un objeto ChartObject que a su vez contiene el objeto Chart. El objeto Chart tiene un Objeto ChartTitle (el Título del Gráfico) y este contiene una propiedad Text, que guarda el texto que se muestra en el Título.

Ejemplo: si queremos establecer el Título de un Gráfico incrustado ya creado, tendremos que codificar en VBA así:

WorkSheets(1).ChartObjects(1).Chart.ChartTitle.Text=”Ejemplo”

Un pequeño apunte sobre el método muy utilizado de SetSourceData

Está bien para establecer todas las series de datos de una sola toma, en la codificación es eficiente en términos de pequeña longitud, pero es inflexible en términos de su control sobre la salida que produce.

Exportar un libro al formato PDF

Hola a tod@s.

Hacemos un alto en el camino para atender una petición que me llego hace unos días, me preguntaban si era posible exportar un archivo Excel o la hoja activa al formato PDF, pues bien lo he estado probando en Excel 2010 y 2007 y funciona perfectamente, os dejo el código para que lo probéis.

Es muy sencillo y fácil de entender por eso no veo necesario extenderme más, si os surge alguna duda me comentáis.

Sub ExporPdf()

    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _

    Filename:="Prueba", includedocproperties:=True, openafterpublish:=True

End Sub

Crear Procedimientos Function

Ahora que sabemos prácticamente todo de los procedimientos Sub y como crearlos, es el momento de descubrir lo útiles que pueden ser los procedimientos de Function.

Solo los procedimientos de Function pueden hacer lo siguiente:

• Derivar un valor.
• Devolver un valor al proyecto.

La sintaxis de la instrucción Function consta de las siguientes partes:

[Public | Function nombre [(lista_argumentos)] [As tipo]

Veamos el siguiente ejemplo:

Public Function CalcEdad(fechaNac As Date)

Dim zFecha As Date

'DateDiff especifica el número de intervalos de tiempo entre dos fechas determinadas (yyyy Año).

CalcEdad = Abs(DateDiff("yyyy", fechaNac, Date))

'DateAdd Devuelve un valor de tipo Variant (Date) con una fecha a la que se ha agregado un intervalo de tiempo especificado.

zFecha = DateAdd("yyyy", CalcEdad, fechaNac)

If zFecha > Date Then CalcEdad = CalcEdad - 1

End Function 

Este ejemplo nos muestra la importancia de los argumentos de las funciones, cuanto más explícito sean, más fácil serán de usar en la función.

Todas las funciones declaradas en Public están disponibles en el asistente de funciones en la categoría Funciones Definidas por el Usuario.

El resultado de la formula con esta función quedaría de la siguiente forma en Excel.

Cuadro de Diálogo

En la mayoría de aplicaciones Windows el usuario hace uso de los cuadros de diálogo para establecer opciones o introducir información que la aplicación necesita para realizar la acción indicada por el usuario.

La función MsgBox

La función MsgBox muestra información y espera una respuesta.  Es una muy buena forma de distribuir la información y obtener la devolución de una respuesta simple.

La sintaxis de la función MsgBox consta de estos argumentos.

MsgBox(Comentario[, botón][, título del cuadro])

En esta tabla describimos las opciones para los ajustes de botón, el estilo de icono y la asignación de botón predeterminado.

Constante	Valor	Descripción
VbOKOnly	0	Muestra solamente el botón Aceptar.
VbOKCancel	1	Muestra los botones Aceptar y Cancelar.
VbAbortRetryIgnore	2	Muestra los botones Anular, Reintentar e Ignorar.
VbYesNoCancel	3	Muestra los botones Sí, No y Cancelar.
VbYesNo	4	Muestra los botones Sí y No.
VbRetryCancel	5	Muestra los botones Reintentar y Cancelar.
VbCritical	16	Muestra el icono de mensaje crítico.
VbQuestion	32	Muestra el icono de pregunta de advertencia.
VbExclamation	48	Muestra el icono de mensaje de advertencia.
VbInformation	64	Muestra el icono de mensaje de información.
VbDefaultButton1	0	El primer botón es el predeterminado.
VbDefaultButton2	256	El segundo botón es el predeterminado.
VbDefaultButton3	512	El tercer botón es el predeterminado.
VbDefaultButton4	768	El cuarto botón es el predeterminado.
VbApplicationModal	0	Aplicación modal; el usuario debe responder al cuadro de mensajes antes de poder seguir trabajando en la aplicación actual.
VbSystemModal	4096	Sistema modal; se suspenden todas las aplicaciones hasta que el usuario responda al cuadro de mensajes.

Sub Mensaje()

    Dim Respuesta As Integer

    Respuesta = MsgBox("Seleccione Aceptar o Cancelar", vbOKCancel)

    ' verificamos si se presionó 'Cancelar'

If Respuesta = vbCancel Then

    MsgBox "Selecciono Cancelar"   

    End If

    If Respuesta = vbOK Then

    MsgBox "Selecciono Aceptar"  

    End If

End Sub

La función InputBox

La función InputBox crea un cuadro de mensaje especial que hace la siguiente:

• Muestra un apunte solicitado un solo bit de información con un cuadro de texto vacío.
• Espera que el usuario introduzca el texto o haga clic en un botón.
• Devuelve el contenido del cuadro de texto con la respuesta del botón al procedimiento que llamo a la función.

La sintaxis de la función es la siguiente:

InputBox(Comentario[, título del cuadro][, respuesta predeterminada en el cuadro de texto][, Tipo de dato])

Sub Nombre()

Dim Nombre As Variant

Nombre = Application.InputBox(prompt:="Escriba su nombre", Title:="Registro de nombres", Default:="Pedro", Type:=2)

Msgbox Nombre 

End Sub