CONSULTA SQL DE AGRUPACIÓN Y SUMA CON ADO

Hola a todos!

Llevo recibidas ya varias consultas sobre el tema de los duplicados y su tratamiento a la hora de agrupar informaciones. Esto es un tema recurrente y que dependiendo del escenario en el que trabajemos puede ser más o menos sencillo.

Por ejemplo, en muchos casos lo podemos solucionar con una tabla dinámica, en otros casos con una macro que elimine duplicados y componer luego el resto de informaciones con otras rutinas, etc.

En el ejemplo de hoy (muy básico) os mostraré una forma que se adapta bien a la hora de trabajar con procesos programados y no utiliza las tablas dinámicas (evitando así el tener luego que tratar con formatos y restricciones típicas de las tablas).

Vamos al ejemplo, imaginad que tenéis el resumen de facturación de tres de vuestros empleados. Los datos son por persona, mes e importe:

CONSULTA SQL DE AGRUPACIÓN Y SUMA CON ADO

Y lo que deseamos es obtener únicamente la suma acumulada por vendedor, es decir:

CONSULTA SQL DE AGRUPACIÓN Y SUMA CON ADO_1

Y claro, la opción más rápida es hacer una tabla dinámica y listo. Pero imaginemos que queremos un proceso programado, por ejemplo para obtener unos datos que luego analizaremos o transformaremos en rutinas posteriores, en ese caso la tabla dinámica, (aunque se puede programar) no sería la mejor opción.

Para ello, os propongo utilizar ADO con una consulta muy sencilla de SQL que nos hará todo el trabajo.

La consulta sería esta:

obSQL = "SELECT [FACTURACION$].[VENDEDOR], SUM ([FACTURACION$].[IMPORTE]) AS FACTURACION_ANUAL " & _
"FROM [FACTURACION$] GROUP BY [FACTURACION$].[VENDEDOR] "

Donde, como ya sabéis de post anteriores, [FACTURACION$]es el nombre de la hoja (siempre con $ al final) y [VENDEDOR]hace referencia al campo de nuestra tabla.

Esta es la macro completa:

Option Explicit
Sub AGRUPAR_SUMA()
'DECLARAMOS VARIABLES
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection, MiLibro As String
Dim i As Long, titulo As String
'ESCRIBIMOS CONSULTA SQL DE AGRUPACIÓN Y SUMA
obSQL = "SELECT [FACTURACION$].[VENDEDOR], SUM ([FACTURACION$].[IMPORTE]) AS FACTURACION_ANUAL " & _
"FROM [FACTURACION$] GROUP BY [FACTURACION$].[VENDEDOR] "
'Guardamos el nombre del libro activo para utilizarlo en la conexión ADO
MiLibro = ActiveWorkbook.Name
'Iniciamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Procedemos a grabar los datos de la consulta en un RS
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
With Worksheets("RESUMEN_ANUAL")
.Select
'Pasamos los datos a la hoja RESUMEN_ANUAL
.Cells(2, 1).CopyFromRecordset Dataread
'Creamos encabezados
For i = 0 To Dataread.Fields.Count - 1
titulo = Dataread.Fields(i).Name
.Cells(1, i + 1) = titulo
Next
End With
'Liberamos y cerramos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End Sub

Dado que estamos trabajando con ADO, Os recuerdo que es necesario marcar la referencia Microsoft ActiveX Data Object 2.8 Library (o la que corresponda) para que todos los componentes funcionen correctamente.

Trabajar con esta metodología hará nuestros procesos mucho más eficientes.

Descarga el archivo de ejemplo pulsando en: CONSULTA SQL DE AGRUPACIÓN Y SUMA CON ADO

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

Anuncios

COMPROBAR SI UNA CELDA CONTIENE UN CARÁCTER O PALABRA ESPECÍFICA

Hola a todos:

Hace unos días me preguntaban una fórmula para poder averiguar si una celda contiene un carácter o cadena específica. La respuesta es que existen varias y con distinta combinación de fórmulas.

