Recuperar número entre textos

Hace tiempo publiqué un artículo donde explicaba una forma de extraer valores numéricos 'mezclados' con texto en una celda.
Muy frecuentemente nos encontramos determinar si un caracter es o no numérico, lo que hacemos empleando la función ESNUMERO combinada con VALOR (o el doble --)... sin duda efectivo ;-)

Hoy propongo emplear la función CODIGO o UNICODE para detectar sobre qué caracter ANSI (o unicode) estamos trabajando...

Replicaremos el caso propuesto para el artículo anterior comentado:

Donde la función construida es:
=LET(cadena;B2; numeros;LET(tratado1;LET(kn;SECUENCIA(LARGO(cadena)); an;EXTRAE(cadena;kn;1); SI(CODIGO(an)>59;"";SI(CODIGO(an)=32;"";an)) ); UNIRCADENAS("";VERDADERO;tratado1)); Separadores;LET(tratado2;LET(ks;SECUENCIA(LARGO(cadena)); as;EXTRAE(cadena;ks;1); SI(CODIGO(as)={44\46};as;"" ) ); UNIRCADENAS("";VERDADERO;UNICOS(tratado2))); separador1;EXTRAE(Separadores;2;1); separador2;EXTRAE(Separadores;1;1); SI.CONJUNTO(LARGO(Separadores)=0;VALOR(numeros); LARGO(Separadores)=1;VALOR.NUMERO(numeros;separador2); LARGO(Separadores)=2;VALOR.NUMERO(numeros;separador1;separador2) ) )
Observemos que el resultado es correcto en los casos expuestos...

La diferencia con el ejercicio anterior se basa en el uso de CODIGO para diferenciar cuáles son los caracteres numéricos... sabiendo que en el código ANSI los diez dígitos responden a los caracteres:
0 - ANSI 48
1 - ANSI 49
2 - ANSI 50
...
9 - ANSI 57
y que además, otros caracteres relevantes son:
32 - ANSI ' ' (espacio en blanco!)
44 - ANSI ,
45 - ANSI -
46 - ANSI .
59 - ANSI ;

