lunes, 7 de octubre de 2019

Interactuando con Microsoft Word (I): Enviar datos de Excel a Word (VBA)

En innumerables ocasiones he visto que mucha gente pregunta como enviar datos de Excel a Word, he de ser sincero y lo primero que se me viene a la cabeza es “que lo hagan desde Word usando Combinar Correspondencia”, pero en muchas de esas mismas ocasiones quieren y/o necesitan hacerlo desde Excel o en todo caso no solo enviar datos por partes sino más bien enviar incluso tablas o rangos completos. ¿Cuál es la solución? Definitivamente usar macros (VBA).

Para empezar, es importante aclarar que, si bien vamos a realizar la programación desde el entorno del VBA de Excel, como vamos a interactuar con Microsoft Word, lo que haremos es hacer referencias a los objetos de dicho programa, es decir, casi como usar el VBA desde el mismo Word. Dicho eso, lo primero que haremos es suponer que tenemos los siguientes datos:

Luego, como la idea es trabajar con Word, declararemos una variable respectiva y crearemos dicho objeto Word.

Dim MiappWord As Object 
Set MiappWord = CreateObject("Word.Application") 

Luego, como ya tenemos el Word listo, lo que haremos es crear un documento nuevo en blanco.

MiappWord.Documents.Add

Una vez hecho eso vamos a copiar el rango que necesitamos enviar a Word:

Hoja1.Range("A1:E97").Copy

Como ven ahí, en esta ocasión uso el nombre del módulo de la hoja para hacer referencia al rango de dicha hoja, no estoy usando ni Sheets ni WorkSheets ¿por qué? En realidad, es una buena forma de hacer referencia a las hojas sobre todo cuando más de una persona usa el archivo ya que si cambian el nombre de la hoja, y por ejemplo dice Sheets(“Hoja1”), dicha instrucción de VBA dejaría de servir, haciendo referencia al módulo de la hoja hay mayor garantía de que no se cambie (no es que nadie sapa como hacerlo, claro). Pero mejor volvamos a lo nuestro.

Como ya tenemos los datos en el portapapeles, ahora lo que haremos es pegarlos en Word, para ello utilizaremos el método PasteExcelTable que pega celdas de Excel en Word. Más referencias aquí: Enlace. Entonces, usaremos lo siguiente:

MiappWord.Selection.PasteExcelTable False, False, False

Ahora haremos visible el Word para ver el resultado.

MiappWord.Visible = True

Si leyeron el enlace recomendado, verán que lo que hemos hecho es pegar las celdas de Excel copiadas, manteniendo el formato de origen, por lo que debemos tener lo siguiente en nuestro archivo Word:

Vamos a probar diversas opciones del método sugerido, pero en cada ocasión vamos a insertar una página nueva en Word para que se note cada vez que pegamos los datos. Al método sugerido, vamos a agregarle un par más para que prueben. Comentaré en el código que es lo que hace cada uno.

With MiappWord
     'usar estilos de destino
     .Selection.PasteExcelTable False, True, False
     .Selection.InsertNewPage
     'vincular y mantener formato de origen
     .Selection.PasteExcelTable True, False, False
     .Selection.InsertNewPage
     'vincular y usar estilos de destino
     .Selection.PasteExcelTable True, True, False
     .Selection.InsertNewPage
     'imagen
     .Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
         Placement:=wdInLine, DisplayAsIcon:=False
     .Selection.InsertNewPage
     'conservar solo texto
     .Selection.PasteAndFormat (wdFormatPlainText)
 End With 

Para que esta última parte funcione sin mayor dilema, activen la siguiente referencia a Microsoft Word:

Si deseamos también podemos guardar el archivo.

MiappWord.ActiveDocument.SaveAs Filename:=ThisWorkbook.Path & "\miarchivo.docx"

Por último, podríamos tener una macro así ya con todo ordenado:

Sub EnviardatosaWord()

Dim MiappWord As Object 

Set MiappWord = CreateObject("Word.Application") 

MiappWord.Documents.Add 
Hoja1.Range("A1:E97").Copy 