Comenzamos con ejemplo para buscar, imaginad que queremos comprobar si en “Excel Signum” se encuentra la cadena de texto siguiente: “Sig“.

Comenzamos con la fórmula más conocida y que encontraréis en todas las web y manuales. Donde con la función HALLAR buscamos “Sig” en el contenido de la celda y si lo encuentra nos devolverá un número com resultado de la posición. Si utilizamos la función ESNUMERO nos devolverá un verdadero o falso, que combinado con la función SI nos permitirá incluir un texto cuando encuentre el valor.

La función Hallar permite comodines y no distingue entre mayúsculas y minúsculas:

=SI(ESNUMERO(HALLAR("Sig";A1));"Contiene valor";"No lo contiene")

La siguiente fórmula (que es la que suelo usar), se compone de la combinación de CONTAR.SI y SI.

Cuando ejecutamos CONTAR.SI nos va a devolver el número de veces que cuenta el valor buscado, con la función SI indicamos que cuando el resultado sea verdadero (superior a 0) indique que contiene el valor buscado.

Esta fórmula permite comodines y en este caso son necesarios dado que para indicar que busque en todo el contenido de la celda debemos especificar los asteriscos al inicio y al final del valor, pero podríamos especificar que solo sea al inicio o al final. No distingue entre mayúsculas y minúsculas:

=SI(CONTAR.SI(A2;"*Sig*");"Contiene valor";"No lo contiene")

Otra posibilidad es una variante de la primera fórmula pero usando la función ENCONTRAR. Tiene la limitación de no permitir comodines y distingue entre mayúsculas y minúsculas:

=SI(ESNUMERO(ENCONTRAR("Sig";A5));"Contiene valor";"No lo contiene")

Por último, otra función que tampoco permite comodines y se debe respetar las mayçusculas y minúsculas, es la siguiente:

=SI(SUSTITUIR(A4;"Sig";"")<>A4=VERDADERO;"Contiene valor";"No lo contiene")

Aquí, lo que evaluamos es que cuando la fórmula sustituir sea verdadera, es cuando nos indicará que contiene el valor (dado que si lo sustituye es porque lo ha encontrado).

Este sería el resultado:

COMPROBAR SI UNA CELDA CONTIENE UN CARÁCTER O PALABRA ESPECÍFICA

Estoy seguro que existen más combinaciones para obtener el mismo resultado y con otras fórmulas, pero creo que estas cuatro os pueden servir en prácticamente todos los casos.

Y eso es todo, espero que os resulten de utilidad.

Descarga el archivo de ejemplo pulsando en: COMPROBAR SI UNA CELDA CONTIENE UN CARÁCTER O PALABRA ESPECÍFICA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

LISTAR TODAS LAS PALABRAS CONTENIDAS EN UNO O VARIOS ARCHIVOS

Hola a todos!

Hoy os traigo un post que ha surgido fruto de una consulta que me envía un lector, dice así:

“Buen dia , te consulto si con el objeto file puedo listar las palabras de cada archivo? gracias”

Esta consulta viene relacionada con el siguiente post: LISTAR LAS PROPIEDADES DE TODOS LOS ARCHIVOS DE UNA CARPETA Y SUBCARPETAS donde lo que hacía el código era lista todos los archivos contenidos en carpetas y subcarpetas y además obtener las diferentes propiedades de cada uno de ellos.

Pues bien, el lector me pregunta si es posible listar todas las palabras de cada uno de los archivos. Antes de seguir adelante, tengo que comentar que en un principio me entraron algunas dudas sobre la utilidad de realizar dicha tarea y sus implicaciones, sobre todo por el (potencial) gran volumen de información que se podría generar en cada consulta, (tantas filas como palabra contengan los archivos seleccionados). Pero siguiendo con la filosofía de esta web: “Si lo imaginas, lo puedes crear” decidí realizar la macro.

Veamos para comenzar a trabajar es necesario crear una serie de archivos que sean sobre los que vamos a extraer todas las palabras que contengan.

