Próximo curso presencial Excel avanzado en Madrid

¿Vives en Madrid?, ¿Te interesa la mejor formación en Excel presencial?.
Curso Presencial Excel Avanzado en Madrid con una duración de 20 horas...
Fechas:
Viernes 22-Mayo-2015
Viernes 29-Mayo-2015
Viernes 5-Junio-2015
Viernes 12-Junio-2015
Viernes 19-Junio-2015

Horario: 16:00 - 20:00 (total 20 horas).

Lugar: C/ Marqués de Ahumada 7, Madrid

Precio: 259,00 euros

Profesor: Ismael Romero (yo mismo). Microsoft MVP Excel 2014 y 2015. Experto en Excel.

¡¡¡Plazas limitadas a 10 alumnos!!!

Infórmate en
cursos@excelforo.com o aquí
Excel Avanzado Presencial Madrid.
MATRÍCULA ABIERTA HASTA EL 15-MAYO-2015

... y por supuesto la mejor formación elearning(online).
Haz de Excel tu mejor aliado!
Aprende con los mejores y adquiere una buena base: Edición de Cursos de Excel y Macros online con tutor personal de Mayo de 2015.
Nunca estudiar fue tan fácil.

Los cursos de Excel y Macros abiertos para este mes de Mayo son:

Curso Excel Avanzado

(ver más)

Curso Macros Medio

(ver más)

Curso Macros Iniciación

(ver más)

Curso Excel Nivel Medio

(ver más)

Curso Tablas dinámicas en Excel

(ver más)

Curso preparación MOS Excel 2010 (Examen 77-882)

(ver más)

Curso Excel Financiero

(ver más)


Esta nueva edición de Cursos de Excel y macros en modalidad elearning (online) comienzan el día 1 de Mayo de 2015; y la matrícula estará abierta hasta el día 10.

Excelforo: con la confianza de siempre....estás a tiempo!!

También formación Excel a empresas. Explota los recursos a tu alcance (ver más).

Informarte sin compromiso en cursos@excelforo.com o directamente en www.excelforo.com.

Seguir leyendo...

VBA: Traduciendo a otros idiomas celdas en Excel.

Un lector solicitaba ayuda para traducir los contenidos de nuestras celdas.. preguntando si existía alguna función en Excel que realzara tal acción.
Aunque no existe una función estándar que haga lo que necesitamos, buscando por la web encontré un código de una UDF (función personalizada) que adapté a la necesidad.

El código siguiente requiere la instalación de la Referencia: Microsoft Internet control, ya que vamos a emplear el traductor de Google, accediendo al formulario de esa web.

Al realizar una conexión a Internet, el proceso de traducción para cada celda se demora algo.. pero al menos tenemos una traducción válida (más o menos.. ya sabemos todos cómo funciona el traductor de Google...)

La función solo requiere dos argumentos. El primero la celda a traducir, y el segundo el código del idioma al que queremos traducirlo (por defecto está al español).
Más abajo en este post aparece una lista de los códigos identificados por Google.

Insertamos el código de la función en un módulo estándar de nuestro proyecto de VBA desde el editor de VB:

Function Traducir(texto_a_traducir As String, Optional CodSalida As String = "es") As String
'Emplearemos el traductor de Google a través de Internet Explorer...
'OJO!!! MUY IMPORTANTE: Debe estar seleccionada la Referencia > Microsoft Internet Control
Dim IE As Object, i As Long
Dim IdiomaOriginal As String, IdiomaSalida As String
Dim texto_traducido As String, Txt_Limpio

'Abrimos Internet Explorer
Set IE = CreateObject("InternetExplorer.application")

'Elegimos el idioma original del texto a traducir
'lo dejamos en Auto para que el sistema detecte cuál es...
IdiomaOriginal = "auto"

'Elegimos el idioma al que queremos traducirlo
'en este caso al español (es)   Ver listado códigos de idioma en el post.
IdiomaSalida = CodSalida

'hacemos invisible el navegador
IE.Visible = False
'Abrimos la web del traductor de Google
IE.navigate "http://translate.google.com/#" & IdiomaOriginal & "/" & IdiomaSalida & "/" & texto_a_traducir

'trabajamos sobre el traductor...
'ver también esta entrada del blog
'http://excelforo.blogspot.com.es/2014/10/vba-rellenando-un-formulario-web-html.html
Do Until IE.ReadyState = 4
    DoEvents
Loop
Application.Wait (Now + TimeValue("0:00:5"))
Do Until IE.ReadyState = 4
    DoEvents