With MiappWord
     'mantener el formato de origen
     .Selection.PasteExcelTable False, False, False
     .Selection.InsertNewPage
     'usar estilos de destino
     .Selection.PasteExcelTable False, True, False
     .Selection.InsertNewPage
     'vincular y mantener formato de origen
     .Selection.PasteExcelTable True, False, False
     .Selection.InsertNewPage
     'vincular y usar estilos de destino
     .Selection.PasteExcelTable True, True, False
     .Selection.InsertNewPage
     'imagen
     .Selection.PasteSpecial Link:=False, DataType:=wdPasteEnhancedMetafile, _
         Placement:=wdInLine, DisplayAsIcon:=False
     .Selection.InsertNewPage
     'conservar solo texto
     .Selection.PasteAndFormat (wdFormatPlainText)
     .ActiveDocument.SaveAs Filename:=ThisWorkbook.Path & "\miarchivo.docx"
     .Visible = True
 End With 

Application.CutCopyMode = False
Set MiappWord = Nothing 
MsgBox "Todo listo" 

End Sub 
Espero les sea útil. Hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el ejemplo de aquí

lunes, 30 de septiembre de 2019

Usando SOLVER para hallar los sumandos de un total

En ocasiones he visto en diversos foros y grupos (Facebook) de Excel que algunos/as usuarios/as tienen la necesidad de hallar los números cuya suma sirva para obtener un total buscado, es decir, necesitan hallar los sumandos de una suma o total.  Aunque hay varias formas de hacerlo (ninguna es simple) en esta ocasión vamos a usar el complemento SOLVER. Manos a la obra.

Empecemos colocando algunos valores en el rango A1:A10.

Por si no se dieron cuenta la suma de todos esos valores da 960 por ende, el total que buscaremos no debe superar ese número.

Ahora nos vamos a situar en A1 y le daremos un formato condicional con la opción de “Nueva regla de formato” y ahí vamos a elegir “Utilice una fórmula que determine las celdas para aplicar formato” y en “Dar formato a los valores donde esta fórmula sea verdadera” vamos a colocar lo siguiente:

=B1=1

Esto es lo que deberíamos tener hasta el momento:

Ahí mismo con el botón “Formato” podemos aplicar, por ejemplo, un fondo de color rojo.

Le damos clic al botón “Aceptar” y luego del mismo modo al cuadro de dialogo anterior. Para no estar dando Formato Condicional a celda por celda, solo copiaremos el formato de este modo:


Ahora, para nuestro ejemplo, vamos a introducir la siguiente fórmula en B11:


Ahora activaremos la herramienta SOLVER, y en el cuadro de dialogo correspondiente vamos a llenar así:

Yo he colocado 600, pero evidentemente ustedes elegirán el número total de su conveniencia. Ahora le daremos clic al botón “Agregar” y en el nuevo cuadro de diálogo dejaremos así las opciones:

Hemos elegido “bin” (de binario) para que los resultados sean 0 (cero) o 1 (uno). Démosle clic a “Aceptar” y obtendremos esto:


Ahora clic a “Resolver” y en el siguiente diálogo igual en “Aceptar”.

Si todo salió bien las celdas con los valores que suman 600, estarán resaltadas con fondo de color rojo.

Prueben con diversos totales y verán que consiguen resultados similares. Ah, eso sí, el total debe ser un número cuyos sumandos se encuentren sí o sí en las alternativas a marcar; asimismo, si hay más alternativas cuyos sumandos den el mismo resultado, la herramienta usada en esta ocasión solo nos brindará un grupo. Igual sé que les será útil.

Abraham Valencia
Lima, Perú

sábado, 14 de septiembre de 2019

Curiosidades en Excel: Valores ocultos en celdas combinadas

Pasan los años y diferentes versiones y nunca, pero nunca, Excel deja de sorprendernos. Hace pocos días en uno de los foros en los que siempre participo, un usuario mencionaba que, en unas celdas combinadas, al ver la celda y la barra de fórmulas, en ambos casos se mostraba un solo valor, pero que al usar macros y/o devolver con fórmulas el valor de ambas celdas por separado (como si no estuviesen combinadas), pues devolvía dos valores distintos ¿Qué cómo es eso? Para que se entienda mejor, antes de seguir con este artículo, descarguen y miren el siguiente archivo: Enlace.

