RECORRER VARIOS ARCHIVOS Y SUS HOJAS Y BORRAR SU CONTENIDO CON VBA

Publicado el por

Hola a todos:

Algunas tareas muy comunes en Excel son las de tener que seleccionar varios libros y realizar la misma tarea en cada uno de ellos y también en sus hojas. En este blog hay varios post donde recorremos y consolidamos información de varios archivos.

Un lector me pedía hace unos días que publicase un post sencillo en el que explicase el proceso de seleccionar varios archivos y borrar todo su contenido, incluyendo todas las hojas.

En efecto, esto se puede realizar, pero antes de nada me gustaría advertir que tengáis precaución a la hora de probar la macro dado que borrará los archivos que hayáis seleccionado. ¿OK?. De todas formas he incluido una ventana de advertencia para elegir si borrar o no hacerlo : )

Pues bien, solo tenéis que incluir esta macro en un módulo estándar y si, queréis un botón de comando en una hoja o ejecutarlo directamente desde VBA:

Sub Borrando_archivos()
'Declaramos variables
Dim narchivos As Variant, ilibro As Object
Dim j As Long, nhojas As Long, i As Long, elige As Long
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Seleccionamos archivos Excel
narchivos = Application.GetOpenFilename(FileFilter:="Excel (*.xls*),*.xls", _
Title:="Seleccionar archivos a borrar", MultiSelect:=True)
'Si no hay selección salimos de la rutina
If IsArray(narchivos) = False Then Exit Sub
'Mostramos ventana de advertencia para verificar si deseamos borrar los archivos
elige = MsgBox("ADVERTENCIA: ¿DESEAS BORRAR EL CONTENIDO DE LOS ARCHIVOS SELECCIONADOS?", vbYesNo + vbExclamation, "BORRAR ARCHIVOS")
'si pulsamos si:
If elige = vbYes Then
'Iniciamos loop por cada archivo seleccionado
For j = LBound(narchivos) To UBound(narchivos)
'Abrimos libro
Set ilibro = Workbooks.Open(Filename:=narchivos(j))
'Contamos número de hojas
nhojas = Sheets.Count
'Recorremos cada hoja y borramos todo el contenido
For i = 1 To nhojas
Sheets(i).Select
Cells.Select
Selection.Clear
Next i
'Cerramos el archivo y guardamos cambios
ActiveWorkbook.Close True
'Seguimos con el próximo archivo
Next j
'si pulsamos no, salimos de la rutina
Else: Exit Sub
End If
End Sub

La macro es muy sencilla, cuando pulsamos el botón de comando o la ejecutamos se muestra una ventana en la que nos va a permitir seleccionar cualquier archivo excel en cualquier ubicación de nuestro equipo:

RECORRER VARIOS ARCHIVOS Y SUS HOJAS CON VBA

Si elegimos archivos, la macro continuará, si no lo hacemos y pulsamos en cancelar, la macro se detendrá y saldremos del proceso.

Como medida de seguridad, es conveniente incluir un msgbox para una segunda confirmación si hemos seleccionado algún archivo en el paso anterior. Es posible que nos hayamos equivocado en la selección y no deseemos eliminar esa información.

RECORRER VARIOS ARCHIVOS Y SUS HOJAS CON VBA1

Si todo está correcto y pulsamos en “SI” la macro se ejecutará y toda la información será borrada. Si elegimos “NO”, la macro no se ejecutará.

En este post es muy importante centrarse en la utilidad del código para recorrer archivos y hojas (mediante dos Loop), siempre será de utilidad para otras muchas tareas, no solo borrar información, también crearla, o consolidarla.

Y eso es todo, espero que os sea de utilidad.
Descarga el archivo de ejemplo pulsando en: RECORRER VARIOS ARCHIVOS Y SUS HOJAS UTILIZANDO 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!!

Anuncios

MOSTRAR RELACIÓN DE FECHAS ENTRE DOS FECHAS

Publicado el por

Hola a todos!.