Loop

'Obtenemos el texto traducido del campo de Google 'Result_box'
'sería algo así: <span class="hps">texto traducido</span>
'y lo dejamos limpio del código o etiquetas de html
'además con Split conseguimos separarlo en una matriz de valores.
Txt_Limpio = Split(Application.WorksheetFunction.Substitute(IE.Document.getElementById("result_box").innerHTML, "</SPAN>", ""), "<")
'en este paso recorremos los elementos de la matriz de elementos resultantes de la traducción
'y lo recomponemos en una frase (si fuera el caso)
For j = LBound(Txt_Limpio) To UBound(Txt_Limpio)
    texto_traducido = texto_traducido & Right(Txt_Limpio(j), Len(Txt_Limpio(j)) - InStr(Txt_Limpio(j), ">"))
Next

'cerramos Internet Explorer
IE.Quit
'devolvemos el texto traducido a la hoja de cálculo
Traducir = texto_traducido

End Function

Para localizar el código HTML del elemento por Id podremos seleccionar el código traducido y presionar Inspeccionar elemento desde el navegador. En este caso sería:

haz clic en la imagen

Un ejemplo del uso de la función 'Traducir':

Finalmente dejo aquí el listado de los códigos de idioma de Google:

Seguir leyendo...

Una validación de datos por aproximación...

Un tema recurrente en el mundillo es cómo conseguir una lista de validación acorde al texto introducido.
Si bien la solución que propongo aquí no es inmediata, es lo más parecido que he encontrado.

Se trata, entonces, de escribir una parte del texto (INICIALES!!!), para luego presionar la flecha del desplegable de la celda validada y que nos muestre aquellas palabras coincidentes con esas iniciales.
MUY IMPORTANTE!.. el rango con las celdas de las palabras deben estar ordenadas!!!

Partimos de un rango A2:A22 con unas palabras ordenadas en sentido ascendente y en D2 una celda validada con una fórmula que mostraré a continuación.
La idea es escribir en D2 parte (inicial o por la izquierda) de las palabras a mostrar... en la imagen siguiente se ve como al escribir 're' y presionar la flecha de validación aparecen las dos únicas palabras que comienzan por 're':

El misterio de este 'éxito' reside en una función DESREF anidada de una COINCIDIR (empleadas en más de una ocasión).
La función en cuestión:
=DESREF(ValidacionAprox!$A$2;COINCIDIR(ValidacionAprox!$D2&"*";ValidacionAprox!$A$2:$A$22;0)-1;;CONTAR.SI(ValidacionAprox!$A$2:$A$22;ValidacionAprox!$D2&"*"))

Por comodidad podemos genera un Nombre definido tipo fórmula:
Lista2: =DESREF(ValidacionAprox!$A$2;COINCIDIR(ValidacionAprox!$D2&"*";ValidacionAprox!$A$2:$A$22;0)-1;;CONTAR.SI(ValidacionAprox!$A$2:$A$22;ValidacionAprox!$D2&"*"))

para luego emplearlo en la validación:

La cuestión siguiente nos llevaría a preguntarnos.. ¿es posible conseguir algo similar sobre la premisa de buscar aquellas palabras que contengan el texto escrito en D2 (en lugar de sólo las que comiencen)??.
Sí, es posible.. con rangos auxiliares mediante fórmulas matriciales (en un post posterior escribiré al respecto); pero también con un poco de programación.

Añadimos los eventos siguientes en la ventana de código de la hoja de trabajo:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim celda As Object
Dim i As Integer
Dim lista As String, elementos As String

'generamos la coleccion
Set unicos = New Collection
'loop en todas las celdas y agregarlas a la coleccion
For Each celda In Range("A2:A22")
    If InStr(1, celda.Value, Target.Value, vbTextCompare) Then
        'cuando encuentre un item repetido, daría un error
        'que salvamos con la instrucción On Error Resume Next
        On Error Resume Next
        'por tanto, nuestra colección solo agrega elementos no repetidos
        'objeto.Add item, key, before, after
        'ocurre un error si una key especificada duplica la key de un miembro existente de la colección
        unicos.Add celda.Value, CStr(celda.Value)
        On Error GoTo 0
    End If
Next celda

'unir los datos en un literal...
For i = 1 To unicos.Count
    lista = lista & "," & unicos(i)
Next i
 
'quitar la primera coma
elementos = Mid(lista, 2)

If Not Intersect(Target, Range("D2")) Is Nothing Then
    With Range("D2").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Operator:=xlBetween, Formula1:=elementos
    End With