Como ven, las celdas A1 y A2 están combinadas y en dicha combinación se observa la frase “Hola, yo soy visible”. A pesar de ello, en las celdas B1 y B2 verán dos frases distintas, en la primera la misma de las celdas combinadas y en la segunda “Hola, yo estoy oculto”, a pesar de que cada una de las segundas está vinculada a una de las primeras.

¿Vieron que en B2 hay un vínculo a A2 y que a pesar de que A2 está combinada con A1 tiene un valor propio?  Si aún tienen dudas, miren bien la celda combinada, no hay nada más que la primera frase.

Ah, si aún tienen dudas no olviden que al combinar celdas solo se mantiene el valor de la primera (superior y/o superior izquierda del grupo de celdas que se combina). Se supone que descargaron el archivo y están viéndolo, pero si aun así persisten las dudas, como ven no tiene macros y no hay más celdas con valores que las vistas hasta el momento… busquen si desean.

¿Ahora sí convencidos/as? Pues veamos cómo lo logré hacer. Primero en A1 (o en donde deseen) escriban algo, lo que sea.

Ahora inserten un TextBox (ActiveX) en la hoja (en cualquier lugar).

Luego en la propiedad “LinkedCell” del TextBox escriban “A2” (obviamente sin las comillas).


Ahora combinen A1 y A2. Como ven, la frase ingresada es el único valor en la celda(s).

Ahora, escriban lo que quieran en el TextBox (evidentemente previa salida del “Modo diseño”). Verán que el texto ingresado no se refleja en la celda combinada.

Ahora eliminen el Textbox, pero ¡sin borrar el texto que contiene antes! Solo elimínenlo tal cual lo habían dejado (no olviden facilitarse la vida eliminando el TextBox en “Modo diseño”). Ahora en la celda que deseen coloquen un vínculo a A1 (=A1).

El resultado será este

Ahora agreguen un vínculo a la celda A2 y miren el resultado:

Como ven, a pesar de haber eliminado el TextBox y de estar combinadas las celdas, en A2 se mantiene el valor ingresado en el TextBox dado que estaban vinculados, es más, al descombinar las celdas veremos esto:

Y listo, misterio resuelto, aunque más que misterio, una curiosidad más del gran Excel. Hasta la próxima.

Abraham Valencia
Lima, Perú

viernes, 6 de septiembre de 2019

La hora mundial en Excel a través de Power Query

Hay muchas cosas curiosas y/o interesantes que se pueden hacer con Excel y definitivamente la inclusión de Power Query ha incrementado dichas posibilidades. En esta ocasión vamos a aprender a tener en una hoja de Excel las diferentes horas que hay en los países del mundo.

De las páginas web que miré, la siguiente es la que me pareció mejor para este tema: Enlace.



Si miran bien, hay un detalle que nos interesa y es la propiedad de dicha web que nos da la hora por países. La activaremos para copiar el enlace que nos será más útil en este caso.

https://www.horlogeparlante.com/reloj-mundial.html?sort=country

Ahora en nuestra hoja de Excel vamos a ir a la pestaña “Datos” y en el grupo “Obtener y transformar datos” vamos a darle clic al botón “Desde la web”.

En el cuadro de diálogo que sale ingresaremos el enlace comentado líneas arriba y le daremos clic al botón “aceptar”.

En el siguiente cuadro, no nos hagamos un mundo y solo démosle clic a “Conectar”.

En el cuadro “Navegador” vamos a elegir la segunda opción “Hora mundial: La hora actual en todos…” y le daremos clic al botón “Transformar datos”.

Se abrirá el editor de Power Query y ahí como la primera columna no nos interesa, le daremos clic derecho a su encabezado y elegiremos “Quitar”.

Ahora, podemos darle doble clic a cada encabezado y colocar los nombres que deseemos.

.

Una vez hecho eso, vamos a la pestaña “Inicio” y le damos clic al botón “Cerrar y cargar”.

f

Si hicimos todo bien, en nuestra hoja tendremos algo así:

Para terminar, vamos a la pestaña “Datos” de nuestro Excel y en el grupo “Consultas y conexiones” dale clic al botón “Actualizar todo” y en la lista que se despliega dale clic a “Propiedades de conexión…” y en el diálogo que se mostrará vamos a ir a la pestaña “Uso” y ahí activen la opción “Actualizar cada” y coloquen 1 minuto (o lo que deseen).  También activen la opción “Actualizar al abrir el archivo”. Clic al botón “Aceptar” y listo, ya esta listo nuestro archivo. Cada minuto, o lo que hayamos elegido, y al abrir el archivo, tendremos la hora de todos los países de nuestras listas.

Ah, por supuesto que se puede ordenar alfabéticamente, pero eso ya es tarea para ustedes. Hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el archivo aquí

sábado, 31 de agosto de 2019

Ver nuestro archivo Excel a través de una web

En muchas ocasiones he recibido consultas sobre cómo lograr que nuestro archivo Excel se vea a través de una web y que, al actualizar los datos del archivo, automáticamente se visualicen en dicha web (Ojo, no descargar un archivo, sino, verlo en la misma web). La primera respuesta que casi siempre se lee o escucha es “no se puede”, pues incluso convirtiendo un archivo Excel en formato HTML al ser incluido en una página web, necesariamente hay que reemplazar el archivo por uno nuevo para que se vean los datos actualizados. Entonces ¿es correcto responde que no se puede? Veámoslo hoy.

En esta ocasión vamos a suponer que queremos compartir a través de una pagina web los resultados de nuestras ventas, incluyendo algún gráfico, y tal como hemos mencionado queremos que cualquier persona lo vea y tan solo utilizando Excel. Nuestra hoja tiene de este modo los datos y un gráfico:



Ahora guardaremos nuestro archivo en OneDrive, en mi caso, y para facilitar las cosas, tengo la app respectiva en el Windows 10 de mi portátil.

Una vez guardado, ya sea por web o por la app, podremos ver dicho archivo en nuestro OneDrive

.

En la versión web de OneDrive, dale clic derecho al archivo y elige “Insertar”.

En la ventana que se abrirá dale clic al botón “Generar”

.

En la siguiente ventana dale clic a “Personalizar la forma en que este libro insertado se mostrará a otros usuarios”.

En la página que se abrirá, personalicen la hoja de Excel eligiendo las opciones que más les convengan para que las personas visualicen.

Una vez hecho eso, no olvides copiar el código del final de la página.

No olvides guardar el código copiado en algún lado, como por ejemplo en el Bloc de Notas.

En nuestra web, crearemos una página, en mi caso uso Blogger y se vería algo así, tanto en diseño como en código HTML.

Ahora agregaremos el código copiado a nuestra página web. Debería quedar algo así:

Luego, en nuestra web debería verse como esto, por supuesto dependiendo del tamaño que hayamos elegido, así como las opciones activadas.

Ahora, cuando cambien los datos en su archivo de Excel, sea en la app o a través de Excel Online, dichas modificaciones ser podrán visualizar directamente en el enlace respectivo. En mi caso, este: Enlace

Y eso es todo por hoy, espero les sea útil.

Abraham Valencia

Nota: El formato de fecha, separador de miles o separador de decimales que se ve en la web generada, depende del de tu Excel Online.

lunes, 19 de agosto de 2019

Cambio de divisas con macros (VBA)

No es inhabitual que muchas personas por razones de trabajo, estudio o personales, necesiten saber el cambio de su moneda nacional por alguna extranjera o quizá entre aquellas. Hoy en día, y con la masificación del uso de internet, es muy fácil encontrar miles de webs que realizan el cambio de modo rápido e incluso de forma gratuita, pero veamos cómo podemos tener esto en nuestro archivo de Excel.

Lo primero es mencionar que usaremos el objeto Internet Explorer que, si bien en ocasiones puede ser un poco lento, es más fácil de entender y usar (Enlace). Segundo, para el ejercicio vamos a usar la siguiente web: Enlace. Y tercero e importante, para tener los tipos de moneda vamos a usar el estándar internacional ISO 4217 que fue creado por la ISO con el objetivo de definir códigos de tres letras para todas las divisas del mundo (Enlace).