Hace unos días me hacían una consulta a través de LinkedIn que trataba sobre cómo se podía generar automáticamente todas las fechas comprendidas entre dos fechas (inicial y final).

Aunque esto es posible realizarlo mediante fórmulas, creo que lo más oportuno es hacerlo con VBA, ya no solo por la rapidez sino por todas las posibilidades que ofrece para luego poder pasar estos datos a un formulario con cuadros de lista o combos.

Para este ejercicio voy a plantear dos situaciones, una en la que queremos obtener todas las fechas y otra en la que queremos obtener solo los días hábiles y omitiendo también festivos.

Veamos la primera, obtener todas las fechas, para ello imaginad que tenemos las siguiente fechas en nuestra hoja Excel:

MOSTRAR RELACION DE FECHAS ENTRE DOS FECHAS

y queremos obtener todas las fechas que comprenden desde el 01/05/2018 hasta el 15/05/2018. Para poder hacerlo vamos a utilizar esta macro:

Sub obtener_fechas()
'Declaramos variables
Dim inicio As Date, fin As Date, nCont As Date
Dim sCadena As String, fsem As String
Dim matriz As Variant, j As Double
With Sheets("FECHAS")
'Indicamos las celdas con la fecha inicio y fin
inicio = .Cells(1, 2)
fin = .Cells(2, 2)
'Mediante un loop añadimos un día a la fecha inicial
'y guardamos fechas en la variable sCadena
Do While inicio < fin - 1
nCont = DateAdd("w", 1, inicio)
inicio = nCont
sCadena = sCadena & " " & inicio
Loop
'pasamos la información de la matriz a la hoja
matriz = Split(sCadena, " ")
For j = 1 To UBound(matriz)
.Cells(j, 3) = Format(matriz(j), "mm/dd/yyyy")
Next j
End With
End Sub

Como podéis observar, mediante un loop vamos añadiendo días a la fecha inicial de forma que vamos componiendo cada fecha hasta llegar a la fecha final. Esos datos los guardamos en una variable string como una cadena de texto que luego pasaremos a una matriz y finalmente a la hoja, este es el resultado:

MOSTRAR RELACION DE FECHAS ENTRE DOS FECHAS1

Ahora vamos con la segunda situación, es decir, queremos obtener sólamente los días hábiles y excluir festivos, para ello utilizaremos la misma macro pero con unas modificaciones:

Sub obtener_fechas_dias_lab()
'Declaramos variables
Dim inicio As Date, fin As Date, nCont As Date, dfest As Date
Dim sCadena As String, fsem As String, j As Double
Dim matriz As Variant
With Sheets("FECHAS")
'Indicamos las celdas con la fecha inicio y fin
inicio = .Cells(1, 2)
fin = .Cells(2, 2)
'Mediante un loop añadimos un día a la fecha inicial
'y guardamos fechas en la variable sCadena
Do While inicio < fin - 1
nCont = DateAdd("w", 1, inicio)
inicio = nCont
fsem = Format(inicio, "ddd")
dfest = CDate(.Cells(3, 2))
'Si el día es un sábado o domingo o un festivo no guardamos fecha
If fsem <> "sá." And fsem <> "do." And inicio <> dfest Then
sCadena = sCadena & " " & inicio
End If
Loop
'pasamos la información de la matriz a la hoja
matriz = Split(sCadena, " ")
For j = 1 To UBound(matriz)
.Cells(j, 4) = Format(matriz(j), "mm/dd/yyyy")
Next j
End With
End Sub

Para poder excluir los sábados y los domingos es necesario que podamos detectar los días en las fechas que vamos generando en el loop, esto lo hacemos con:

Format(inicio, "ddd")

Donde los sábados se representan como: “sá.” y los domingos como “do.“, es obvio que si estamos trabajando con otros idiomas debemos modificar e introducir los datos correspondientes . Con un condicional excluimos los sábados y los domingos y también la fecha del día festivo, finalmente la información es la siguiente:

MOSTRAR RELACION DE FECHAS ENTRE DOS FECHAS2

Y con esto ya hemos finalizado el ejercicio, ya podemos obtener los días entre dos fechas y listalos, (con o sin días laborales).

Espero que os resulte de utilidad!.

Descarga el archivo de ejemplo pulsando en: MOSTRAR RELACIÓN DE FECHAS ENTRE DOS FECHAS

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

PROBANDO ALGORITMO PREDICTIVO ID3 CON RESULTADOS DE DIAGNÓSTICOS ONCOLÓGICOS.

Publicado el por

Hola a todos:

Como muchos de vosotros sabéis, esta web la creé en su momento como una forma de transmitir todo el conocimiento que iba aprendiendo sobre programación en particular y Excel en general. En cierto modo una forma de compartir el tiempo que dedico a mi hobbie que es programar y aprender a hacer cada día un poco mejor (nadie nace experto y dudo mucho que lleguemos a serlo en algún momento, todos debemos comprender la importancia de la humildad).

Pues bien, hoy me apetece escribir acerca de otros trabajos e investigaciones que voy realizando por cuenta propia y con el poco tiempo del que dispongo actualmente (ya sabéis, la vida, las obligaciones profesionales, etc).

Hace un tiempo escribí un post en el que me atrevía a programar parte de un algoritmo ID3, cuyo funcionamiento se basa en los árboles de decisión. Si no lo habéis leído o visto, este es el enlace: GENERANDO UN ALGORITMO ID3 EN VBA

Sé que en el mercado existe software muy potente con este tipo de algoritmos programados y embebidos en programas específicos, pero me gusta probar, investigar y trabajar y sobre todo el contestarme a la pregunta ¿eso lo puedo hacer programando en Excel?.

Pues bién finalmente lo realicé y me llevó un par de semanas trabajando de veinte minutos a una hora cada día (no disponía de más tiempo).

Hace tiempo encontré documentación sobre un tema muy concreto y que me interesa enormemente, ya no tanto por su implicación actual en la vida de las personas sino porque en el pasado me tocó sufrirlo de cerca con un familiar muy cercano, esto es: el cáncer.

Esta documentación la podéis encontrar en la UCI MAchine Learning UCI Machine Learning Repository un lugar web de naturaleza pública para que investigadores o personas interesadas en el machine learning puedan tener a su disposición datos con los que poder investigar.

En el caso del cáncer hay varios repositorios con documentación extensa, en mi caso, me decidí por este: machine-learning-databases breast cancer wisconsin

Aquí podréis encontrar una base de datos con diversos indicadores y su medición en una relación de casos:

PRACTICUM1

En rojo os marco los archivos necesarios, el primero son los datos recogidos en un archivo plano txt delimitado por comas:

PRACTICUM2

Para determinar el nombre de los campos y la escala de cada uno de ellos es necesario abrir el otro archivo con las especificaciones, y entre otras informaciones nos mostrará lo siguiente:

PRACTICUM3

Efectivamente, aquí tenemos el nombre de todos los campos, incluido el último, como variable dicotómica (2 – el tumor es benigno y 4 – el tumor es maligno).

Pues bien, con esta información creí interesante utilizar el algoritmo que había creado en Excel basado en el ID3 para comprobar cuál era el resultado que mostraba y si podría tener sentido.

Lo primero que hice fue crear la base de datos e incluirla en la hoja Excel:

PRACTICUM4

El resto del proceso es sencillo, solo hay que pulsar el botón “Generar Algoritmo” que se encuentra en la hoja Resultados.

La información obtenida fue la siguiente:

PRACTICUM5

Aunque la información que se muestra es extensa, podemos extraer una serie de conclusiones inicialmente:

En primer lugar, de todas las variables que hemos analizado o procesado, son dos cláramente las que determinan el resultado es 2 o 4, estas variables que voy a denominar explicativas, son: la uniformidad en el tamaño de la célula y la uniformidad en la forma de la célula.

