lunes, 25 de junio de 2012

Volviendo a la desprotección con una macro

Aunque en el artículo Protección mejorada se dio la impresión de que es imposible desproteger una hoja cuando se ha puesto contraseña de protección en Propiedades de VBAProject, usando la macro descargada de Cómo quitar la protección de una hoja sin conocer la contraseña, la realidad es que se puede utilizar la macro de una forma muy sencilla. Estos son los pasos que hay que dar:
  1. Abrir el libro que contiene la doble protección (en la hoja y en Propiedades de VBAProject).
  2. Desde ese libro, abrir otro libro nuevo.
  3. Importar la macro al nuevo libro.
  4. Acceder a la hoja que queremos desproteger y ejecutar la macro incorporada en el otro libro.
De nuevo, usaremos el fichero "SE99-Palíndromo.xlsm", que ya tiene la doble protección y que podemos descargar desde aquí. La versión para Excel 2003 se puede descargar desde aquí.

Abrimos el libro, comprobamos que el rango C3:C19 está protegido con contraseña y que en VBA nos pide la contraseña cuando intentamos hacer algo.

Hacemos clic en Archivo + Nuevo + Libro en blanco + Crear para abrir un nuevo libro. Por defecto, este libro se llamará "Libro1". Ni siquiera es necesario guardarlo; lo utilizaremos durante el tiempo necesario para desproteger nuestra hoja y luego lo eliminaremos.

Estando en "Libro1", pulsamos Alt + F11 para entrar en VBA, accedemos a Archivo + Importar archivo y buscamos la macro que habremos descargado previamente desde aquí. Una vez incorporada la macro, salimos de VBA pulsando Alt + Q.

Nos situamos en la "Hoja1" del libro "SE99-Palíndromo.xlsm", accedemos a Vista + Macros + Ver macros, seleccionamos Libro1!BuscarContraseña y pulsamos el botón Ejecutar (en Excel 2003 hay que acceder a Herramientas + Macro + Macros). Al cabo de una rato, se mostrará el mensaje siguiente:

La contraseña está quitada. Cerramos "Libro1" pero no lo guardamos (ya no nos hace falta) y seguimos trabajando con la hoja original.

Este método vale para Excel 2003, 2007 y 2010.


jueves, 21 de junio de 2012

Otra forma de desproteger una hoja

A partir de la versión 2007, Excel utiliza un sistema de archivos denominado Open XML. Los libros contienen paquetes en forma de archivos XML comprimidos en formato ZIP especial. Si sustituimos la extensión de un fichero Excel por .ZIP, podremos ver los documentos XML que componen el libro. Además, podremos manipular estos documentos para, por ejemplo, eliminar la protección de una hoja. Aquí enlazamos con el artículo Protección mejorada donde se planteó este problema y quedó pendiente de solución.

El fichero utilizado para el ejemplo será "SE99-Palíndromo.xlsm", que si se descarga desde aquí se comprobará que el rango C3:C19 está protegido con contraseña y que se ha puesto otra contraseña en Propiedades de VBAProject. Desconocemos ambas contraseña y nuestro objetivo es desproteger la hoja.

El primer paso consiste en sustituir la extensión del fichero por .ZIP. Windows pide confirmación.
Ahora, descomprimimos el fichero "SE99-Palíndromo.ZIP". Obtendremos varias carpetas, subcarpetas y ficheros.

Abrimos la carpeta "xl". Dentro hay más carpetas y ficheros.


Abrimos la carpeta "worksheets". En el interior hay tantos ficheros XML como hojas tiene el libro. En nuestro caso sólo hay uno.


Con el Bloc de notas de Windows, abrimos el fichero "sheet1.xml".


Con Edición + Buscar, buscamos la palabra "password".


Seleccionamos y borramos: <sheetProtection password="86C7" sheet="1" objects="1" scenarios="1" selectLockedCells="1"/>