Imaginar dos archivos, con tres pestañas cada uno y con información en cada una, por ejemplo, en la primera tenemos estos datos:

LISTAR TODAS LAS PALABRAS CONTENIDAS EN UNO O VARIOS ARCHIVOS

Y así en el resto de los archivos:

Lo que vamos a pretender es listar todas esas palabras en una única columna desde nuestro archivo.

Para realizar este trabajo, utilizaremos la siguiente macro:

Option Explicit
Sub LISTAR_PALABRAS()
'Declaramos variables
Dim i As Integer, j As Integer, dir_Archivo As Variant
Dim Archivo_n As Workbook, elimina As Integer, nHoja As Integer
Dim target As Variant, celda As Variant, Dat As Variant, n As Integer
Application.ScreenUpdating = False
'Creamos ventana de diálogo para seleccionar los archivos que queremos agrupar
dir_Archivo = Application.GetOpenFilename(Title:="SELECCIONA ARCHIVOS PARA CONSOLIDAR", MultiSelect:=True, filefilter:="Excel files (*.xls*), *.xls*")
'Si no seleccionamos archivos, salimos del proceso
If Not IsArray(dir_Archivo) Then
Exit Sub
End If
'Si existen datos en la hoja AGRUPADO, los eliminamos
With ThisWorkbook.Sheets("Hoja1")
elimina = Application.CountA(.Range("A:A")) + 1
If elimina > 0 Then .Range("A1:A" & elimina).Clear
'Iniciamos un for con para identificar los archivos seleccionados
If IsArray(dir_Archivo) Then
For j = LBound(dir_Archivo) To UBound(dir_Archivo)
'obtenemos ruta al archivo seleccionado
Set Archivo_n = Workbooks.Open(Filename:=dir_Archivo(j))
'contamos las hojas del archivo seleccionado
nHoja = ActiveWorkbook.Worksheets.Count
'Iniciamiamos bucle.
For i = 1 To nHoja
'seleccionamos hasta la última fila con datos
Sheets(i).Select
With Sheets(i)
Range(.Cells(1, 1), ActiveCell.SpecialCells(xlLastCell)).Select
target = Selection
End With
'Por cada celda con datos en la selección
For Each celda In target
If celda <> "" Then
'Analizamos contenido y separamos por espacio cada palabra
For Each Dat In Split(celda, " ")
n = Application.CountA(.Range("A:A")) + 1
'Lo colocamos en la hoja de nuestro archivo
.Cells(n, 1) = Dat
Next Dat
End If
Next celda
Next i
'cerramos archivo seleccionado
Archivo_n.Close False
Next j
End If
End With
End Sub

Este código permite seleccionar directamente los archivos que necesitamos, sin necesidad de seleccionar la carpeta (se podría modificar para que lo hiciese en carpetas y subcarpetas).

El resultado de la macro sería el siguiente:

Primero os vuelvo a dejar el contenido de la primera hoja:

LISTAR TODAS LAS PALABRAS CONTENIDAS EN UNO O VARIOS ARCHIVOS

Y el resultado:

LISTAR TODAS LAS PALABRAS CONTENIDAS EN UNO O VARIOS ARCHIVOS_1

Hasta la línea 26 se corresponde con la primera hoja del primer archivo, lo que sigue es la segunda hoja (en la que ya no dejé encabezados).

Como podéis comprobar, la macro identifica cada palabra como aquel elemento separado por un espacio en blanco.

La macro no es compleja, simplemente incluimos los archivos seleccionados en un array y luego recorremos cada uno de ellos y sus hojas, seleccionando el rango con datos y detectando las palabras contenidas en cada celda para luego pasarlas a nuestra hoja.

Comentar que los datos se pasan a la columna A de nuestro archivo. Como siempre lo subo como .xls (lo que implica un límite de filas de 65.536), para tener mayor espacio solo tenéis que guardar el archivo como .xlsm o también programar parte de la macro que al llegar a determinado número de fila, comience a lista en la columna B y así sucesivamente (esto os lo dejo como tarea)