Ahora veamos como funciona la web de cambio. Si entramos a ella, ingresamos un monto y elegimos los tipos de moneda:

Al darle clic al botón correspondiente nos hará el cambio de divisas solicitado.

Lo que más nos interesa es el enlace generado. Veámoslo con detalle:

https://www.xe.com/es/currencyconverter/convert/?Amount=100&From=PEN&To=USD

Como quizás ya se dieron cuenta, aparece el monto (100), así como el código de las dos monedas seleccionadas (PEN y USD).  Si prueban más montos y monedas verán el mismo efecto. Ojo con algo importante, dicha web solo acepta la coma (,) como separador de decimales, nunca el punto (.). Entonces, si logramos reemplazar el monto y los códigos con los que necesitemos, y lo enviamos a Internet Explorer, tendremos los resultados que necesitamos.

Para los códigos de las monedas podemos conseguirlos de enlace respectivo que he compartido líneas arriba de tal modo que en una hoja (que yo he llamado “Códigos”) tendremos algo así:

Creen un Userform y agréguenle un TextBox y dos Combobox (y algunos Labels como título y sub títulos, si desean claro).

Como vieron yo en mi hoja “Códigos” tengo tres columnas: Código (que es el que más nos interesa), divisa (moneda) y el país; por ende, en la propiedad ColumnCount he colocado 3. Para llenarlos he usado el evento Initialize del Userform.

Private Sub UserForm_Initialize() 
Me.CmbCodigos.RowSource = "=Códigos!A2:C180" 
Me.CmbCodigos2.RowSource = "=Códigos!A2:C180" 
End Sub 

Con el TextBox vamos a hacer que solamente acepte números y la coma decimal (no olviden que la web no acepta punto como separador de decimales) haciendo uso de su evento KeyPress. Ah, por cierto, solo dejaremos que se ingrese una coma como máximo.

Private Sub TxtMoneda_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii < 48 Or KeyAscii > 57) And KeyAscii <> 44 Then
     KeyAscii = 0 
End If 
End Sub  

También agregaremos algunos Label más para los resultados obtenidos, así como un botón.

Ahora en el evento Click del botón vamos a agregar tres variables que reemplazarán al monto y las dos divisas a reemplazar para usar el mismo enlace que obtuvimos líneas arriba. Además, vamos a obtener los valores del cambio y pasarlos a nuestros Label. Como ya en otro artículo he explicado como usar los valores de los elementos/objetos de la web, aquí simplemente los colocaré directamente. Entonces, el código quedará así:

Private Sub CmdCambio_Click() 

Dim IE As Object 
Dim MiMonto$, maCambiar$, mCambio$, MiURL$ 

If TxtMoneda = "" Or CmbCodigos = "" Or CmbCodigos2 = "" Then
     MsgBox "No dejes campos en blanco", vbOKOnly, "Todo Sobre Excel"
     TxtMoneda.SetFocus
     Exit Sub 
End If 

Application.Cursor = xlWait 

Set IE = CreateObject("InternetExplorer.Application") 
Let MiMonto = TxtMoneda: Let maCambiar = CmbCodigos: Let mCambio = CmbCodigos2 
Let MiURL = "https://www.xe.com/es/currencyconverter/convert/?Amount=" & MiMonto & "&From=" & CmbCodigos & "&To=" & CmbCodigos2 

IE.Navigate MiURL 

Do Until IE.ReadyState = 4
     DoEvents 
Loop 

Label6 = IE.document.getElementsByClassName("converterresult-conversionTo")(0).innerText 
Label7 = IE.document.getElementsByClassName("sc-EHOje lkcPkj")(0).innerText & _
     vbNewLine & IE.document.getElementsByClassName("sc-EHOje lkcPkj")(1).innerText 

IE.Quit 

Application.Cursor = xlDefault 

Set IE = Nothing 

End Sub   

Si todo quedó bien obtendremos un resultado de este estilo:

Y eso es todo por hoy, hasta la próxima.

.Abraham Valencia

Descargue el ejemplo aquí