Guardamos el fichero (Archivo + Guardar) y cerramos el Bloc de notas. El fichero "sheet1.xml" ya no tiene contraseña.

A continuación, volvemos a comprimir todos los ficheros y carpetas con WinZip. De este modo, obtendremos un nuevo fichero "SE99-Palíndromo.ZIP" modificado. Le cambiamos la extensión por .xlsm y la hoja quedará desprotegida, aunque la protección que hay en Propiedades de VBAProject permanece.

Este método también vale para libros con protección simple; es decir, libros a los que no se les ha puesto protección en Propiedades de VBAProject.

En libros de Excel 2003 el método no funciona ya que Office 2003 no usa el sistema de archivos Open XML. Sin embargo, si tenemos instalado Excel 2010 podemos resolver fácilmente el problema de la siguiente manera:
  1. Abrimos el libro con Excel 2010.
  2. Lo guardamos con extensión .xlsx (si no tiene macros) o .xlsm (si tiene macros).
  3. Quitamos la protección como se ha indicado.
  4. Volvemos a abrir el libro con Excel 2010 y lo guardamos con extensión .xls para que pueda ser abierto con Excel 2003.


miércoles, 20 de junio de 2012

Protección mejorada

Ya hemos comprobado que la protección que ofrece Excel tiene poca consistencia y que se pueden descubrir fácilmente las contraseñas. Sin embargo, hay otro nivel de protección que dificulta (aunque no impide) la desactivación de las contraseñas.

Para poder aplicar esta protección en Excel 2010 es necesario que el libro tenga, al menos, una macro (aunque esté vacía). En Excel 2003 no es preciso este requisito. La prueba la podemos hacer con el fichero "SE99-Palíndromo.xlsm" empleado en el artículo Palíndromos (3 de 3).

Después de entrar en el editor VBA pulsando Alt + F11, accedemos a Herramientas + Propiedades de VBAProject. En la pestaña Protección, marcamos el cuadro de verificación Bloquear proyecto para visualización, ponemos una contraseña y la repetimos en la fila siguiente.

Pulsamos Aceptar para cerrar el cuadro de diálogo y Alt +Q para salir de VBA.

Ahora, protegemos las celdas que contienen fórmulas, como se ha explicado en el artículo Cómo se protege una hoja. La nueva contraseña puede ser distinta de la anterior.

Cuando un usuario pretenda hacer una modificación, Excel le pedirá la contraseña con la que está protegida la hoja. El usuario, que no la conoce, sonreirá astutamente pensando que puede engañar a Excel con la macro que se ha bajado del artículo Cómo quitar la protección de una hoja sin conocer la contraseña. Pronto llegará la decepción, porque cuando entre en el editor de VBA para copiar o importar la macro e intente acceder a Archivo + Importar archivo, se encontrará con que la opción buscada está desactivada.

Lleno de ansiedad, buscará afanosamente otra solución e intentará escribir la macro manualmente accediendo a Insertar + Módulo, pero la situación será la misma.

Desesperado, apagará el ordenador y lo tirará por la ventana. Craso error, la protección puede desactivarse. Para descubrir cómo hacerlo sólo hay que esperar a que se publiquen los dos próximos artículos donde se desvelarán sendas claves para evitar el desespero de los impacientes.

martes, 19 de junio de 2012

Cómo quitar la protección de una hoja sin conocer la contraseña

En el artículo anterior hemos visto cómo proteger con contraseña una hoja Excel. Para hacer cambios en la hoja necesitaremos la contraseña, pero en caso de no conocerla podemos recurrir al truco que se explica a continuación.

La protección de las hojas que proporciona Excel es muy débil. Cualquier contraseña que introduzcamos se transforma internamente en una palabra de 12 caracteres; los once primeros son una "A" mayúscula (ASCII 65) o una "B" mayúscula (ASCII 66), mientras que el último carácter puede variar entre el ASCII 32 y el ASCII 126. Bastará crear una macro que compruebe sistemáticamente todas las posibles combinaciones con estos caracteres hasta que halle la palabra correcta.