Así pues al evaluar caracter a caracter y verificando que sea >59, y con el condicional SI sustituyéndolo por 'nada', al igual que para el caracter 32 (espacio en blanco), estamos limpiando la cadena de texto de cualquier caracter 'no numérico' (como queríamos:
numeros;LET(tratado1;LET(kn;SECUENCIA(LARGO(cadena)); an;EXTRAE(cadena;kn;1); SI(CODIGO(an)>59;"";SI(CODIGO(an)=32;"";an)) ); UNIRCADENAS("";VERDADERO;tratado1))

De forma similar, y con la misma finalidad del artículo comentado, extraemos de manera única los caracteres empleados como separadores decimales o de grupo (miles, millones, etc..):
Separadores;LET(tratado2;LET(ks;SECUENCIA(LARGO(cadena)); as;EXTRAE(cadena;ks;1); SI(CODIGO(as)={44\46};as;"" ) ); UNIRCADENAS("";VERDADERO;UNICOS(tratado2)))
En este caso, los códigos ANSI buscados son o el 44 o el 46, esto es, la coma o el punto.

El resto de la función es idéntica a la anterior, donde identificamos cúal es el separador de grupo ('separador1')y el separador decimal ('separador2'), para finalmente, emplear una función VALOR o VALOR.NUMERO, que convierta el texto 'numérico' obtenido en un número cierto. Sin duda otra forma de trabajar, alternativamente a ESNUMERO... y quizá algo más corta.

Seguir leyendo...

Power Automate: Ejecutar Macro en Excel

Hoy expondré un ejemplo de cómo automatizar, con Power Automate Desktop, nuestras tareas en Excel.
En particular veremos cómo es posible trabajar sobre un fichero de Excel y ejecutar una macro en él contenida.

Nuestro fichero Excel tiene por nombre: 'PA_EjecutarMacroExcel.xlsm', que por facilidad tendremos abierto (no obstante, recuerda que con Power Automate podemos abrir un fichero Excel cualquiera, incluso habilitar sus macros!!).
Dentro de nuestro fichero existe una macro llamada '' muy simple, que solo añade a la derecha de la celda una fórmula donde multiplicamos por dos celda activa:

Sub PruebaPowerAutomate()
ActiveCell.Offset(0, 1).FormulaR1C1 = "=RC[-1]*2"
End Sub

Con los previos listos, accederemos a Power Automate Desktop para montar nuestro Nuevo Flujo:

Un repaso antes de detallar cada paso...
1- para nuestro ejemplo comenzamos generando una lista aleatoria de números...
2- Asociamos a un libro de Excel ya abierto (el ya comentado 'PA_EjecutarMacroExcel.xlsm')
3- elegimos la hoja activa sobre la que trabajar de nuestro archivo
4- e igualmente elegimos cuál será nuestra celda activa, en mi ejemplo la celda A1.
5- con un bucle For...Each recorremos cada elemento de números aleatorios generados (ver primer paso), donde:
5.1- definimos la celda activa como una por debajo de la celda activa anterior...
5.2- escribimos en la hoja de cálculo el valor o elemento que toque de nuestra lista 5.3- y finalmente ejecutamos nuestra macro... lo que completará a su derecha la fórmula comentada.

Veamos con detalle cada paso descrito.

En el paso uno desde las acciones de Variables añadiremos Generar número aleatorio, donde en el ejemplo indicamos que los valores a generar irán entre 0 y 100, además generaremos 5 valores y no permitimos que haya duplicados.

En el segundo paso desde el grupo de Excel indicamos que queremos trabajar sobre un archivo ya abierto:Asociar a Excel en ejecución, donde damos el nombre (y ruta si fuera el caso) del archivo... PA_EjecutarMacroExcel.xlsm

Paso tres. Del fichero anterior indicamos el nombre de la hoja que activamos, y sobre la que trabajaremos...Establece la hoja de cálculo de Excel activa.

Podemos seleccionar la hoja activa bien por 'Nombre de hoja' bien por 'Posición' (o Índice) de izquierda a derecha.

Paso cuatro. Identificamos cuál será la celda activa dentro de la hoja anterior, celda A1 en mi ejemplo... Insertamos la acción Activar celda en hoja de cálculo de Excel.

Es posible identificarla en forma absoluta (un número de columna y un número de fila) o de manera relativa (desplazada en alguna dirección desde la actual celda activa...).

Paso cinco. Entramos en el bucle For Each para recorrer la lista aleatoria del punto uno.

Dentro del proceso del bucle, en el punto 5.1 activaremos la celda por debajo de la actual celda activa (recuerda que era A1)... esto activará en cada etapa del bucle la celda inmediata por debajo.
Activar celda en hoja de cálculo de Excel

Seguimos dentro del bucle en el paso 5.2 donde añadimos la acción Escribir en hoja de cálculo de Excel, donde indicamos que deseamos escribir el elemento actual de la lista aleatoria en la celda activa actual...

Finalizamos con el flujo, en el último paso dentro de bucle... Paso 5.3 donde finalmente lanzamos nuestra macro/VBA de Excel: Ejecutar macro de Excel
Basta indicar el nombre de la macro.

Al ejecutar nuestro flujo desde Power Automate Desktop veríamos el resultado:

Donde efectivamente comprobamos que se han trasladado cinco valores aleatorios a la hoja 'TestPA' del libro 'PA_EjecutarMacroExcel.xlsm', y cómo además se ha ejecutado la macro en cada momento, añdiendo el procedimiento deseado (multiplicar por dos).

Seguir leyendo...

Power Query: Multiplicar sobre valores null

Quizá nunca te hayas percatado de este aspecto, pero las consecuencias son MUY importantes.
Cuando operamos sobre varias columnas en Power Query empleando List.Product y entre los elementos nos encontramos con un valor null, PQ trata ese null como 1 :OOO ¿cómo!!?
Observa el caso:

Notemos como el uso de la función List.Product en el ejemplo, da tratamiento de 1 al valor null:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Producto", type text}, {"Unidades", Int64.Type}, {"Precio", type number}, {"Factor", type number}}),
    MultiplicaciónInsertada = Table.AddColumn(TipoCambiado, "Multiplicación", each List.Product({[Unidades], [Precio], [Factor]}, Precision.Double), type number)
in
    MultiplicaciónInsertada

Matemáticamente es una locura...
Una posible solución sería reemplazar los valores 'null' por cero, lo que debe solucionar el caso:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Producto", type text}, {"Unidades", Int64.Type}, {"Precio", type number}, {"Factor", type number}}),
    ValorReemplazado = Table.ReplaceValue(TipoCambiado,null,0,Replacer.ReplaceValue,{"Unidades", "Precio", "Factor"}),
    MultiplicaciónInsertada = Table.AddColumn(ValorReemplazado, "Multiplicación", each List.Product({[Unidades], [Precio], [Factor]}, Precision.Double), type number)
in
    MultiplicaciónInsertada

Pero, sin duda, la solución más óptima pasa por Agregar una columna personalizada donde agreguemos el producto de las columnas necesarias:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Producto", type text}, {"Unidades", Int64.Type}, {"Precio", type number}, {"Factor", type number}}),
    //Opción 1 - List.Product
    MultiplicaciónInsertada = Table.AddColumn(TipoCambiado, "Multiplicación", each List.Product({[Unidades], [Precio], [Factor]}, Precision.Double), type number),

    //Opción 2 - Agregar Columna Personalizada
    AddCol_Multiplicacion = Table.AddColumn(MultiplicaciónInsertada, "Personalizado", each [Unidades]*[Precio]*[Factor])
in
    AddCol_Multiplicacion

Con esta opción no es necesario forzar reemplazamiento alguno, y el resultado del producto es el esperado... o al menos lo más cercano a la realidad.

No está claro el porqué de este comportamiento, o por que no existe opción alguna en la configuración de la función que resuelva esta situación...

Seguir leyendo...

Power Query: Operar sobre columnas variables

Un ejercicio interesante con Power Query es facilitar el cálculo sobre un número de columnas variables, sujetas a la elección del usuario, de forma dinámica.

Se trata pues de permitir que el usuario elija una lista de columnas (meses en mi ejemplo) sobre las que operar (celdas P2:P5), que además controlemos que la selección coincida con los nombres de las columnas, para evitar fallos, y finalmente se realice la suma acumulada de los meses resultantes...

Para ello cargaremos las dos tablas (la de datos y la de la selección de meses) en el editor de Power Query; comenzaremos editando y convirtiendo en una Lista la selección de meses:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblMESES"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Meses a operar", type text}})[Meses a operar]
in
    TipoCambiado

Seguidamente editaremos la tabla de datos (que he llamado 'TblDATOS') para añadir las siguientes líneas de código:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"País", type text}, {"ene", Int64.Type}, {"feb", Int64.Type}, {"mar", Int64.Type}, {"abr", Int64.Type}, {"may", Int64.Type}, {"jun", Int64.Type}, {"jul", Int64.Type}, {"ago", Int64.Type}, {"sep", Int64.Type}, {"oct", Int64.Type}, {"nov", Int64.Type}, {"dic", Int64.Type}}),
    
    //Trabajamos únicamente con los campos que sí existen en nuestra tabla
    listaCampos=List.Intersect({Table.ColumnNames(TipoCambiado),TblMESES}),
    
    //OPCIÓN 1 -- Trabajando con Records
    AddCol_Operacion1 = Table.AddColumn(TipoCambiado, "Suma_1", each List.Sum(Record.ToList(Record.SelectFields(_, listaCampos))), Int64.Type),

    //OPCIÓN 2 -- Trabajando con Evaluate
    TransfMeses = "{" & Text.Combine(List.Transform(listaCampos, each "[" & _ & "]"),", ") & "}",
    AddCol_Operacion2 = Table.AddColumn(AddCol_Operacion1, "Suma_2", each List.Sum( Expression.Evaluate(TransfMeses, [_=_]) ), Int64.Type )
in
    AddCol_Operacion2

IMPORTANTE la línea donde verificamos la existencia de campos indicados en la selección de meses en la tabla de datos:
listaCampos=List.Intersect({Table.ColumnNames(TipoCambiado),TblMESES}),
donde generamos una lista 'nueva' solo con los campos existentes.. esto es, de la lista de meses propuesta en las celda P2:P5:
Enero, feb, mar, sep
comprobamos que sólo feb, mar, sep coinciden con los nombres de columnas de la TblDATOS.

Debemos saber a qué composición debemos llegar para alimentar la función List.Sum. Si hicieramos la acción manualmente veríamos lo siguiente:
= Table.AddColumn(AddCol_Operacion2, "Adición", each List.Sum({[feb], [mar], [sep]}), Int64.Type)
donde comprobamos que List.Sum necesita una lista 'especial' con los nombres de las columnas entre corchetes...
:OO
Esto es, necesitamos crear una lista algo diferente... una lista de columnas.

A continuación trabajamos con dos alternativas para dinamicar el cálculo (Suma en nuestro ejemplo) sobre las columnas anteriores.

En la primera opción generamos la lista de campos que requiere List.Sum empleando la selección de campos de un registro, ya que Record.SelectFields solo necesita una lista simple de columnas...
El resultado anterior, un conjunto de los valores de los campos seleccionados, lo convertimos a lista (Record.ToList), con las características requeridas por List.Sum.

El segundo método descrito es más indirecto, ya que trabajamos sobre literales de los nombres de las columnas... lo que nos exige emplear la función Expression.Evaluate para hacer entender que los textos generados sean interpretado como columnas ciertas.
Así pues generamos dinámicamente una cadena de texto que tenga la forma: '{[feb], [mar], [sep]}'
Esto se consigue en:
TransfMeses = "{" & Text.Combine(List.Transform(listaCampos, each "[" & _ & "]"),", ") & "}",
basicamente, con List.Transform recorremos cada elemento de la lista de meses: feb, mar, sep, concatenado cada uno con los corchetes, quedando una lista: [feb], [mar], [sep].
Lista que combinamos en un solo elemento con Text.Combine, para finalmente darle aspecto de lista incorporando las llaves al principio y fin de nuestra cadena:
"{" & [feb], [mar], [sep] & "}"
pero ojo!!, esto sigue siendo simplemente una cadena de texto!!!.
Para aportarle la funcionalidad que queremos, aplicaremos en su momento, la función Expression.Evaluate:
AddCol_Operacion2 = Table.AddColumn(AddCol_Operacion1, "Suma_2", each List.Sum( Expression.Evaluate(TransfMeses, [_=_]) ), Int64.Type )
Consiguiendo nuestro objetivo... tener una lista de columnas!!

Y finalmente, una suma de dichas columnas ;-)

Seguir leyendo...

VBA: Pasar texto a matriz de celdas

Hoy aplicaremos algo de programación que nos permitirá transformar una cadena de texto, separados en distintas celdas, en una matriz, distinguiendo la parte numérica de los títulos.

Este ejercicio es interesante por el tratamiento que haremos con las Arrays/Matrices...
Ya que generaremos con la función SPLIT nuevas arrays, y con ReDim conseguiremos reconstruir la array definitiva... recomponiendo la parte de textos, asociada a los títulos de la 'tabla original', e incorporando después la parte numérica de dicha tabla.

He optado por generar una UDF con los siguientes argumentos:
Function RecuperaDatoMatriz(rngDatos As Range, dato_tabla As String, Optional num_fila As Long, Optional num_col As Long)
-siendo 'rngDatos' el rango de celdas donde se encuentren las distintas celdas con las cadenas de texto/número a tratar.
-el segundo argumento 'dato_tabla' nos permitirá elegir entre recuperar un dato en particular o el conjunto de la tabla.
-los dos últimos argumentos 'num_fila' y 'num_col' identifican la posición a retornar si hubieramos elegido la opción de 'dato'.

Desde el editor de VB insertaremos un nnuevo módulo estándar donde añadiremos el siguiente procedimiento Function:

Function RecuperaDatoMatriz(rngDatos As Range, dato_tabla As String, Optional num_fila As Long, Optional num_col As Long)
'   'rngDatos' el rango de celdas donde se encuentren las distintas celdas con las cadenas de texto/número a tratar.
'   'dato_tabla' nos permitirá elegir entre recuperar un dato en particular o el conjunto de la tabla.
'   'num_fila' y 'num_col' identifican la posición a retornar si hubieramos elegido la opción de 'dato'.

'definimos las distintas arrays sobre las que trabajar
Dim arrCols() As Double
Dim arrColsFinal() As Variant
Dim arrConceptos() As Variant
Dim arrConceptoCompleto() As Variant

Dim NumFilas As Integer, NumCols As Integer

'obtenemos el número de filas del rango seleccionado
NumFilas = rngDatos.Rows.Count
Dim arrFilas() As Long
ReDim arrFilas(1 To NumFilas) As Long
For nf = 1 To NumFilas
    arrFilas(nf) = nf
Next nf
'y el número de columnas máximo entre todas las celdas seleccionadas
'En este caso empleamos el espacio en blanco para separar columnas
'(idealmente incluiríamos este separador como argumento de la UDF)
For Each co In rngDatos
    arrDividido = Split(co.Value, " ", -1, vbTextCompare)
    NumCols = Application.Max(UBound(arrDividido), NumCols)
Next co

'Procedemos a obtener las matrices para la parte numérica 'arrCols' y la parte de encabezados 'arrConceptos'
'partimos las cadenas de texto con la función SPLIT, usando el espacio en blanco como separador...
f = 0
For Each celda In rngDatos
    arrDividido = Split(celda.Value, " ", -1, vbTextCompare)
    'redefinimos las dimensiones de nuestras arrays destino
    ReDim Preserve arrCols(0 To NumCols, 0 To NumFilas - 1) As Double
    ReDim Preserve arrConceptos(0 To NumCols, 0 To NumFilas - 1) As Variant
    
    c = 1: con = 1
    For itm = 0 To NumCols
        On Error Resume Next
        'recupera los números solamente
        If IsNumeric(arrDividido(itm)) Then
            arrCols(c, f) = arrDividido(itm)
            c = c + 1
        Else
            'recupera solo los textos
            arrConceptos(con, f) = arrDividido(itm)
            
            'Unimos los conceptos en un solo dato
            ReDim Preserve arrConceptoCompleto(0 To f) As Variant
            If arrConceptos(con, f) <> "" Then
                arrConceptoCompleto(f) = arrConceptoCompleto(f) & " " & VBA.Trim(arrConceptos(con, f) & " ")
            End If
            con = con + 1
        End If
        On Error GoTo 0
    Next itm
    f = f + 1
Next celda

'Recomponemos en una única array a partir de las matrices anteriores
'empezamos añadiendo en una nueva matriz, la final -arrColsFinal, la parte de los conceptos/títulos
ReDim arrColsFinal(1 To NumFilas, 1 To 1) As Variant
For xx = 1 To NumFilas
    arrColsFinal(xx, 1) = arrConceptoCompleto(xx - 1)
Next xx

'para luego ir incorporando las columnas NO vacías de números...
finC = 2
For x = 1 To NumCols + 1
    a_sp = Application.Index(Application.Transpose(arrCols), [arrFilas], [x])
    If Application.Sum(a_sp) > 0 Then
        ReDim Preserve arrColsFinal(1 To NumFilas, 1 To finC) As Variant

        For ff = 1 To NumFilas
            arrColsFinal(ff, finC) = Application.Index(Application.Transpose(arrCols), ff, x)
        Next ff

        finC = finC + 1
    End If
Next x

'Controlamos el dato o matriz devuelto según la elección del usuario
If LCase(dato_tabla) = "dato" And (Not IsMissing(num_fila) And Not IsMissing(num_col)) Then
    RecuperaDatoMatriz = Application.Index(arrColsFinal, num_fila, num_col)
ElseIf LCase(dato_tabla) = "tabla" Then
    RecuperaDatoMatriz = arrColsFinal
Else
    RecuperaDatoMatriz = "revisa los argumentos"
End If
End Function

Podemos probar nuestra UDF para recuperar una matriz a partir del conjunto de celdas como la que se ve en la imagen (que en este caso se ha recuperado desde un pdf, y correspondía a una tabla dentro de este pdf).
Añadimos nuestra función en la celda C2:
=RecuperaDatoMatriz($A$2:$A$10;"tabla")

O si queremos recuperar un dato concreto correspondiente a un mes y un país...
En D13 añadimos una fórmula para identificar la fila del país:
=SUMA((ESNUMERO(ENCONTRAR(D12;$C$2:$C$10))*FILA($C$2:$C$10)))-FILA($C$2)+1
Para a continuación en D14 y siguientes añadir nuestra UDF:
=RecuperaDatoMatriz($A$2:$A$10;"dato";$D$13;COINCIDIR($C14;$C$14:$C$26;0)+1)

MUY IMPORTANTE que los datos distribuidos, de la tabla original, responde a un patrón (normal si la fuente es una tabla) en cuanto a número de columnas y filas....

Seguir leyendo...

Power Automate: Extraer texto de pdf a Excel

Mencioné en el artículo previo la posibilidad de extraer el texto de un pdf empleando Power Automate Desktop, y algunos lectores han sentido curiosidad sobre la forma de hacerlo...
Así pues aquí va el proceso.

Partiremos de un fichero Excel con una lista de rutas con los pdf de los cuales nos interesa recuperar su texto.
En mi caso, el fichero será: F:\excelforo\PA_ImportarPDF.xlsx, y en la hoja 1 dispongo del 'listado de pdf's':
F:\excelforo\FacturaEjemploPDF.pdf
F:\excelforo\FacturaEjemploPDF2.pdf

Observa que no es una tabla, ni tiene asignado nombres definidos... son sencillamente dos celdas...

Mantendremos para nuestro ejemplo el fichero abierto

Listos para abrir Power Automate Desktop.
Crearemos un Nuevo Flujo que tendrá los siguientes pasos:

Son 'diez' pasos muy sencillos de seguir que detallaré a continuación:
1- Asociar a Excel en ejecución: es decir, nos dirigimos a nuestro fichero Excel.
2- Establecer la hoja de cálculo activa: indicamos qué hoja del libro anterior activamos.
3- Leer en hoja de trabajo: recuperamos una 'tabla' con el contenido de las celdas.
4- Recuperar columna de datos de tabla en lista: transformamos la tabla anterior en una lista, que nos permitirá fácilmente recorrerla.
5- Bucle For each: que nos permitirá recorrer la lista de rutas del paso anterior.
Dentro del bucle, para elemento de la lista, esto es, para cada fichero pdf:
5.1- Extraer texto del PDF: recupera una única cadena de texto con el contenido completo del pdf..
5.2- Dividir texto: paso fundamental, ya que nos permitirá devolver en pasos siguientes en contenido anterior dividido en distintas celdas!!.
5.3- Establecer la hoja de cálculo activa: activamos la hoja destino de nuestro libro de Excel.
5.4- Obtener la primera columna o fila libre de la hoja de cálculo: nos dice cuál sera la celda destino donde pegar el texto obtenido.
5.5- Escribir en la hoja de cálculo: último paso, terminamos escribiendo a partir de la celda anterior.
Como se puede ver ser pasos bastante lógicos...

Veamos en detalle cada uno de ellos.

El primer paso es dirigirnos a nuestro libro de Excel:Asociar a Excel en ejecución.
Desde las acciones de Excel:

La configuración es muy simple, basta seleccionar desde el buscador el fichero de Excel deseado.

Segundo paso. Establecer la hoja de cálculo activa.
También desde las acciones de Excel..

A partir de la variable generada en el primer paso 'Excelinstance' indicamos la hoja que deseamos activar. Pudiendo optar por referirnos a ella bien por su nombre o bien por su posición entre hojas.
En este caso he optado por dirigirme a la primera hoja del libro.

Tercer paso. Leer en hoja de trabajo.
Igualmente desde las acciones de Excel recorremos un rango de celdas de la hoja activa.

Los parámetros a configurar son sencillos:
- indicamos la variable de libro sobre la que trabajar (y OJO sobre la hoja activada previamente!!). - definimos dónde están los datos a recuperar. Tres opciones (Valor de una celda, Rango de celdas o Celdas seleccionadas).
Yo he optado en este caso por identificar los valores de un rango de celdas, por lo que he definido también las posiciones de fila+columna donde empieza y acaba el rango:
Primera columna=1
Primera fila=1
Última columna=1
Última fila=2
O sea, desde la celda A1 hasta A2

Cuarto paso. Recuperar columna de datos de tabla en lista.
Esta acción la encontramos en el grupo de 'Variables', y nos permite transformar la tabla de datos anterior (las celdas recuperadas) en una lista.
Lista que luego podremos recorrer!!

Importante.. el parámetro que identifica la columna, nos permite optar por el nombre de la columna o por su posición o índice. Si optamos por índice debemos saber que está en base cero. Esto es, la primera columna es la posición cero, la segunda es la posición uno, etc...

Quinto paso. Bucle For each.
En el grupo de acciones de 'Bucles'. Indicamos que lista queremos recorrer. La lista creada en el paso anterior.

Dentro del bucle previo (For each) daremos cinco pasos:
Paso I: Extraer texto del PDF, que recupera una única cadena de texto con el contenido completo del pdf.
Este paso lo encontramos en el grupo de acciones de PDF.

El archivo pdf se le indica a partir de la variable del bucle; además, en este caso, indicamos que queremos recuperar todas las páginas del documento.

Paso II: Dividir texto
En el grupo de acciones de Texto
Este paso dividirá en distintas líneas el contenido obtenido del pdf anterior.. dividiéndolo por cada salto de línea que encuentre (en nuestro ejemplo).
Si bien la configuración nos permite un amplio abanico de posibilidades, incluso personalizando el delimitador!!

Paso III: Establecer la hoja de cálculo activa activamos la hoja destino de nuestro libro de Excel.
Es una acción vista ya en el paso segundo...

Paso IV: Obtener la primera columna o fila libre de la hoja de cálculo Donde identificamos la primera celda libre donde trasladaremos los textos del pdf tratados...
Dentro del grupo de acciones de Excel.

Nos devuelve hasta dos variables, una para la 'Primera fila' y otra para la 'Primera columna' disponible en la hoja activa de nuestra instancia de Excel...

Paso V y último: Escribir en la hoja de cálculo. Escribimos en nuestro Excel el resultado.