Sin embargo, en vista de otros estudios, parece que la tercera variable “el núcleo desnudo” sería también determinante.

En este caso, mi algoritmo está solo programado para analizar aquella variable con el atributo cuya ganancia sea máxima, es decir la uniformidad en el tamaño de la célula.

Posteriormente obtendremos el valor o ganancia de cada una de las variables en los diferentes nodos de la variable principal, es decir, la uniformidad va desde el 1 (más uniforme, hasta el 10 (menos uniforme).

Según los resultados en los niveles 1 y 10 no existe ningún indicador que determine si podría ser maligno o benigno, dado que el peso de cada variable en todos los casos es 0.

Cuando se trata de una uniformidad total el resultado tiene a ser benigno, sin embargo es necesario observar el comportamiento de los núcleos desnudos y los nucleolos normales. En los niveles 2, 3 y 4 serán determinantes los núcleos desnudos y el espesor de la masa y a partir del nivel 6 las probabilidades de un resultado maligno son mayores, dado que son multitud de variables las que tendrían incidencia o habría que tener en cuenta.

Quizás para resolver los casos 5 y 10 sean necesarios más casos en la muestra con la que estamos trabajando y en general muy seguramente sea necesario ampliar la muestra para obtener mayores evidencias.

Los datos indican que cuanto mayor es la uniformidad de la célula y (muy probablemente también la uniformidad de la forma) el resultado sería maligno. Aunque es necesario también tener en cuenta el “peso” del resto de las variables en cada uno de los niveles de la variable principal.

Existe un estudio sobre esta misma fuente de datos que no puedo dejar de mencionar, aunque se ha realizado con el algoritmo C5. Este estudio lo podéis descargar aquí: Prediction of Breast cancer y aunque sus resultados vienen dados por otro tipo de plataforma y algoritmo, coincidimos en bastantes conclusiones. Y la más importante es que es necesario un mayor número de casos y quizás implementar un algoritmo mucho más preciso que el C5.

Por último, esto solo se trata de un ejercicio a modo de ejemplo y sin mayores pretensiones que mostrar su funcionamiento. La información siempre ha de ser tratada y contrastada por profesionales que conozcan los conceptos, yo no soy médico ni tampoco tengo conocimientos en este área de trabajo, pero sí he tratado de construir un método para obtener una serie de informaciones.

Me gustaría disponer de más tiempo para seguir desarrollando, pero desgraciadamente no dispongo de tal ventaja. La solución es ir haciéndolo poco a poco y con los instrumentos que tengo a mi alcance : )

Estas son las cosas que hago y con las que disfruto, investigando, aprendiendo, equivocándome y volviéndolo a intentar.

Os dejo el caso práctico:

Descarga el archivo de ejemplo pulsando en: PROBANDO ALGORITMO PREDICTIVO ID3 CON RESULTADOS ONCOLÓGICOS

 

ELIMINAR UNA MACRO UTILIZANDO OTRA MACRO

Publicado el por

Hola a todos:

Hace unos días un lector me enviaba una consulta acerca de cómo podía eliminar una macro utilizando otra macro.

Aunque no suele ser muy habitual este tipo de procedimientos, dado que implica que si nos equivocamos podemos eliminar código o macros por equivocación, la verdad es que me pareció interesante para publicar en la web.

Antes de comenzar con el ejercicio, para que la macro funcione es necesario realizar unas modificaciones en el centro de confianza de nuestro programa de Excel. Para hacerlo, debéis entrar en Archivo > Opciones y pulsar en Centro de Confianza:

ELIMINAR UNA MACRO UTILIZANDO OTRA MACRO

A continuación se mostrará esta otra pantalla:

ELIMINAR UNA MACRO UTILIZANDO OTRA MACRO1

Elegimos la opción Configuración de macros y activamos la casilla que pone: Confiar en el acceso al modelo de objetos de proyectos de VBA, y aceptamos.

Es importante que realicemos este paso, si no lo hacemos la macro que os voy a mostrar generará un error y no se ejecutará.