La macro es la siguiente:

Para no tener que escribirla, podemos descargarla desde aquí. La guardamos en el Escritorio o en la carpeta que queramos y entramos en el editor de VBA pulsando Alt + F11. Elegimos Archivo + Importar archivo, buscamos el fichero "BuscarContraseña.bas" y aceptamos. En el Explorador de proyectos aparecerá una nueva entrada, llamada Módulos, y debajo Módulo1. Haciendo doble clic en Módulo1 podremos ver la macro ya insertada.

Salimos del editor de VBA pulsando Alt + Q.

Para ejecutar la macro, vamos a la hoja que queremos desproteger (si hay varias, se desprotegen de una en una), accedemos a Programador + Macros, seleccionamos BuscarContraseña (en el ejemplo sólo hay una macro pero en otros libros puede haber más) y pulsamos el botón Ejecutar.

Al cabo de un rato, la macro encontrará la contraseña y mostrará un mensaje similar al siguiente:

La hoja ha quedado desprotegida. Ya no hace falta la macro y conviene eliminarla. Para quitarla, pulsamos Alt + F11, hacemos clic con el botón derecho en Módulo1 y, en el menú emergente, elegimos Quitar Módulo1. Se mostrará el mensaje siguiente:

Como ya hemos descargado el fichero "BuscarContraseña.bas", no necesitamos exportarla a ningún sitio; por tanto, pulsamos el botón No. Módulo1 habrá desaparecido.

Salimos de VBA pulsando Alt + Q y guardamos el libro con la hoja perfectamente desprotegida.

lunes, 18 de junio de 2012

Cómo se protege una hoja

Cuando en una hoja de cálculo hay datos críticos, formulas, formatos... que no deben ser modificados, es conveniente protegerla (con contraseña o sin ella) para evitar problemas. Los pasos que deben darse son muy sencillos:
  1. Seleccionar toda la hoja y desbloquearla.
  2. Seleccionar las celdas que queremos proteger y bloquearlas.
  3. Proteger la hoja.
  4. Guardar el libro.
Vamos a hacerlo, paso a paso, con el fichero SE21-Más vendidos.xlsx del artículo Obtener encabezados. Este libro tiene dos hojas; podemos proteger las dos hojas pero, para ilustrar el procedimiento, bastará hacerlo con la hoja "Método 1".

Hacemos clic con el botón derecho en el cuadrado que hay encima de la fila 1 y a la izquierda de la columna A.

En el menú emergente, elegimos Formato de celdas. En la ficha Proteger, nos aseguramos de que estén desmarcadas las dos opciones y pulsamos Aceptar. Toda la hoja ha quedado desbloqueada.

Ahora, hay que bloquear las celdas que queremos proteger. En nuestro ejemplo, protegeremos todas las celdas que tienen fórmulas. Para no tener que buscarlas y seleccionarlas manualmente, dejaremos que las encuentre Excel.

Accedemos a Inicio + Buscar y seleccionar + Fórmulas. Habrán quedado seleccionados los rangos C15:E15 y H4:K4.

Con el botón derecho hacemos clic en cualquier celda de los dos rangos mencionados y elegimos Formato de celdas. Volveremos al cuadro de diálogo anterior en el que marcaremos los dos cuadros de verificación.

Todavía no hemos protegido la hoja; lo haremos en el siguiente paso. Accedemos a Revisar + Proteger hoja, marcamos Proteger hoja y contenido de celdas bloqueadas, ponemos una contraseña (es opcional) y, en los cuadros de verificación inferiores, marcamos las opciones que queramos que los usuarios puedan modificar libremente. Si hemos puesto contraseña, al pulsar Aceptar, Excel nos pedirá que repitamos la contraseña.

Comprobamos que es imposible modificar ninguna fórmula y guardamos el libro.