Y eso es todo, espero que os haya resultado de interés y en algún momento os sirva de utilidad la macro.

Descarga el archivo de ejemplo pulsando en: LISTAR TODAS LAS PALABRAS CONTENIDAS EN UNO O VARIOS ARCHIVOS

Archivos de prueba: EJEMPLO_1 EJEMPLO_2

 

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

PROGRAMAR LA FUNCIÓN SI.CONJUNTO EN VBA

Hola a todos!.

Esta semana recibí una consulta en la que me pedían que mostrase la sintaxis de la función SI.CONJUNTO en VBA. En realidad, creo que se refería a cómo se podía programar la función.

Esta función está disponible en Office 365, Excel 2019 y Excel 2016 y básicamente actúa como varias funciones SI anidadas. Os podéis informar sobre sus características en el link que os he dejado en el párrafo anterior.

Pero en VBA no la tenemos como función de hoja o como aplicación, es decir, lo que tenemos es lo que nos muestra el editor de VBA cuando grabamos una macro, algo así:

ActiveCell.FormulaR1C1 = "=IFS(RC[-2]=0,""OK"")"

Es decir con notación R1C1. Por lo que la programación de esta forma resulta bastante tediosa y es necesario realizar los ajuste necesarios para hacerla más dinámica. Para ello, vamos a utilizar variables que nos van a ayudar con nuestro proyecto.

Veamos un ejemplo muy simple:

PROGRAMAR LA FUNCION SI.CONJUNTO EN VBA

Supongamos estas reglas y que las queremos programar. Si en una celda ponemos un número superior a 89, indicaremos una A, si es superior a 79 indicaremos una B, y así sucesivamente.

Este sería el ejemplo:

PROGRAMAR LA FUNCION SI.CONJUNTO EN VBA_1

Para hacerlo operativo lo he programado en un evento de hoja “Worksheet_SelectionChange”:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Declaramos variables
Dim Cond As Variant, SICONJUNTO As String
With Sheets(1)
'Si dejamos la celda vacía, le damos valor 0
Cond = IIf(.Cells(2, 4) = "", 0, .Cells(2, 4))
'Programamos la función según reglas y utilizamos variable anterior
SICONJUNTO = "=IFS(" & Cond & " >89 , ""A"", " & Cond & " > 79, ""B"", " & Cond & " > 69 , ""C"" , " & Cond & " > 59 , ""D"" ," & Cond & " <= 59 , ""F"")"
'Igualamos el valor de la celda "D5" al resultado de la función
.Cells(5, 4) = SICONJUNTO
End With
End Sub

Como podéis observar, he programado la función SI.CONJUNTO utilizando cadenas de texto e incluyendo la variable Cond para pasar el criterio seleccionado.

El resultado que vamos a buscar es escribir la fórmula en la celda utilizando VBA, una técnica muy útil cuando no podemos invocar la función desde la propiedad Application.WorksheetFunction:

PROGRAMAR LA FUNCION SI.CONJUNTO EN VBA_2

Y eso es todo. Me gustaría indicar que hasta cierto punto la función Si.Conjunto en VBA es fácilmente replicable con IF y Else o ElseIf o con un Select Case. No obstante, aquí dejo la forma de hacerlo en VBA.