Ahora veamos dos formas de realizar el borrado de nuestros procesos. Por una parte podemos eliminar el contenido de un módulo entero, aunque tenga varios sub o funciones. Simplemente queremos vaciar de contenido todo el módulo.

Para realizar esto, este es el código que podemos utilizar:

Sub Borrar_módulo()
'Declaramos variables
Dim modulo As String
'Eliminamos todo el contenido del módulo que indiquemos
modulo = "Módulo1"
With ThisWorkbook
.VBProject.VBComponents(modulo).CodeModule.DeleteLines 1, .VBProject.VBComponents(modulo).CodeModule.CountOfLines
End With
End Sub

En este caso estamos eliminando todo el contenido del módulo 1, si quisiéramos borrar el contenido “ThisWorkBook”, simplemente lo indicamos en la macro sustituyéndolo en la macro.

Pero también puede que solo deseemos eliminar un procedimiento concreto y no todo. Para eso, utilizaremos el siguiente código:

Sub Borrar_macro()
'Declaramos variables
Dim fin As Long
Dim inicio As Long
Dim modulo As Object
'Indicamos el módulo en el que queremos borrar la macro
Set modulo = ThisWorkbook.VBProject.VBComponents("Módulo1").CodeModule
'Indicamos el nombre del procedimiento o macro a eliminar
With modulo
inicio = .ProcStartLine("Borrar_módulo", vbext_pk_Proc)
fin = .ProcCountLines("Borrar_módulo", vbext_pk_Proc)
.DeleteLines inicio, fin
End With
End Sub

En este caso borraremos solo este código especificando el nombre de la subrutina anterior (Borrar_módulo) y que esté incluida en el módulo 1 de nuestro proyecto de VBA.

Obviamente podremos cambiar los valores en función de nuestra necesidades.

No voy a dejar archivo de ejemplo porque no estoy interactuando con la hoja ni vamos a crear datos, y no creo que sea necesario dado que solo necesitáis el código para y leer el post para poder realizar el ejemplo por vosotros mismos. De todas formas si tenéis dudas, me comentáis.

Por cierto, cuidado con este tipo de programaciones, recordad que estamos borrando código y debéis ser muy cuidadosos a la hora de programar, ¿ok?.

Eso es todo, espero que os haya gustado!. : )

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

INSERTAR UN CERO DESPUÉS DE UN CARÁCTER DETERMINADO EN UNA CADENA ALFANUMÉRICA

Publicado el por

Hola a todos!

Hace unos días recibí una consulta en la que me preguntaban cómo se podía insertar un cero en una cadena de texto (o numérica) después de un carácter determinado. Por ejemplo, si tenemos esto:

a1sAttPOMMJ2 y queremos que se inserte un cero después de cada A mayúscula, es decir que quedase así: a1sA0ttPOMMJ2

Para poder realizar este trabajo, he optado por descomponer la cadena de texto inicial mediante un loop que recorra todos los caracteres y en el momento que encuentre la A, entonces insertamos un 0 en la nueva cadena.

Es decir, vamos a utilizar este código y compondremos una sencilla función que nos facilitará la tarea:

Public Function AÑADE_0(Micelda As String)
'Declaramos variables
Dim i As Double, cero As Variant
Dim letr As String
With Sheets(1)
For i = Len(Micelda) To 1 Step -1
'Si la letra es una A activamente la variable "cero"
If Mid(Micelda, i, 1) = "A" Then
cero = 0
End If
'Añadimos el cero a la nueva cadena
letr = Mid(Micelda, i, 1) & cero & letr
cero = vbNullString
Next i
AÑADE_0 = (letr)
End With
End Function

Los ceros los vamos a insertar por la derecha, después del carácter indicado.

El resultado es que podemos utilizar la UDF (que he denominado AÑADE_0) como si se tratase de otra función más y obtener este resultado:

INSERTAR UN CERO DESPUES DE UN CARACTER DETERMINADO EN UNA CADENA ALFANUMERICA