En las celdas no protegidas podemos hacer cualquier cambio, pero si necesitamos modificar alguna fórmula, hay que desproteger previamente la hoja eligiendo Revisar + Desproteger hoja. Si hemos puesto contraseña, Excel nos la pedirá:

Escribimos la contraseña, pulsamos Aceptar, hacemos los cambios que queramos y volvemos a proteger la hoja.


lunes, 11 de junio de 2012

¿Cuántos miércoles hay entre dos fechas?

Vamos a calcular el número de miércoles (o cualquier otro día de la semana) que hay entre dos fechas. Los únicos datos que dispondremos serán la fecha inicial (B2), la fecha final (B3) y el día de la semana (B4).

En D2:E6 obtendremos diversos valores intermedios que nos servirán para poner la fórmula definitiva en B6.

Supongamos que la fecha inicial es el 5 de junio de 2012, la final, el 27 de junio de 2012 y el día elegido, el miércoles.

Comenzaremos obteniendo en E2 el día de la semana de la fecha inicial.

En E2:
=DIASEM(B2;2)    [Resultado: 2]

El siguiente paso consistirá en calcular los días que hay entre las dos fechas.

En E3:
=B3-B2    [Resultado: 22]

Ahora, calcularemos las semanas completas que hay entre la fecha inicial y la final.

En E4:
=COCIENTE(E3;7)     [Resultado: 3]

Nota: En Excel 2003 la función COCIENTE sólo está disponible si está cargado el complemento Herramientas para análisis (Herramientas + Complementos + Herramientas para análisis). Si no queremos usar COCIENTE, la fórmula se puede sustituir por: =ENTERO(E3/7)

Tres semanas completas significa que hay 3 miércoles, pero aún queda 1 día más, que podría tocar miércoles, con lo que el número total de miércoles sería 4. Para comprobarlo, necesitamos calcular los días sobrantes.

En E5:
=RESIDUO(E3;7)    [Resultado: 1]

Para saber si alguno de los días sobrantes (en nuestro caso sólo hay uno) cae en miércoles, debemos calcular el número de la semana que le corresponde al miércoles (el día que hayamos elegido en B4).

En E6:
=COINCIDIR(B4;{"lunes";"martes";"miércoles";"jueves";"viernes";"sábado";"domingo"};0)   [Resultado: 3]

Ya tenemos todos los datos necesarios: hay tres semanas completas, lo que significa que hay tres miércoles (valor de la celda E4), y un día adicional (pueden haber hasta seis días adicionales) que hay que comprobar si es miércoles. Si lo es, se añade un día más; en caso contrario, se añaden cero días.

En B6:
=E4+SI(Y(E2<=E6;E2+E5>=E6);1;SI((E2+E5-7)>=E6;1;0))    [Resultado: 4]

 
 
 

miércoles, 6 de junio de 2012

Control de asistencia

Vamos a hacer una hoja de control de asistencia para un curso de formación que se desarrollará los jueves (3 horas) y los viernes (2 horas) desde el 6 de septiembre de 2011 hasta el 30 de mayo de 2012. La aplicación deberá ser lo más versátil posible adaptándose automáticamente a cualquier circunstancia para que se pueda usar en cursos venideros.

El libro tendrá 7 hojas. La hoja "Datos" contendrá información básica: inicio y fin de cada una de las tres fases del curso, número de horas que se impartirá cada día, días festivos y resumen de las horas impartidas.

La hoja "Primera" tendrá una lista de participantes en el curso y espacio para poder anotar las asistencias los días que haya cursillo.

Las hojas "Segunda" y "Tercera" son similares a la hoja "Primera" pero las fechas de las columnas corresponden a cada una de las siguientes fases del curso. La aplicación deberá crear automáticamente las hojas "Primera", "Segunda" y "Tercera", poniendo las fechas de los encabezados y los bordes de las celdas. Además, habrá otras tres hojas auxiliares: "AuxP", "AuxS" y "AuxT".

En las celdas F7:L7 de la hoja "Datos" ponemos las horas que se van a impartir cada día. En el ejemplo que vamos a desarrollar habrá clase únicamente los jueves (3 horas) y los viernes (2 horas).

Si un día no hay clase, en la celda inferior colocaremos un cero; en caso contrario, un uno. Esto nos servirá para hacer cálculos más adelante.

En F8:
=SI(F7>0;1;0)    [Extender la fórmula hasta la celda L8]

Ahora, debemos trabajar con la hoja "AuxP" que nos servirá para obtener los datos con los que construiremos la tabla de la hoja "Primera". Cuando la terminemos, su aspecto será:

En la fila 2 pondremos todas las fechas comprendidas entre el inicio del curso y una fecha posterior a la terminación de la primera parte. Dejaremos un margen suficiente pensando en futuros cursos.

En la fila 3 pondremos cero los días que no haya cursillo y dejaremos los días cuando haya cursillo. Para que se vea mejor, sustituiremos el formato de fecha por el formato general.

En la fila 4 dejaremos en blanco las celdas con ceros y mantendremos las fechas de las restantes. Aquí usaremos el formato de fecha.

En la fila 5 numeraremos las fechas de la fila 4.

En la fila 6 colocaremos todas las fechas de la fila 4 sin dejar celdas en blanco ayudándonos de la numeración de la fila 5.

Comencemos a poner las fórmulas de la hoja "AuxP".

En C2:
=Datos!D5

Hemos puesto en C2 la fecha de comienzo del curso.

En D2:
=C2+1    [Extender la fórmula hasta la celda DM2]

Esto nos garantiza que la primera fase del curso quede dentro de este rango de fechas.

En C3:
=C2*ELEGIR(DIASEM(C2;2);Datos!$F$8;Datos!$G$8;Datos!$H$8;Datos!$I$8;Datos!$J$8;Datos!$K$8;Datos!$L$8)   [Extender la fórmula hasta la celda DM3]

Puesto que en la próxima fila nos referiremos a los días festivos de la hoja "Datos", vamos a crear una "nombre" que haga referencia a esta lista. Será un "nombre" dinámico ya que la lista de días festivos cambia cada año y pretendemos hacer una hoja que sirva para otras ocasiones.

Accedemos a Fórmulas + Asignar nombre y creamos el "nombre" Festivos con la siguiente definición:

 Festivos  =DESREF(Datos!$C$15;1;0;CONTARA(Datos!$C$16:$C$200);1)

En C4:
=SI(C3=0;"";SI(ESERROR(COINCIDIR(C3;Festivos;0));C3;""))   [Extender la fórmula hasta la celda DM4]

En C5:
=SI(C4="";"";CONTAR($C$4:C4))   [Extender la fórmula hasta la celda DM5]

En este momento crearemos otros dos "nombres": DisponiblesP (que hará referencia a la fila 4) y OrdenP (que abarcará los datos de la fila 5).

 DisponiblesP  =DESREF(AuxP!$C$4;0;0;1;CONTARA(AuxP!$4:$4)-1)
 OrdenP  =DESREF(AuxP!$C$5;0;0;1;CONTARA(AuxP!$5:$5)-1)

En C6:
=INDICE(DisponiblesP;COINCIDIR(COLUMNA()-2;OrdenP))   [Extender la fórmula hasta la celda DM6]

Si nos fijamos bien, comprobaremos que en la fila 6 están contenidos los jueves y viernes (sólo los no festivos) de la primera fase del cursillo. En realidad, la lista se extiende unos cuantos días más, ya que hemos tenido la precaución de prever futuros cursos. Cuando llegamos al 23 de diciembre ya no hay más jueves o viernes no festivos en la fila 1, por lo que se repite esta fecha (el 29 de diciembre es el último día, pero es festivo).

Este es el momento de crear la tabla de asistencia de la primera fase del curso.

En la celda D4 de la hoja "Primera" ponemos formato personalizado ddd y escribimos:
=AuxP!C6   [Resultado: jue]

En la celda E4 de la hoja "Primera" volvemos a poner el formato personalizado ddd y escribimos:
=SI(O(AuxP!D6=AuxP!C6;AuxP!D6>Datos!$D$6);"";AuxP!D6)   [Resultado: vie]

Extendemos la fórmula de la celda E4 hasta DL4.

En la celda D2 ponemos formato personalizado dd y escribimos:
=D4   [Resultado: 08]

En la celda D3 ponemos formato personalizado mmm y escribimos:
=D4   [Resultado: sep]

Extendemos ambas fórmulas hasta la columna DL.

En D26:
=SI.ERROR(ELEGIR(DIASEM(D4;2);Datos!$F$7;Datos!$G$7;Datos!$H$7;Datos!$I$7;Datos!$J$7;Datos!$K$7;Datos!$L$7);0)    [Extender la fórmula hasta la celda DL26]

Seleccionamos D26:DL26 y ponemos el formato personalizado: #;;

Creamos una celda combinada con B26:C26, ponemos el formato personalizado _)_)"HORAS " *. #.##0_)_)_) y escribimos la fórmula:
=SUMA(D26:DL26)   [Resultado: 60]

Ahora, usaremos el formato condicional para poner los bordes.

Seleccionamos D5:DL24 y accedemos a Inicio + Formato condicional + Nueva regla. Elegimos Utilice una fórmula que determine las celdas para aplicar formato y ponemos la fórmula: =D$2<>"". Pulsamos Formato y, en la pestaña Bordes, elegimos Contorno.

El formato debe ser igual en D26:DL26. En las filas 2, 3 y 4 podemos variar ligeramente el formato para eliminar la línea de separación entre las filas 2 y 3. Si ponemos una línea de puntos entre la 3 y la 4 se leerá mejor la fecha.

El último paso en esta hoja será dejarla preparada para que se imprima correctamente. Establecemos el área de impresión accediendo a Diseño de página y haciendo clic en el vértice inferior derecho del grupo.

Se mostrará el cuadro de diálogo Configurar página. En la pestaña Página, seleccionamos orientación Horizontal. En Márgenes, pondremos 1 cm. en todos excepto en el superior, que pondremos 2 cm. En la pestaña Hoja pondremos B2:AA26 en el apartado Área de impresión; en el apartado Repetir columnas a la izquierda pondremos $B:$C. Con el último dato le indicamos a Excel que si la impresión ocupa más de una hoja, en todas ellas se muestren los nombres de los alumnos.

Pulsando Vista preliminar veremos cómo quedaría la hoja si la imprimiéramos, pero todavía no vamos a hacerlo. 

Si en F7:L7 de la hoja "Datos" cambiamos los días en los que se imparte el curso, tendremos que cambiar a mano el Área de impresión. Pero lo que pretendemos es que se ajuste automáticamente sin intervención del usuario. Veamos cómo podemos conseguirlo.

Vamos a Fórmulas + Administrador de nombres para obtener la lista de "nombres" que hemos creado. Veremos que Excel ha añadido dos nuevos nombres: Área_de_impresión y Títulos_a_imprimir. En la columna Ámbito, nos indica que estos nombres sólo están vigentes en la hoja "Primera".

Conseguiremos que el Área de impresión se ajuste automáticamente sustituyendo el valor del "nombre" Área_de_impresión por una fórmula dinámica. Para ello, seleccionamos Área_de_impresión, pulsamos el botón Editar y sustituimos su valor por el siguiente:

 Área_de_impresión  =DESREF(Primera!$B$2;0;0;25;115-CONTAR.SI(Primera!$26:$26;"=0"))

Ya podemos imprimir la hoja.

Las hojas "Segunda" y "Tercera" se construyen de la misma manera.

El último paso consistirá en poner las horas de las fases en el rango D10:D13 de la hoja "Datos":

En D10:
=Primera!B26

En D11:
=Segunda!B26

En D12:
=Tercera!B26

En D13:
=SUMA(D10:D12)