El archivo os lo paso con extensión .xls (dado que wordpress no admite .xlsm. Por otra parte, si usáis versiones que no admiten la función SI.CONJUNTO la macro no funcionará correctamente.

Descarga el archivo de ejemplo pulsando en: PROGRAMAR LA FUNCIÓN SI.CONJUNTO EN VBA

 

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

EJERCICIO PRÁCTICO CON LA FUNCIÓN SPLIT Y EL MÉTODO FIND

Hola a todos:

A lo largo de las semanas me van llegando consultas de los lectores, muchas de ellas requieren bastante trabajo de programación y en otros casos requieren un trabajo previo de análisis funcional. Como no puedo realizar un post con un ejercicio de este tipo, dado que sería muy extenso, creo que lo más interesante es realizar un ejemplo con algunas de las funciones utilizadas en esas consultas.

Por ejemplo, hoy quiero hablar sobre las funcionalidades de la función “Split” y su combinación con el método find.

Imaginamos un ejemplo práctico:

EJERCICIO PRÁCTICO CON LA FUNCIÓN SPLIT Y EL MÉTODO FIND

Lo que debemos hacer es confeccionar una función que nos permita obtener las sumas de los importes que se encuentran en la columna B y que coincidan entre la columna A y la celda D2. Como podéis observar en la celda D2 los números se encuentran delimitados por una “,”.

Para poder construir nuestro código debemos utilizar una función que nos permita extraer cada uno de los elementos de la celda D2, para ello tendremos que utilizar la función Split.

Para localizar esos elementos en la columna A, utilizaremos el método Find.

Podríamos realizar una macro directamente que funcionase con un botón de ejecución, pero para este ejemplo lo más práctico es una UDF (Función Definida por el Usuario).

Esta es la función que vamos a utilizar y que como siempre iremos comentando:

Option Explicit
Function SumaV(ByVal Target As Range)
'Declaramos variables
Dim Num As Variant, nCampo As Variant, sItem As Variant
Dim Numero As Double, Total As Double, nCol As String
Dim Celda As Range
'En la hoja activa
With ActiveSheet
'Recorremos celdas del rango seleccionado
For Each Celda In Target
'por cada dato delimitado buscamos en la columna1
For Each Num In Split(Celda, ",")
Set nCampo = .Range("A:A")
Set sItem = nCampo.Find(Num)
'Buscamos celda con el número buscado
nCol = sItem.Address
'Seleccionamos el valor de la siguiente columna
Numero = .Range(nCol).Offset(0, 1)
Total = Total + Numero
'Pasamos el valor al resultado
SumaV = Format(Total, "#,##0")
Next Num
Next Celda
End With
End Function

Lo primero que debemos tener en cuenta es que la función tendrá que seleccionar un rango (no solo una celda), por ello tenemos que utilizar una primera instrucción For Each que recorra el rango seleccionado, o lo que es lo mismo, las celdas seleccionadas.

For Each Celda In Target

Donde “Target” es el rango que seleccionemos.

Lo siguiente será realizar otra instrucción For Each por cada elemento delimitado en cada celda. Aquí es donde utilizaremos la función Split embebida en el ciclo:

For Each Num In Split(Celda, ",")

Es obvio que si estamos utilizando otro delimitador, como un punto y coma o una barra vertical, debemos modificarlo en el código.

Una vez que tenemos el item seleccionado, ahora debemos buscarlo en la columna A, para ello utilizaremos el métido Find (podríamos programar un buscar V o un For).

Set nCampo = .Range("A:A")
Set sItem = nCampo.Find(Num)
'Buscamos celda con el número buscado
nCol = sItem.Address

Con esto tendremos la celda de la columna A en la que hemos encontrado coincidencia. Pero necesitamos la cifra que debemos sumar, para ello vamos a utilizar la función Offset:

Numero = .Range(nCol).Offset(0, 1)

El resto ya es ir cerrando las dos instrucciones y proceder a sumar cada coincidencia.

El resultado es el siguiente:

EJERCICIO PRÁCTICO CON LA FUNCIÓN SPLIT Y EL MÉTODO FIND_1

Como habéis podido comprobar, la suma que realiza la función es la correcta.

Podríamos hacerlo con un rango de celdas y su resultado sería el correcto.

Descarga el archivo de ejemplo pulsando en: EJERCICIO PRÁCTICO CON LA FUNCIÓN SPLIT Y EL MÉTODO FIND

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

SELECCIONAR ARCHIVOS DE UNA CARPETA Y ENVIARLOS POR OUTLOOK SEGÚN CATÁLOGO DE INFORMES

Hola a todos!.

El envío de correos a través de Outlook utilizando VBA es un tema muy visto en manuales, web etc … que se dedican a Excel.

Como ya sabéis, trato de no publicar temas sobre los que la información existente ya es muy abundante. Principalmente porque no aportaría nada nuevo y por otra parte porque así me obligo a buscar alternativas y publicar cosas nuevas o poco publicadas.

En este caso, lo que os propongo es realizar un pequeño proceso mediante el cual vamos a detallar en una hoja una relación de nombres de archivos que normalmente enviamos a nuestros clientes y que luego vamos a seleccionar de la carpeta en la que se encuentran para finalmente enviarlos por Outlook.

Imaginad que tenemos una carpeta con una serie de informes:

SELECCIONAR ARCHIVOS Y ENVIARLOS POR OUTLOOK

Y queremos que seleccionando la carpeta EJEMPLOS se adjunten y se envíen por correo Outlook. Esto lo vamos a especificar de la siguiente manera en nuestro archivo Excel:

SELECCIONAR ARCHIVOS Y ENVIARLOS POR OUTLOOK_1

Como véis debemos indicar el nombre del archivo EXACTAMENTE igual que el que tenemos en la carpeta y sin la extensión.

Una vez que lo tenemos, ejecutaremos el siguiente código, compuesto por una macro que ejecuta una función:

Sub ENVIAR_CORREOS()
'Declaramos variables
Dim sFSO As Object, Directorio As String
Dim dir_Archivo As Variant
'Abrimos ventana de diálogo para seleccionar carpeta
Set dir_Archivo = Application.FileDialog(msoFileDialogFolderPicker)
dir_Archivo.Show
'Si no seleccionamos nada salimos del proceso
If dir_Archivo.SelectedItems.Count = 0 Then
Exit Sub
End If
'Capturamos el directorio
Directorio = dir_Archivo.SelectedItems(1)
'Creamos objeto y ejecutamos función Carpeta
Set sFSO = CreateObject("Scripting.FileSystemObject")
CARPETA sFSO.GetFolder(Directorio)
End Sub

Function CARPETA(ByVal nCarpeta)
'Declaramos variables
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim fin As Long, i As Long, File As Variant
Dim adjunto As String, nFile As String
Dim olMailItem As Variant, Celda As Variant
'Iniciamos función
With Sheets("Hoja1")
fin = Application.CountA(.Range("A:A"))
'recorremos hoja y celda para comprobar si hace
'referencia a varios archivos.
For i = 2 To fin
Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)
For Each Celda In Split(.Cells(i, 1), "|")
For Each File In nCarpeta.Files
adjunto = File
nFile = Left(File.Name, InStr(File.Name, ".") - 1)
If Celda = nFile Then
'Destinatario
olMail.To = .Cells(i, 2)
'Con copia a
olMail.CC = .Cells(i, 3)
'Con copia oculta
olMail.BCC = .Cells(i, 4)
'Asunto
olMail.Subject = .Cells(i, 1)
'Cuerpo de correo
olMail.HTMLBody = "Buenos días:
Les enviamos los archivos solicitados.
Atentamente."
' Adjuntamos archivo y dejamos correo en bandeja de salida
olMail.Attachments.Add (adjunto)
'Para enviar debéis utilizar Send en lugar de Display
'olMail.Send
olMail.Display
End If
Next File
Next Celda
olMail: Close
olApp: Close
Next i
End With
Set olMail = Nothing
Set olApp = Nothing
End Function

