jueves, 18 de octubre de 2018

Power Query: Recuperar datos de fichero más reciente

Un caso muy habitual es la necesidad de recuperar la información del fichero más reciente dentro de una carpeta... por ejemplo, si tenemos cada mes u nuevo fichero de precios para nuestros productos, y cada vez añadimos el nuevo fichero con nuevos precios...

Supongamos el ejemplo en que tenemos precios de enero y febrero en dos ficheros distintos (obviamente el de febrero tiene fecha de creación más reciente)

Power Query: Recuperar datos de fichero más reciente



Por otro lado, en otro libro de trabajo, tenemos nuestro listado de ventas

Power Query: Recuperar datos de fichero más reciente



El objetivo a alcanzar es aplicar los precios más recientes (del fichero más actual) a las distintas ventas registradas.

En un primer paso cargaremos la tabla de ventas solo como conexión.
Así desde la ficha Datos > grupo Obtener y transformar > Desde una tabla procedemos.
Recuerda, preferiblemente, cerrar y cargar en ... solo conexión


En el siguiente paso accederemos a los ficheros de precios, desde la ficha Datos > grupo Obtener y transformar > Nueva consulta > Desde un archivo > Desde una carpeta

Power Query: Recuperar datos de fichero más reciente



En la ventana siguiente buscaremos la ubicación de nuestra carpeta de precios... y Aceptaremos

Power Query: Recuperar datos de fichero más reciente



la siguiente ventana es importante... ya que deberemos Editar la consulta.

Power Query: Recuperar datos de fichero más reciente



Dentro del editor de consultas de Power Query ordenaremos en sentido descendente por el campo de fecha deseado (hay tres posibilidades: Fecha creación, Fecha últimos acceso o Fecha última modificación).
Yo optaré por la Fecha de creación / Date created.


A continuación, buscaremos la opción dentro de la ficha Inicio > grupo Reducir filas > Conservar filas > Conservar filas superiores

Power Query: Recuperar datos de fichero más reciente



Se abrirá una ventana donde se nos pregunta cuántas filas queremos conservar de la consulta que estamos viendo... claramente solo queremos mantener una.

Power Query: Recuperar datos de fichero más reciente



Desde nuestra posición o vista actual forzaremos a mostrar el contenido de nuestro fichero 'superviviente', así pues buscaremos el campo 'Content' y lo desplegaremos... esto abrirá una nueva ventana para que indiquemos la situación de nuestros datos dentro de libro de trabajo con los precios (podemos optar por el nombre de la hoja o el nombre de la tabla/rango). Luego de seleccionarlos, aceptaremos.

Power Query: Recuperar datos de fichero más reciente



La conexión ya está finalizada, y vemos los datos esperados...

Power Query: Recuperar datos de fichero más reciente



Finalmente, para conseguir relacionar nuestra tabla de ventas y nuestros precios más recientes, solo tendremos que combinar ambas tablas... lo que podemos hacer en este momento.
Con la consulta de últimos precios aún abierta, podemos navegar a la ficha Inicio > botón Combinar > Combinar consultas par crear una nueva.

Power Query: Recuperar datos de fichero más reciente



En la ventana de 'Combinar' marcamos los campos que servirán de nexo en ambas tablas e igualmente el tipo de combinación deseada, en mi ejemplo: Externa derecha (todas de la segunda, coincidencias de la primera)
Esto es, todos los registros de nuestras ventas y los precios coincidentes de los precios más recientes...

Power Query: Recuperar datos de fichero más reciente



En la nueva consulta creada solo tendremos que Expandir los campos deseados de la tabla de ventas (TblVentas):= (fechas, productos y unidades)

Power Query: Recuperar datos de fichero más reciente



Hemos acabado.. podemos renombrar nuestra consulta y ocultar las columnas no deseadas, añadir columnas calculadas, etc...
Para el ejemplo dejaré todas las columnas y nombres por defecto.
Queda Cargar y cerrar la consulta y mostrarla en la hoja de cálculo.

Power Query: Recuperar datos de fichero más reciente



Lo interesante es que al añadir un nuevo fichero de precios, por ejemplo par marzo 2018, al actualizar nuestra consulta recuperaremos los precios de este último fichero con la fecha de creación más actual...

martes, 16 de octubre de 2018

VBA: Kill y RmDir para eliminar ficheros y carpetas

Hace algún tiempo publiqué la manera de crear carpetas en nuestro árbol de Windows con la función MkDir (ver aquí)

Hoy veremos la acción opuesta para eliminar carpetas y los ficheros que ellas contengan, para lo que emplearemos las funciones Kill y RmDir.