End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'comenzamos borrando la validación existente en D2
If Not Intersect(Target, Range("D2")) Is Nothing Then
    respuesta = MsgBox("Usar Lista existente??", vbYesNo)
    If respuesta = vbNo Then
        With Range("D2").Validation
            .Delete
        End With
    End If
End If
End Sub

Este código tiene una desventaja importante.. y es que se requiere una confirmación previa al seleccionar la celda D2, y que nos permite mantener o borrar la validación de celda existente o creada en un paso anterior con el evento _Change.

Obviamente no es lo más deseable.. pero al menos desplegamos aquellos elemento de nuestro rango que nos interesan...

Seguir leyendo...

VBA: La propiedad FirstPageNumber - Numerando páginas de impresión...

En el post del día veremos cómo controlar la numeración de nuestras páginas impresas.
Se plantea el siguiente objetivo: Imprimir diferentes hojas, siendo la primera una portada en la que no queremos ver numeración alguna, y deseamos que la numeración comience en 1 a partir de la segunda de las páginas.. y continúe...

Veamos un planteamiento de hoja a imprimir:

El asunto es claro: la primera página irá sin numeración, la segunda irá numerada como página 1, la tercera como página 2, etc...

Insertamos en un módulo estándar del proyecto de VBA y ejecutamos el siguiente procedimiento:

Sub ImpresionPaginas()
'configuramos el diseño de página
With ActiveSheet.PageSetup
    'Indicamos que la primera página es diferente
    .DifferentFirstPageHeaderFooter = True
    'indicamos con qué numeración deseamos comenzar la numeración global...
    .FirstPageNumber = 0
    'Numeramos la sección derecha del Pié de página
    .RightFooter = "&P"
End With

'imprimimos el área de impresión, mostrando la Vista preliminar
ActiveSheet.PrintOut preview:=True
End Sub

La clave de esta simple macro es la propiedad .FirstPageNumber.

Otra forma podría ser:

Sub ImpresionPaginas2()

With ActiveSheet.PageSetup
    'indicamos que la numeración comience en automático
    .FirstPageNumber = xlAutomatic
    'Indicamos que la primera página es diferente
    .DifferentFirstPageHeaderFooter = True
    'asignamos valor a la sección derecha del pié de página
    'OJO con la forma de escribir el valor...
    .RightFooter = "&P-1 "
End With

'imprimimos el área de impresión, mostrando la Vista preliminar
ActiveSheet.PrintOut preview:=True
End Sub

En este segundo caso empleamos la poco conocida forma de restar o sumar una cantidad directamente.

De igual forma podríamos configurar nuestras páginas de impresión desde la ventana diálogo correspondiente, sin emplear la programación

Seguir leyendo...

VBA: Pasar imagen de Excel a Word.

Hoy veremos un sencillo código para traspasar, mediante macros, una imagen desde nuestra hoja de cálculo a un documento de Word (insertado en una Tabla).
De especial en este ejemplo, es que generamos una Tabla en nuestro documento de Word, en la cual insertamos la imagen.
Además verificaremos si existe el fichero .docx o no, tomando el nombre del fichero de la celda G1.

Tenemos la siguiente imagen superpuesta en un rango de celdas... la idea es seleccionar el rango y copiarlo como imagen, para luego copiar y pegarlo en un documento de Word.

Insertamos el código asociado al control CommandButton y ejecutamos el siguiente procedimiento:

Private Sub CommandButton1_Click()
'con la referencia Microsoft Word 15.0 Object Library
Dim tabla As Object
'copiamos el rango de la hoja de cálculo
Sheets("ExcelToWord").Select
Range("B1:E11").CopyPicture xlScreen, xlPicture

'definimos la ruta destino del documento de Word
'tomando como nombre de docx el valor de la celda G1
Dim archivo As String
archivo = "E:\excelforo\" & Range("G1") & ".docx"
'Creamos el documento
Set objWord = CreateObject("Word.Application")
'y lo hacemos visible
objWord.Visible = True

'verificamos si existe o no un .docx con ese nombre (celda G1)
With objWord
    If Dir(archivo) = "" Then
        'si no existe lo añadimos nuevo
        Set objDoc = objWord.Documents.Add
        objDoc.SaveAs (archivo)
     Else
        'si existe lo abrimos
        Set objDoc = .Documents.Open(archivo)
    End If
End With
'generamos una Tabla en el documento de Word
Set tabla = objDoc.Tables.Add(objDoc.Range, 1, 1)
'y pegamos en la celda 'A1' de la tabla de Word la imagen
tabla.cell(1, 1).Range.Paste
'acabamos guardando el Word
objDoc.Save
     
Set tabla = Nothing
End Sub

Tras ejecutar el proceso veremos nuestro documento Word:

NOTA: es importante activar/habilitar la referencia 'Microsoft Word 15.0 Object Library'

Seguir leyendo...

VBA: Modificar dimensiones de todos los comentarios.

UN lector, a través de un comentario planteaba la manera de solventar un problema con el que, en ocasiones, nos encontramos: Modificar las dimensiones (alto y ancho) de los bocadillos de los comentarios:

...por alguna razón todos los comentarios perdieron el ancho predeterminado y se ven como una linea, he tenido que seleccionar uno por uno y ajustarle el ancho nuevamente. hay manera de seleccionar todos los comentarios del libro y cambiarle las propiedades al mismo tiempo?...

Es un problema tedioso que suele ocurrir (no siempre) cuando trabajamos con paneles inmovilizados...

la solución pasaría por lanzar un procedimiento que recorra todos los comentarios de nuestra hoja y vaya asignándole a cada uno unas dimensiones definidas por nosotros..
Supongamos nos encontramos con el siguiente panorama.. y hemos descartado ir comentario por comentario de manera manual:

Añadimos el procedimiento Sub ModificarDimensionesComentarios en un módulo de nuestro proyecto de VBA:

Sub ModificarDimensionesComentarios()
'Definir la hoja donde se colocarán los comentarios
Dim hojaComentarios As Worksheet, rng As Range
Set hojaComentarios = ActiveWorkbook.ActiveSheet
Set rng = Selection.SpecialCells(xlCellTypeComments)
 
'Extraer los comentarios si se ha seleccionado un rango
If TypeName(rng) = "Range" Then
'Establecer el rango a inspeccionar delimitando celdas con comentarios
Set rango = Intersect(rng, ActiveSheet.UsedRange)
'Recorrer todas las celdas del rango
For Each celda In rango.Cells
    'Obtener el comentario de la celda
    On Error Resume Next
    comentario = celda.Comment.Text
    
    'Si la celda tiene un comentario
    'modificamos su Ancho y Alto
    If Len(comentario) > 0 Then
        celda.Comment.Shape.Width = 80
        celda.Comment.Shape.Height = 40
    End If
    
    comentario = ""
    On Error GoTo 0
Next celda
End If
 
End Sub

Tras ejecutar el proceso este sería el resultado:

Otra forma igual de válida...

Sub DimensionarComentarios()
Dim xWs As Worksheet
Dim xComment As Comment
'recorremos las hojas del libro
For Each xWs In Application.ActiveWorkbook.Worksheets
    'y dentro de cada hoja todos los comentarios
    For Each xComment In xWs.Comments
        ' a cada comentario le cambiamos Ancho y Alto
        With xComment.Shape
            .Width = 80
            .Height = 40
        End With
    Next
Next
End Sub

Seguir leyendo...

Consolidando datos de orígenes diferentes con Power Query en Excel.

¿Cuántas veces hemos tenido que trabajar sobre diferentes orígenes de datos, en diferentes hojas o libros?, y ¿en cuántas ocasiones hemos tenido que recurrir a nuestras macros (VBA para Excel) u otros trucos para poder disponer de nuestra información en un único lugar?...
En el día de hoy aprenderemos una manera sencilla, empleando el complemento Power Query para Excel.

Antes de empezar indicarte que si no lo tienes instalado (para versiones Excel 2010 y superiores) puedes hacerlo gratuítamente desde la página de descargas de Microsoft aquí.
Ojo con la versión y arquitectura (32/64 bits) que tengas...!!!

Una vez instalado aparecerá en tu cinta de opciones la siguiente Ficha:

Haz clic en la imagen

Podrías preguntarte qué esto de Power Query??.. bueno, hay muchas descripciones pero una buena sería que Power Query para Excel es un complemento de Excel que nos permite conectarnos a cualquier origen de datos; transformar, combinar y acomodar los datos según la necesidad, y cargarlos a una hoja Excel o incluos a un modelo de datos (Power Pivot).
Además, todo el proceso de conexión, transformación, combinado y acomodo de los datos queda grabado para poder ser reutilizado posteriormente (cuando, por ejemplo, los datos se refresquen o haya que incluir los del siguiente periodo)... cosa de la que hoy nos aprovecharemos...
En definitiva es un constructor de consultas muy potente (y fácil de emplear).