La macro principal selecciona la carpeta y la función recorre todos los archivos que contiene. En el momento que el archivo de la instrucción for-each es igual al archivo especificado en la hoja, procedemos a realizar el envío con Outlook.

Dado que tenemos también el correo del destinatario, con copia (CC) y con copia oculta (CCO) en la misma línea, podemos indicarlo en la macro para que los incluya.

Si queremos enviar a varios destinatarios a la vez, solo tenéis que indicar el correo electrónico seguido de un punto y como “;” y luego el siguiente email (como lo hacéis en Outlook).

Si lo que queréis es adjuntar varios archivos en un correo, debéis utilizar la barra como delimitador la barra vertical “|”. Por ejemplo en la siguiente imagen, el primer correo adjuntará los archivos “EJEMPLO” y “EJEMPLO_2”:

SELECCIONAR ARCHIVOS Y ENVIARLOS POR OUTLOOK_3

El resultado es el siguiente, los tres correos han sido generados y cada uno con su adjunto:

SELECCIONAR ARCHIVOS Y ENVIARLOS POR OUTLOOK_2

En el ejemplo de la macro los dejo en bandeja de salida, si queréis enviarlos directamente, debéis sustituir: olMail.Display por olMail.Send

Y eso es todo! Espero que os haya resultado de interés.