Indicamos la variable a trasladar y el destino (en mi ejemplo siempre desde la fila 1 y la primera columna libre que encuentre...

El resultado es óptimo, igual que el del desarrollo con VBA... pero con una ventaja: NO requerimos de licencias o librerías de Acrobat!!.

Seguir leyendo...

VBA: Extraer texto de PDF a Excel

Por suerte hoy en día existen diferentes formas de extraer el texto de un documento pdf: Power Automate, Power Query (leer algo más), programas de terceros, o incluso el mismo Adobe con su método de exportación...
Si bien, algunos de ellos, nos devuelven un texto algo liado y en muchas ocasiones 'bajarado o mezclado', difícil de trabajar.

Un alternativa más limpia de extraer el texto contenido en un pdf es empleando VBA.
Si bien necesitaremos tener acceso a las librerías de Adobe Acrobat (no Reader!!).

Así pues, lo primero que haremos, dentro del Editor de VBA es ir al menú de Herramientas > Referencias y activar la librería: Adobe Acrobat 10.0 Type Library (o la última versión que tengas).
Hecho esto, en el siguiente paso, añadiremos en un módulo estándar del editor de VBA el siguiente procedimiento:.

Sub ExtarerTexto_PDF()
Dim FilePath As String
FilePath = "F:\excelforo\FacturaEjemploPDF.pdf"

Dim strTextoPDF As String

Dim acPDoc As Acrobat.AcroPDDoc                 'para acceder al pdf
Dim acHiLst As Acrobat.AcroHiliteList           'selección de palabras..
Dim acPage As Acrobat.AcroPDPage                'identificará una página concreta dento del pdf
Dim acGTxt As Acrobat.AcroPDTextSelect          'para trabajar sobre el texto seleccionado

Set acPDoc = New Acrobat.AcroPDDoc
Set acHiLst = New Acrobat.AcroHiliteList

'indicamos el tamaño máximo de caracteres a recuperar
acHiLst.Add 0, 2500

'abrimos el documento pdf
acPDoc.Open FilePath

'NumPags = acPDoc.GetNumPages()         'por si quisieramos recorrer todas las páginas del doc

Set acPage = acPDoc.AcquirePage(0)    'sobre la página 1
'con CreateWordHilite seleccionamos todas las palabras del texto de la página
Set acGTxt = acPage.CreateWordHilite(acHiLst)
'si se ha seleccionado algo, lo cargaremos en nuestra variable strTexto
If Not acGTxt Is Nothing Then
    With acGTxt
        'recorriendo cada palabra...
        For j = 0 To .GetNumText - 1
            strTextoPDF = strTextoPDF & .GetText(j)
        Next j
    End With
End If

'Para trasladarlo a las celdas de nuestra hoja de cálculo
'partimos el texto obtenido por cada salto de línea identificada... (usamos la función SPLIT)
arrTexto = Split(strTextoPDF, vbCrLf)
'finalmente recorremos la matriz obtenida
'y trasladamos cada parte a una celda distinta
For fila = 1 To UBound(arrTexto)
    Hoja4.Range("A1").Offset(fila - 1, 0).Value = arrTexto(fila)
Next fila

'cerramos el documento
acPDoc.Close

'liberamos memoria
Set acPDoc = Nothing
Set acHiLst = Nothing
Set acPage = Nothing
Set acGTxt = Nothing
End Sub

El procedimiento, en general, realiza los siguientes pasos:
- Abre el pdf
- Indicamos sobre qué página (base 0) trabajaremos
- Tomamos el número de caracteres indicado en un paso previo (2500 en mi ejemplo)
- identificamos cada palabra y las recorremos componiendo un texto único
- finalmente separamos por saltos de línea existentes, y
- lo trasladamos a las celdas de nuestra hoja de cálculo.
Por ejemplo, en la siguiente imagen vemos el resultado de tratar el documento pdf de la derecha con nuestra macro.

Algunos métodos, propiedades, objetos, etc... de la librería de Adobe empleados son:
.Open: muy simple. Abre el pdf en un nueva instancia.
AcroHiliteList.Add: En esencia crea una selección de texto a partir de una lista de caracteres y recuentos de caracteres en una página.
Después de crear nuestra lista destacada, podremos usar:
- PDPage.CreatePageHilite
-o PDPage.CreateWordHilite
dependiendo de si la lista creada se utiliza por caracteres o palabras, con el fin de crear una selección de texto de la lista.
.GetNumPages(): Nos dice el número total de páginas del documento pdf.
.AcquirePage(índice): Trabaja sobre la página concreta indicada del documento pdf.
.CreateWordHilite: Crea una selección de texto a partir de una lista de palabras en una sola página. La selección de texto puede luego configurarse como la selección actual usando AVDoc.SetTextSelection, y la vista se puede configurar para mostrar la selección usando AVDoc.ShowTextSelect.
.GetNumText: Obtiene el número de elementos de texto en una selección de texto. OJO, un 'elemento de texto' no es necesariamente una palabra; un elemento de texto consta de caracteres de la misma fuente, tamaño y estilo.
.GetText: Obtiene el texto del elemento especificado de una selección de texto previa (por ejemplo, la generada con .CreateWordHilite).

Como vemos el resultado obtenido es bastante limpio y fácil de trabajar... si bien se requiere un conocimiento elevado del entorno de Adobe para poder trabajar nuestros pdf's ;-)

Seguir leyendo...

Contar Meses Consecutivos

Hoy contaremos cuántos meses consecutivos se dan bajo ciertas condiciones en un listado de matriculaciones:

Vemos que tenemos un listado de matriculaciones de ciertos alumnos en ciertos cursos durante meses del año corriente (entre ENERO y AGOSTO), y deseamos conocer para cada par Alumno-Curso cuántos meses consecutivos se ha matriculado cada uno de ellos.

En primer lugar toca transformar los meses en texto a valores de tipo fecha, lo que haremos con una sencilla fórmula FECHANUMERO:
=FECHANUMERO("1/"&[@[Mes matrícula]]&"2021")
rápido y simple.
La función FECHANUMERO transforma una fecha en formato de texto a tipo de dato fecha.

Vamos ahora a por la clave del asunto...
En primer lugar necesitamos listar elementos únicos de Alumno-Curso, lo que se consigue con la función UNICOS:

Nuestra fórmula, combina ORDENAR y UNICOS es:
=ORDENAR(UNICOS(Tabla1[[Curso]:[Alumno]]);2)
donde ordenamos con prioridad en el segundo de las columnas/campos, i.e., por los Alumnos...

A continuación obtendremos una secuencia de números naturales que nos indicarán en qué meses se ha producido cada matriculación, para cada par de elementos:

La fórmula buscada es:
=SI.ERROR(COINCIDIR(FECHA.MES("1/1/2021";SECUENCIA(1;9;0));ORDENAR(TRANSPONER(UNICOS((SI(Tabla1[Alumno]=$H3;SI(Tabla1[Curso]=$G3;Tabla1[Fecha convertida])))));;;VERDADERO);0);0)
Un poco larga, sí, pero nos da lo que necesitamos...
Identifica y posiciona en qué meses se ha matriculado tal alumno en tal curso.
Por ejemplo, el cuarto caso de la lista: alumno ANA + curso VBA, donde si verificamos la tabla original comprobaremos que ha cursado durante los meses de: FEBRERO, MARZO y ABBRIL

Y qué hace nuestra fórmula para listar de esa forma las coincidencias??.
En primer lugar generamos un listado de fechas correlativas correspondiente al periodo de estudio:
FECHA.MES("1/1/2021";SECUENCIA(1;9;0))
que genera una matriz de nueve fechas comenzando el 1/1/2021 y finalizando el 1/9/2021

Por otro lado buscamos la matriz ordenada de fechas en las que cada alumno+curso se ha matriculado:
ORDENAR(TRANSPONER(UNICOS((SI(Tabla1[Alumno]=$H3;SI(Tabla1[Curso]=$G3;Tabla1[Fecha convertida])))));;;VERDADERO)
Podemos ver el resultado, a modo de ejemplo, en la imagen siguiente:

Con ambas matrices 'creadas virtualmente', procedemos a compararlas con la función COINCIDIR mediante un tipo de coincidencia exacta:
COINCIDIR(matriz_9_meses;matriz_matriculaciones;0)
Esto nos devolverá una nueva matriz de números naturales consecutivos para aquellas posiciones o meses donde existe la coincidencia, esto es, donde se hubieran matriculado... con errores de tipo N/D (o N/A) para las no coincidencias...
Errores que tratamos finalmente con SI.ERROR.

Solo nos queda contar el valor máximo de meses consecutivos matriculados.
Esto lo conseguiremos con la siguiente fórmula:
=MAX(FRECUENCIA(SI(I3#>0;COLUMNA(I3#));SI(I3#=0;COLUMNA(I3#))))
Al trabajar con Microsoft 365, y sobre fórmulas matriciales desbordadas, adquiere la notación correspondiente con las almohadillas o numerales #
Además no hace falta ejecutarla matricialmente presionando Ctrl Mayusc Enter.

La fórmula se apoya en la función FRECUENCIA, identificando, por número de columna, dónde se encuentran los ceros (Meses de NO matriculación) y los valores positivos (Meses de SI matriculación).
Esto es muy interesante, ya que FRECUENCIA permite contar el número de los resultados que se encuentran dentro de un rango.. justo lo que necesitamos.

Veamos un ejemplo del comportamiento de FRECUENCIA...

En la imagen, en la columna DATOS se replica el resultado del condicional anterior:
SI(rng_datos>0;COLUMNA(rang_datos))
Mientras que en la columna GRUPOS vemos el resultado de:
SI(rng_datos=0;COLUMNA(rang_datos))
A la derecha el resultado de FRECUENCIA y una breve explicación de lo que vemos...

Poco más queda por hacer, salvo recuperar el valor máximo de entre esos conteos:
=MAX(FRECUENCIA(datos;grupo))
Obteniendo el resultado buscado!!!

Seguir leyendo...