Para el caso que nos ocupa hoy partiremos de tres tablas de datos dispuestas en hojas diferentes, con estructuras de campos similares, pero no dispuestas en iguales posiciones:

Haz clic en la imagen

Observemos que el orden de los campos de las tres tablas es diferente, incluso una de ellas posee un campo extra que no existe en las demás...

Estamos en disposición de generar y unificar en una sola tabla toda la información.
Para ella accederemos a la ficha de Power Query > grupo Desde Excely con una de las tablas seleccionada, por ejemplo la de la Empresa A (tabla con nombre Tbl_A), presionaremos el botón Desde tabla:

Haz clic en la imagen

Tras aceptar se acaba de generar una hoja nueva con una tabla resultante (de momento igual a la 'Tbl_A'); podríamos repetir esta acción para el resto de tablas... pero he optado por un método personalizado avanzado que ahorrará tiempo y espacio...
A continuación presionaremos, dentro de la ficha de Power Query el botón Mostrar Panel, lo que sacará un panel de control para las consultas generadas...

A continuación haremos doble clic sobre la consulta generada dentro del Panel de Consultas del Libro (o bien botón derecho del ratón y Editar), se abrirá de nuevo nuestra ventana de Editor de Consultas... (la misma que habíamos cerrado anteriormente). Igualmente podríamos haberlo configurado en el mismo momento de la creación...
En esta ventana buscaremos en el menú Inicio > grupo Consultas > botón Editor Avanzado:

Seguir leyendo...

Convertir Números como texto a números (puntos por comas).

En alguna ocasión, al trabajar con datos importados de orígenes dispares nos hemos encontrado con valores numéricos (supuestamente) con los separadores decimales o de miles intercambiados.
En la entrada de hoy veremos un procedimiento que ajusta estos valores a nuestra configuración, intercambiando sin error los separadores.

La clave de este ejercicio reside en la función personalizada 'NumTexto_a_Num' con la que 'partimos' el supuesto dígito en la parte Entera y la parte Decimal, obtenida al separar el valor por el separador decimal.

Veamos el fichero de partida, donde se mezclan importes con los separadores cambiados e importes correctamente importados, con otros datos de texto o incluso fechas...

Insertamos el siguiente procedimiento 'CambioPuntoxComa' y la función 'NumTexto_a_Num' en un módulo estándar:

Sub CambioPuntoxComa()
'controlamos el rango sobre el que trabajar
Set rngceldas = Range("A2").CurrentRegion
'recorremos todas las celdas del rango
For Each celda In rngceldas
    'cambiamos el valor de la celda por el que devuelva la función personalizada NumTexto_a_Num
    'dejamos fuera las celdas con valores de texto, lógicas, fechas...
    If IsNumeric(celda.Value) = True Then
        'para descartar lo que ya son números!!
        If Application.WorksheetFunction.IsText(celda.Value) Then
            celda.Value = NumTexto_a_Num(celda.Value, ".")
            celda.NumberFormat = "#,##0.00"
        End If
    End If
Next celda
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function NumTexto_a_Num(NumText As String, SepDec As String) As Double

Dim Entero As String, Decimales As String, SepMiles As String
'Array temporal para tratar la parte entera y Decimal
Dim Temp() As String

'gestionamos qué aparece como Separador de miles en los valores a tratar...
'si el separador decimal es el punto, entonces el de miles será la coma
'en otro caso el de miles sería el punto
If SepDec = "." Then
    SepMiles = ","
Else
    SepMiles = "."
End If

'completamos la matriz/array temporal con las partes del valor...
Temp = Split(NumText, SepDec, -1)
'desterminamos la parte entera del número
'reemplazando el Separador de miler por nada.. para dejarlo 'limpio'
Entero = WorksheetFunction.Substitute(Temp(0), SepMiles, "")

'finalmente devolvemos el valor a la función
Select Case WorksheetFunction.CountA(Temp)
    'si la matriz/array tiene un elemento, entonces
    'es un número entero, sin decimales
    Case Is = 1
        NumTexto_a_Num = Val(numInt)
    Case Else
    'en otros casos, es un número con parte decimal
    'y componemos el valor....
        Decimales = Temp(1)
        NumTexto_a_Num = Val(Entero & SepDec & Decimales)
End Select
  
End Function

Tras ejecutar el procedimiento obtendremos lo deseado... los valores que ya eran numéricos lo seguirán siendo (permanecerán intactos), mientras que los números 'cambiados' se habrán convertido a números...

Seguir leyendo...