Descarga el archivo de ejemplo pulsando en: SELECCIONAR ARCHIVOS DE UNA CARPETA Y ENVIARLOS POR OUTLOOK SEGÚN CATÁLOGO DE INFORMES

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies

CONTAR CARACTERES ESPECÍFICOS EN UN RANGO SELECCIONADO

Hola a todos!.

La semana pasada, por diversos motivos, no me fue posible publicar el post habitual. Pero este sábado lo he podido escribir : )

Como sabéis hay varias fórmulas que nos sirven para contar caracteres determinados o específicos en una celda o un rango. Normalmente se suele trabajar con matriciales o combinación de fórmulas, sin embargo, y dado que con VBA podemos crear nuestras propias funciones, os voy a proponer crear nuestra propia fórmula para contar caracteres.

Vamos a imaginar que tenemos un texto, por ejemplo este:

El azafran silvestre o colchicum autumnale
NO ES EL AZAFRAN COMUN
ES UNA planta muy venenosa
que conviene saber identificar

CONTAR CARACTERES ESPECIFICOS EN UN RANGO SELECCIONADO

Y queremos contar el número de letras “m” que existen en todo el rango que en el que he escrito el texto.

Para ello vamos a usar una función que acabo de crear y que he nombrado como CONTARV:

Option Explicit
Public Function CONTARV(ByVal target As Range, ByVal Caracter As Variant)
'Definimos variables
Dim Celda As Range, nCar As Long, i As Long
Dim nCont As String
'Iniciamos primer loop por cada celda en el rango
For Each Celda In target
'Por cada celda recorremos todos los caracteres
For i = Len(Celda) To 1 Step -1
nCont = Mid(Celda, i, 1)
'Si alguno de los caracteres es igual al seleccionado
'lo contamos
If UCase(nCont) = UCase(Caracter) Then
nCar = nCar + 1
End If
Next i
Next Celda
'Pasamos el resultado al valor de la función
CONTARV = nCar
End Function

Según el ejemplo, aquí vemos el resultado:

CONTAR CARACTERES ESPECIFICOS EN UN RANGO SELECCIONADO_1

Como podéis comprobar, la macro nos ha contado el número de letras m en el texto, sean mayúsculas o minúsculas. Esto lo logramos igualando valores con la función UCase()

If UCase(nCont) = UCase(Caracter) Then

Si queréis que el resultado sea sensible a mayúsculas o minúsculas solo tenéis que borrar la función UCase(U).

El código funciona igual para contenido numérico, sin embargo, con el tema de las tildes, se debe especificar si la lleva o no.

Por otra parte, el segundo argumento de la función, en el que indicamos el caracter a contar, se puede especificar en una celda (como lo tenéis en el ejemplo) o en la función (entre comillas, sea número o texto):

=CONTARV(A3:A7;”m”)

Y eso es todo, como habéis visto, nos hemos creado nuestra propia UDF o función definida por el usuario, para solucionar una posible necesidad.

Espero que os haya resultado interesante  🙂

Descarga el archivo de ejemplo pulsando en: CONTAR CARACTERES ESPECÍFICOS EN UN RANGO SELECCIONADO

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies