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.