Descarga el archivo de ejemplo pulsando en: INSERTAR UN CERO DESPUÉS DE UN CARÁCTER DETERMINADO EN UNA CADENA ALFANUMÉRICA

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

MOSTRAR INFORMACIÓN DEL SISTEMA DESDE EXCEL CON VBA

Publicado el por

Hola a todos:

Casi siempre suelo contestar a todos los correos que me envíais en un tiempo razonable, pero en algunas ocasiones puedo retrasarme, los motivos son muy variables, desde que no dispongo de tiempo, que se me pasó el correo, o que vuestra consulta llegó por error a la carpeta de spam del buzón de la web, en esos casos, lo siento.

No obstante, aunque el correo sea de hace tiempo, suelo contestar y ponerme en contacto con la persona que me ha enviado la consulta.

Hoy es uno de esos casos (correo en spam de la web), y la pregunta era bastante sencilla, el lector quería saber si existía algún procedimiento para poder invocar/mostrar la ventana de información del sistema desde Excel.

No se trata de mostrar la información en excel sino mostrarla en windows abriendo la ventana desde Excel.

Una macro que nos puede venir muy bien es esta, donde directamente ejecutamos un .exe para mostrar la información del sistema con la función “Shell”

Sub Info_sistem()
Dim sistema As Long
sistema = Shell("C:\Program Files\Common Files\Microsoft Shared\MSINFO\MSINFO32.EXE", vbMaximizedFocus)
End Sub

Si esta información la tenéis en otra ubicación o recibe otra denominación, solo tenéis que buscarla en vuestro sistema y sustituir la cadena de conexión.

El resultado es el siguiente:

MOSTRAR INFORMACION DEL SISTEMA DESDE EXCEL CON VBA

Como podéis observar he eliminado alguna información de mi sistema para no hacer datos públicos. Pero cuando lo ejecutéis en vuestro equipo tendréis toda la información disponible.

Y esto es todo, espero que este sencillo código os pueda ayudar.

Y de nuevo perdonad si tardo más de la cuenta en contestar a vuestras consultas : )

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

BLOQUEAR EQUIPO DESDE EXCEL USANDO VBA

Publicado el por

Hola a todos, ¿qué tal estáis?.

Ayer por la noche un lector me preguntaba cómo podría incluir un botón de comando en su hoja Excel para poder bloquear su equipo cada vez que lo pulsase. Al parecer, la mayor parte del tiempo de su jornada laboral tiene que estar controlando un cuadro de mando realizado en Excel y en muchas ocasiones cuando se ausenta de su puesto de trabajo debe dejar el equipo bloqueado (al instante) pulsando control + alt + supr y luego indicar “bloquear” y por eso quiere que con solo pulsar un botón lo haga.

Esto se puede realizar también con Excel, en concreto utilizando la función LockWorkStation y programando en VBA. Es bastante sencillo de realizar y basta con unas pocas líneas de código.

La forma más sencilla de hacerlo es así:

Declare Function LockWorkStation Lib "user32.dll" () As Long
Sub Bloquear_equipo()
LockWorkStation
End Sub

En efecto, declaramos la función haciendo referencia a la librería y luego lo ejecutamos en un procedimiento Sub.

Obviamente, podemos incluir una multitud de tareas o modificaciones, por ejemplo llamando a la función desde ThisWorkBook en cualquiera de los eventos predefinidos, por ejemplo cuando cambiemos de hoja, cuando el libro no esté activo, etc.

Es un código muy simple, pero en algunas circunstancias puede ser interesante, sobre todo cuando desarrollamos nuestro trabajo utilizando la misma hoja o el mismo libro durante mucho tiempo.

También se podría incluir otra línea de código que además de bloquear el equipo, antes realice una copia de seguridad de nuestro archivo. Pero esa parte os la dejo a vosotros para que vayáis investigando poco a poco : )

Espero que os haya resultado de utilidad!.

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

Donate Button with Credit Cards

¡¡Muchas gracias!!