Tenemos la siguiente carpeta que deseamos eliminar y que contiene ciertos ficheros...

VBA: Kill y RmDir para eliminar ficheros y carpetas



Nuestro código VBA a incluir en un módulo estándar dentro del Editor de VBA es:

Sub Borrado()
Dim strRuta As String
'ruta de la carpeta a borrar..
strRuta = "E:\excelforo\000AAAExcelforo\Carpeta_a_borrar_1"

If Right(strRuta, 1) <> "\" Then strRuta = strRuta & "\"

On Error Resume Next
'paso 1 - eliminamos contenido
'función KILL: para el borrado de una carpeta y los ficheros qeu hubiera en ella
Kill strRuta & "*.*"    ' borra la totalidad de archivos
'Kill strRuta & "*.xl*"    ' borraría la totalidad de archivos solo de Excel

'paso 2 - borramos la carpeta
'función RmDir: para borrar una carpeta ya vacía de ficheros
RmDir strRuta  'eliminamos la carpeta (si está vacía!!)
On Error GoTo 0
End Sub



Un alternativa a estas funciones de VBA es emplear el ScriptSystemObject y su método .DeleteFolder

Nuestro código VBA a incluir en un módulo estándar dentro del Editor de VBA es:

Sub Borrado_Carpeta()
'Elimina la carpeta sin necesidad de eliminar los ficheros en ella contenidos
Dim FSO As Object
Dim strRuta As String

'ruta de la carpeta a borrar..
strRuta = "E:\excelforo\000AAAExcelforo\Carpeta_a_borrar_2"

'quitamos la posible última barra \ de la ruta
If Right(strRuta, 1) = "\" Then MyPath = Left(strRuta, Len(strRuta) - 1)

'llamamos al script de FileSystem
Set FSO = CreateObject("Scripting.FileSystemObject")

'y acabamos borrando la carpeta y todo su contenido
FSO.DeleteFolder strRuta

End Sub



Con ambos métodos conseguimos nuestro objetivo de eliminar desde Excel carpetas y ficheros situados en cualquier parte de nuestro entorno de trabajo...

jueves, 11 de octubre de 2018

VBA: Convertir textos a mayúsculas

Estudiaremos en esta ocasión la forma de convertir en mayúsculas los textos de un rango...
Se trata de dar respuesta a la cuestión planteada por un usuario del blog:
[...] Tengo una duda, cómo se podría hacer para que cuando se copie un rango, por ejemplo 5 celdas con letras en minúsculas, y se pegue en la hoja1, todas las celdas pegadas se transformen en mayúsculas. La alternativa que nos presentas aplica solo si se pega una celda o se escribe en una sola celda. [...]


Partiremos del siguiente rango B2:D6 con textos y números.

VBA: Convertir textos a mayúsculas



Veremos algunas alternativas...
Insertamos el siguiente procedimiento 'Sub' en un módulo estándar:

Sub ConvertirMayusculas()
'a elegir una manera...

'1-forma clásica
'recorremos celda a celda la selección
For Each celda In Selection
    ' y convertimos con la función UCASE todo en mayúscula
    celda.Value = UCase(celda.Value)
Next celda

'2-forma óptima (recomendada)
Dim strSel As String
strSel = Selection.Address
Selection = Evaluate("INDEX(UPPER(" & strSel & "),,)")

'3-forma alternativa sobre rango estático
[B2:D6] = [INDEX(UPPER(B2:D6),,)]
End Sub



OJO!!!, estos métodos convierten todo a valores... por lo que las fórmulas 'desaparecen, convertidas a valores!!!

Para evitar este contratiempo nos obligaremos a emplear el modo bucle (forma clásica), donde con la propiedad .HasFormula discriminaremos las celdas con fórmulas.
Puedes ver un ejemplo similar aquí.


La primera forma emplea la función VBA llamada UCASE que procesa los textos y los convierte en mayúsculas...
Su contraria es LCASE que transforma en minúscula los textos.
El procedimiento se realiza celda a celda...

La segunda y tercera forma (en el fondo es la misma) emplea funciones de la hoja de cálculo:
función INDICE y función MAYUSC para transformar cada celda del rango indicado en la función.
En primer lugar UPPER - MAYUSC convierte en mayúscula los textos, y luego INDEX - INDICE devuelve cada valor a su celda...


Como punto interesante la manera de referirnos a los rangos en el método 3 expuesto
[B2:D6] = [INDEX(UPPER(B2:D6),,)]

que emplea una notación de VBA algo olvidada... con el uso de los corchetes (square brackets).
Estos corchetes en general representan o replican el uso de la función EVALUATE.