Considero que la función “BUSCARV” es una de las más usadas por los usuarios/as de Excel. Esta función es útil cuando se necesita buscar elementos de una tabla o de una fila y aunque hay mucha información al respecto en internet, creo importante escribir un poco sobre ella. Dicha función tiene cuatro argumentos:
BUSCARV (Valor buscado, Matriz, Número de columna, Ordenado)
Argumento
|
Descripción
|
| Valor buscado | El valor que se desea buscar. |
| Matriz |
Rango en donde se encuentra el valor buscado. Dicho valor debe estar siempre en la primera columna del rango (la de más a la izquierda) para que BUSCARV funcione correctamente.
|
| Número de columna |
Columna del rango/matriz que contiene el valor que deseamos sea devuelto. La columna 1 es aquella en donde está el “Valor buscado”, la siguiente de la derecha es la 2 y así sucesivamente.
|
| Ordenado |
Es opcional, puede especificarse como “VERDADERO” (1) si se desea una coincidencia aproximada o “FALSO” (0) si se desea una coincidencia exacta del “Valor buscado”. Si no especifica el valor predeterminado siempre será VERDADERO (1).
|
Vamos con un ejemplo:
La matriz de datos está en el rango “B2:E7” y a través del uso de “BUSCARV” en “H2”, “I2” y “J2”, e ingresando un código de la primera columna de la matriz en la celda “G2”, se obtienen los valores deseados en las celdas con la fórmula respectiva:
Como se puede apreciar en el argumento “Ordenado” he usado cero (0) que es útil solo para coincidencias exactas, pero ¿qué ocurre si el código ingresado en “G2” no es exactamente igual a los de la matriz. Veamos:
Vamos a suponer que tenemos que calificar alumnos/as y las notas son en base a rangos del 0 al 20.
Entonces, si “BUSCARV” no puede encontrar el “Valor buscado” y el cuarto argumento (Ordenado) es “VERDADERO” (1), la función de la fórmula buscará una coincidencia aproximada, es decir utilizará el valor más alto que sea menor o igual al “Valor buscado” dentro de la columna 1 de la matriz. Para que se entienda mejor usaremos lo de las notas de la tabla anterior solo que como ahí son rangos, para poder usar “BUSCARV” colocaremos valores del siguiente modo:
Entonces, al aplicar las fórmulas podemos tener algo así:
En donde la columna “E” es netamente decorativa, en la columna “F” sí ingresamos la supuesta nota y en la columna “G” está la fórmula que necesitamos y que nos da el resultado esperado.
Y esa, amigos, es la forma de usar "BUSCARV". Hasta la próxima.
En muchas ocasiones los usuarios/as de Excel desean insertar fórmulas a través de VBA; para este fin hay varias propiedades del objeto “Range” que nos pueden ayudar. Aunque hay más, vamos a centrarnos, en esta ocasión, en cuatro:
- Formula
- FormulaLocal
- FormulaR1C1
- FormulaR1C1Local
En el caso de la propiedad “Formula”, toma como base el idioma primigenio del VBA, es decir, el inglés; además de eso toma como separador de lista la coma (,). Estos detalles son muy importantes a tomar en cuenta cuando insertemos una fórmula en una celda ya que significa que, sea cual sea el idioma de nuestro Office, debemos colocar la función en inglés. Asimismo, aunque nuestro separador de lista sea el punto y coma (;) deberemos usar la coma (,) en la macro. En todos los casos, el VBA detectará el idioma del Office y el separador de lista del sistema operativo, y en la celda colocará el adecuado. Podríamos decir que el inglés y la coma (,) son “universales” cuando de la propiedad “Formula” hablamos. Esto se entenderá mejor con un ejemplo. En mi portátil tengo el Office en Castellano y mi separador de lista es el punto y coma (;) pero insertaré fórmulas del siguiente modo:
Range(“A11”).Formula="=Sum(A1:A10)"
Range("G16").Formula = "=VlookUp(F16,B25:C29,2,1)" En las respectivas celdas tendremos las siguientes fórmulas:
=Suma(A1:A10)
=BuscarV(F16;B25:C29;2;1) Cuando usamos la propiedad “FormulaLocal”, al contrario, debemos usar las funciones en el idioma de nuestro Office y el separado de lista de nuestro sistema operativo. Por ejemplo, en mi portátil tendría que colocar así:
Range(“A11”).FormulaLocal="=Suma(A1:A10)"
Range("G16").FormulaLocal = "=BuscarV(F16;B25:C29;2;1)" En el caso de la propiedad “FormulaR1C1”, vamos a explicar su uso con uno de los ejemplos que ya hemos usado. Insertaremos en la celda “A11” la fórmula "=Suma(A1:A10)" del siguiente modo:
Range("A11").FormulaR1C1 ="=SUM(R[-10]C:R[-1]C)"Tal como con la propiedad “Formula”, en el caso de “FormulaR1C1” también se usan las funciones en inglés y el separador de lista debe ser la coma (,). Las referencias R1C1 deben ser entendidas del siguiente modo, para este caso:
- La celda de referencia sería en este caso la “A11”
- “R[-10]” hace referencia a 10 filas arriba de “A11”, es decir la fila 1
- “C” hace referencia a la misma columna, es decir a la “A”
- “R[-1]” hace referencia a una fila arriba de “A11”, es decir la fila 10
- “C”, nuevamente, hace referencia a la misma columna, es decir a la “A”
Con la propiedad “FormulaR1C1Local”, se usa la función en el idioma del Office y el separador de lista del sistema operativo y las referencia a filas cambia de “R” a “F”. Para insertar la misma fórmula del ejemplo anterior sería así:
Range("A11").FormulaR1C1Local ="=Suma(F[-10]C:F[-1]C)"Y eso es todo en esta ocasión, espero se haya entendido.. Hasta la próxima.
Abraham Valencia
Dim Izquierda As Single, Arriba As Single, Ancho As Single, Alto As Single
Application.DisplayAlerts = False
Application.ScreenUpdating= False
With Worksheets("Hoja1").Range("A1:D5")
Izquierda = .Left
Arriba = .Top
Ancho = .Width
Alto = .Height
.CopyPicture
End With Microsoft Excel es una hoja de cálculo y según los datos conocidos sigue siendo el programa más usado de la famosa suite Microsoft Office. Millones, de personas usan Excel como base de datos y muchos se apoyan en VBA pasa simular tener sus propios sistemas de venta, de facturación, etc. ¿Está mal eso? No necesariamente, aunque finalmente para una misma cantidad de datos Excel ocupará más espacio que un programa como Access, que es parte de la misma suite y que en realidad es el programa que debería usarse, de preferencia, para cosas como las mencionadas.
Cuando finalmente se opta por Excel como base de datos, es muy habitual que se cometan muchos errores que al final perjudican a los propios usuarios. Lo clásico es que para una misma base de datos se usan diferentes archivos. En muchas ocasiones, por ejemplo, para ventas o compras, he visto cosas de este tipo, en donde tienen un archivo por año:
Y dentro de esos archivos tienen normalmente una hoja por mes::
Siendo todos los archivos iguales. Por supuesto que cada hoja tiene los mismos campos. Otro ejemplo:
Entonces, se puede tener, para tres años entre ventas y compras, unos seis archivos y en total unas 72 hojas ¿una bicoca, cierto? Aunque no es nada comparado a casos como este:
En este caso, para tres años tendríamos 72 archivos y 864 hojas ¿qué les parece? Y eso contando solo hojas de datos cuando sabemos bien que muchos tienes además hojas para facturar, reportes, etc. Ahora, imagínense intentar hacer consolidados, ver ventas o compras por mes o año o entre fechas, o ver datos solo de ciertos clientes, etc. Hay que copiar/pegar datos, abrir y cerrar archivos, ubicar cada archivo necesario, etc. generando muchas veces perdida de tiempo, perdida de datos, archivos que se pueden ir corrompiendo y varias otras cosas más.
Entonces, idealmente ¿cómo se tendría que hacer? Pues en general es suficiente con un solo archivo y con una hoja para los datos, podría colocarse un campo para el tipo (venta o compra) y una columna de fechas para que de ese modo con filtros o tablas dinámicas (u otra herramienta) se puedan tener datos específicos por periodos de tiempo o clientes.
Les aseguro que de ese modo les será mucho más fácil manejar sus datos y en otra hoja pueden hacer sus reportes usando quizá VBA o funciones tipo “BuscarV”, aunque recordemos que Excel, a diferencia de Access, no tiene una herramienta automática para generarlos por lo que habrá que usar bastante el ingenio (y por supuesto leer mucho).
Espero haberlos ayudado. Hasta la próxima.
Abraham Valencia
En esta ocasión seguiremos trabajando con Microsoft Outllok pero ahora lo que veremos es como enviar hojas de un libro o incluso el propio libro como archivo adjunto del mensaje de correo. Voy a suponer que ya leyeron este artículo: Enlace, así que solo diré que para enviar el libro de Excel que es el activo en ese momento, bastará agregar una línea para adjuntarlo, además de lo habitual en estos casos. Con dicha línea lo que haremos es usar la propiedad “FullName”, que devuelve el nombre del libro incluyendo la ruta de su ubicación. De ese modo en el correo se adjuntará el archivo (siempre y cuando se haya guardado). Entonces, prueben esto:
Sub EnviarMensajeOutlook()
Dim OutlookApp As Outlook.Application
Dim objItem As MailItem
Set OutlookApp = CreateObject("Outlook.Application")
Set objItem = OutlookApp.CreateItem(olMailItem)
With objItem
.To = "abraham.valencia@gmail.com" 'Para
.Subject = "Adjuntando archivo" 'Asunto
.Body = "Estamos adjuntando un archivo" 'Cuerpo
.Attachments.Add ActiveWorkbook.FullName
Application.Wait (Now + TimeValue("00:00:03")) 'Tiempo para adjuntar
.Send 'Enviar
End With
Set OutlookApp = Nothing
Set objItem = Nothing
End Sub Quizá no sea necesario enviar el libro completo sino solo la hoja activa. De ser así, guardaremos la hoja activa como archivo, lo adjuntaremos y lo eliminaremos de inmediato, en el supuesto de que no lo necesitamos posterior al envío:
Sub EnviarMensajeOutlook()
Dim OutlookApp As Outlook.Application
Dim objItem As MailItem
Set OutlookApp = CreateObject("Outlook.Application")
Set objItem = OutlookApp.CreateItem(olMailItem)
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Temporal.xlsx"
ActiveWorkbook.Close
With objItem
.To = "abraham.valencia@gmail.com" 'Para
.Subject = "Adjuntando archivo" 'Asunto
.Body = "Estamos adjuntando un archivo" 'Cuerpo
.Attachments.Add ThisWorkbook.Path & "\Temporal.xlsx"
Application.Wait (Now + TimeValue("00:00:03")) 'Tiempo para adjuntar
.Send 'Enviar
End With
Kill ThisWorkbook.Path & "\Temporal.xlsx"
Set OutlookApp = Nothing
Set objItem = Nothing
End Sub De no desear que sea la hoja activa la que se envíe, basta indicar, en la línea respectiva, que hoja queremos copiar:
Sheets(“Hoja45”).Copy O incluso puede ser un archivo con solo algunas de las hojas del libro:
Sheets(Array("Hoja1", "Hoja4")).Copy Abraham Valencia
Definitivamente una de las “nubes” más usadas para compartir archivos hoy en día debe ser “Google Drive” y definitivamente muchos usuarios de Excel también la usan para compartir y/o trabajar archivos con otras personas, priorizando incluso su uso por sobre “OneDrive” y/o Excel Online.
Lo que muchos/as no saben es que cuando se carga un archivo de Excel en “Google Drive” y se intenta abrir en línea, lo que dicha “nube” hace es convertir dicho archivo en una “Hoja de Cálculo de Google” que es, en estricto, otro programa y por ende otro formato de archivo, es decir, deja de tener formato de Excel a pesar de que el aspecto es muy similar e incluso los nombres de muchas funciones son iguales (Google las hizo así por cuestiones de compatibilidad), lo que no es de extrañar dado que ambos son el mismo tipo de programa: Hojas de cálculo (y sí, la “Hoja de Cálculo de Google” lo que hizo es tomar el nombre en base al tipo de programa).
Algo importante es no olvidar que así haya compatibilidad entre programas, eso de ningún modo asegura que las características y/o fórmulas usadas permanezcan y/o funcionen tal cual lo hacen en Excel una vez que las tenemos en “Google Drive” y queremos usarlas en línea. Una característica que no es compatible, son las macros de Excel, ya que el lenguaje usado en ellas es el VBA mientras que la “Hoja de Cálculo de Google” usa Google Apps Scripts como lenguaje de programación. Una de las preguntas más recurrentes en los foros es justamente el uso de las macros de Excel en archivos colocados en “Google Drive” pero como quizás ya entendieron, pues no, no se pueden usar.
Entonces ¿es conveniente para los usuarios/as de Excel usar “Google Drive”? Pues para compartir archivos, es decir cargar y descargar, es totalmente apto como casi toda “nube”, pero si se trata de trabajar en conjunto, pues ya saben que, al no ser los mismos programas, así como hay compatibilidades también hay limitaciones y por supuesto si se trata de macros, no hay forma de hacerlas compatibles.
Hasta la próxima.
Abraham Valencia
Desde el cada vez mayor uso de Whatsapp no es raro que en los foros de Excel muchas persona busquen un modo de interactuar entre ambos; cada vez hay más preguntas al respecto pero ¿se puede lograr algo así realmente? Si bien no es complicado lograr que, sobre todo, a través de VBA Excel interactúe con otros programas de Office e incluso con otros de Microsoft (como Internet Explorer por ejemplo) con relativa facilidad, no necesariamente ocurrirá lo mismo con otros programas y, lamentablemente para los que buscan lograrlo, por lo menos hasta el momento Whatsapp es uno de ellos.
¿Eso quiere decir que no existe forma alguna de combinar el uso de ambos? Si bien ni Whatsapp Web ni la versión móvil permiten conexiones con VBA hay algunas alternativas que sugieren algunas personas. Una es usar la API de Whatsapp como sugiere Christian Delgado von Eitzen:Otra alternativa es usar el método "SendKeys" de VBA, a sabiendas de la inestabilidad y dilemas que esto puede traer. Aquí les dejo una alternativa de Jairo Martinez:
Entonces, aquí les dejo ambas a ver si cumplen las expectativas que ustedes tengan respecto al tema. Hasta la próxima.
Abraham Valencia
Como ya he comentado en otros artículos, en los foros/comunidades de Excel entre las preguntas más frecuentes están las relacionadas a cómo enviar mensajes de correo electrónico a través de Macros de Excel. Muchas de dichas preguntas incluso tienen carácter más puntual y no es raro que las personas pregunten por “alertas” automáticas cuando, por ejemplo, se cumplen ciertas fechas que se consideran límites. En esta ocasión vamos a dar algunos consejos para esos casos y usando Microsoft Outlook.
Primero, por si acaso, recordemos como enviar mensajes a través de Outlook: Enlace_1, luego también recordemos como enviar mensajes masivos: Enlace_2, algo que nos será útil en esta ocasión. Ahora vamos a suponer que tenemos los siguientes datos:
Lo que vamos a hacer que la macro se active a través de evento “Open” del libro, por lo que pondremos lo siguiente en el módulo del libro (que lleva por defecto el nombre “ThisWorkBook”):
Private Sub Workbook_Open()
Call EnviarAlertas
End Sub Entonces, dicho todo eso, nuestra macro debería quedar así:
Sub EnviarAlertas()
Dim OutlookApp As Outlook.Application
Dim objItem As MailItem
Dim UltimaFila As Long, x As Long
Dim FechaV As Date
Set OutlookApp = CreateObject("Outlook.Application")
Let UltimaFila = Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To UltimaFila
Let FechaV = Range("D" & x).Value
If FechaV < Date And Range("E" & x).Value = "" Then
Set objItem = OutlookApp.CreateItem(olMailItem)
With objItem
.To = Range("B" & x).Value
.cc = "tucorreo@correo.com"
.Subject = "Deuda vencida"
.Body = "Estimado/a señor/a " & Range("A" & x) & " su cuota de " & FormatCurrency(Range("C" & x).Value) & " venció el día " & Range("D" & x).Value
.Send
End With
Set objItem = Nothing
Range("E" & x).Value = "Sí"
End If
Next x
Set OutlookApp = Nothing
MsgBox "Cuentas revisadas"
End Sub No olvides activar la referencia a "Microsoft Outlook 15.0 Object Library" en el editor de VBA (en donde el 15.0 puede variar dependiendo de tu versión de Excel).
Y listo, eso es todo en esta ocasión, solo adáptalo a tus necesidades. Hasta la próxima.
Abraham Valencia
Entre las preguntas más seguidas que se encuentra en los foros de Excel están aquellas relacionadas al envío de mensajes de correo electrónico a través de macros. Hay mucha información en internet al respecto, tanto usando Microsoft Outlook como usando la librería “Collaboration Data Objects” (CDO). En esta ocasión vamos a centrarnos en usar Microsoft Outlook.
Si bien, como ya mencioné, hay mucha información respecto al envío de mensajes, no necesariamente es tan fácil encontrar cosas específicas como el realizar envíos masivos y además adjuntando algún archivo y precisamente eso es lo que mostraremos como hacer.
Lo primero es que, por si acaso, den una leidita a esto: Enlace. Ahora, vamos a suponer que tenemos los datos, incluyendo el correo electrónico, de aquellas personas a las que queremos enviar el mensaje. Los datos para el ejemplo tendrán: Nombre, correo, fecha (de un supuesto reporte), ruta (del supuesto archivo/reporte pdf).
Sabemos entonces en que columna está cada tipo dato. Vamos a suponer también que las filas de datos pueden ir incrementándose, por lo que será necesario averiguar la última fila de manera automática para lo que usaremos la variable “UltimaFila”. Como son varios registros usaremos un bucle del tipo “For – Next” que comience a recorrer todo desde la fila 2 ya que la 1 es la de los encabezados.
Como ya expliqué en otro artículo, cuando se usa el objeto "Outlook", y justamente se nota más cuando se hacen envíos masivos, lo que ocurre es que se van colocando los mensajes en la "Bandeja de salida" así que es recomendable que se tenga el Microsoft Outlook configurado para el envío automático cuando se abre/cierra para que el proceso continúe cuando se use otra vez dicho programa ya que probablemente la macro no termine de enviar los mensajes. Igual vamos a usar “Application.Wait” para que haga una pausa de 10 segundos a la macro y esperar que sea tiempo suficiente para que adjunten los archivos por completo y se envíe el mensaje antes de que siga el bucle (que irá acumulando en la “fila” de la bandeja de salida del Outlook).
Sub EnviarMensajeOutlook()
Dim OutlookApp As Outlook.Application
Dim objItem As MailItem
Dim UltimaFila As Integer, x As Integer
Set OutlookApp = CreateObject("Outlook.Application")
Let UltimaFila = Cells(Rows.Count, 2).End(xlUp).Row
For x = 2 To UltimaFila
Set objItem = OutlookApp.CreateItem(olMailItem)
With objItem
.To = Range("B" & x).Value
.Subject = "Reporte de compras"
.Body = "Estimado/a señor/a " & Range("A" & x) & " le enviamos el repote del día " & Range("C" & x).Value
.Attachments.Add Range("D" & x).Value
Application.Wait (Now + TimeValue("00:00:10"))
.Send
End With
Set objItem = Nothing
Next x
Set OutlookApp = Nothing
MsgBox "Todo enviado"
End Sub No olvides activar la referencia a "Microsoft Outlook 15.0 Object Library" en el editor de VBA (en donde el 15.0 puede variar dependiendo de tu versión de Excel).
En muchas ocasiones tenemos datos en nuestras hojas en Excel y en base a esos datos tenemos fórmulas, tablas dinámicas u otras. Normalmente lo tenemos hecho tipo tablas, algo así por ejemplo:
Si para ese caso quisiéramos saber el total de ventas tendríamos que usar una fórmula de este tipo:
=SUMA(D2:D15)Del mismo modo para crear una tabla dinámica, elegiríamos el rango “A1:D15”. Pero ¿qué pasa en esos casos cuando agregamos un registro? Es decir, si agregamos datos a la fila 16. Pues dicho registro no estará entre los datos que muestra la tabla dinámica y la fórmula anterior no tomará en cuenta el nuevo dato de la columna, es decir la celda “D16”. Podríamos, por ejemplo, usar rangos dinámicos con nombre (usando por ejemplo la función DESREF) pero existe una forma mucho más simple: Usar las tablas de Excel.
Las tablas son un conjunto de filas y columnas con datos y que Excel puede relacionar entre sí. La primera fila de una tabla contiene los encabezados de cada columna y eso ayudará a identificarlas. Las tablas facilitan el trabajo de datos con Excel.
Para convertir un rango en “Tabla” de Excel, colocamos el cursor en cualquier celda del rango en donde tenemos nuestros datos, luego elegimos, en la cinta de opciones, la pestaña “Insertar” y en el grupo “Tablas” damos click al botón “Tabla”, Excel seleccionará todo nuestro rango de datos y nos mostrará un cuadro de dialogo llamado “Crear tabla”. Ahí verificamos que todos los datos estén seleccionados y elegimos, de ser el caso, que “La tabla tiene encabezados”.
Los datos pasaran a tener esta apariencia:
Puedes cambiar los colores/estilo del diseño, el nombre de la tabla, exportar los datos, etc., haciendo uso de las “Herramientas de tabla”.
Si tenemos los datos como tabla, podríamos usar fórmulas de este tipo:
=SUMAR.SI(Tabla1[Local],"A",Tabla1[Venta])=SUMA(Tabla1[Venta])De agregar más datos a la tabla, si comenzamos por la primera fila sin datos inmediata inferior a dicha tabla y usamos las mismas columnas, automáticamente el Excel hará que la tabla extienda su rango. De esa forma las fórmulas seguirán actuando sobre los datos si necesidad de que tengamos que volver a modificarlas. Lo mismo ocurrirá, por ejemplo, con las tablas dinámicas cuyo origen de datos sea una tabla. Además de eso, las tablas, en cada columna cuentan con filtros que podemos usar de ser necesario:
Y eso es todo en esta ocasión. Hasta la próxima.
Abraham Valencia
Cuando programas, una de las grandes ventajas de trabajar con Excel y toda la suite de Microsoft es la compatibilidad entre su lenguaje Visual Basic for Applications (VBA). De este modo podemos trabajar con más de uno de sus programas a la vez y/o interactuar entre ellos solo debemos aprender, tal como en el caso de Excel, los objetos, propiedades y otros de cada programa.
Una forma de abrir o crear archivos desde el VBA es usar la función “CreateObject”. La sintaxis de dicho método es la siguiente:
CreateObject (clase, nombre del servidor)
En donde “clase” es el nombre de la aplicación y/o la clase del objeto para crear y “nombre del servidor” es justamente el nombre del servidor de red en donde se crea el objeto. Si el nombre de servidor se deja en blanco, el objeto se creará en el equipo desde donde se usa la función.
Vamos a dejar de lado lo del servidor y centrarnos en la creación de objeto en nuestro propio equipo. Por ejemplo, vamos a suponer que queremos crear un archivo de Word nuevo entonces lo primero que vamos a hacer es declarar una variable con la que nombraremos a nuestro objeto “Word”, después crearemos el objeto, lo haremos visible (por defecto se crea el objeto como no visible) y agregaremos un documento nuevo:
Sub CreandoWord()
Dim MiappWord As Object
Set MiappWord = CreateObject("Word.Application")
MiappWord.Documents.Add
MiappWord.Visible = True
Set MiappWord = Nothing
End SubLa línea “MiappWord.Documents.Add”, que es la que agrega el documento nuevo de Word, puede ser obtenida fácilmente con la grabadora de macros de Word, aunque lo menciono solo como referencia ya que ese sería un tema ajeno a Excel.
Supongamos ahora lo que queremos es abrir un archivo ya existente, pues usaríamos algo como esto:
Sub CreandoWord()
Dim MiappWord As Object
Set MiappWord = CreateObject("Word.Application")
MiappWord.Documents.Open ("D:\Filatelia\Actividades CAF 2018.docx")
MiappWord.Visible = True
Set MiappWord = Nothing
End SubUna forma en que el editor de VBA nos puede “ayudar” con los objetos, propiedades y métodos de, en este caso, Word, es si declaramos la referencia respectiva:
No olviden que el 15.0 puede variar dependiendo de la versión de Office que estén usando. Luego cambiamos la declaración de variable a tipo “Word.Application” y tendremos el siguiente resultado:
También podemos, por ejemplo, abrir una presentación hecha en Power Point del siguiente modo:
Sub AbrirPPT()
Dim appPpt As Object
Set appPpt = CreateObject("Powerpoint.Application")
appPpt.Presentations.Open Filename:=ThisWorkbook.Path & "\Hola.pptx"
appPpt.ActivePresentation.SlideShowSettings.Run
appPpt.Visible = True
Set appPpt = Nothing
End Sub En este caso el archivo de Power Point está en la misma carpeta que el archivo Excel. La línea que abren dicho archivo y la que activa la presentación, son parte del manejo de objetos y métodos de dicho programa pero lamentablemente en este caso dicho programa ya no cuenta con grabadora de macros que nos ayude pues Microsoft decidió retirarla desde la versión de Office 2007.
Es casi el mismo procedimiento para Infopath, Publisher, etc., pero no olvidemos que puede servir para otros programas como por ejemplo Internet Explorer:
Sub Guglear()
Dim ie As ObjectDim
PaginaWeb As String
PaginaWeb = "www.google.com.pe"
Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate PaginaWeb
Do
DoEvents
Loop Until ie.readyState = 4
ie.Visible = True
Set ie = Nothing
End Sub Y eso es todo en esta ocasión. Hasta la próxima.
Abraham Valencia
No conocí personalmente a Charles "Chip" H. Pearson, pero soy uno de los miles que recurrió a su página web para absolver alguna duda sobre VBA de Excel. Si mal no recuerdo la primera vez que lo leí fue el año 2006 y desde ese entonces use muchos de los enlaces a sus ejemplos y explicaciones para ayudar a guiar a muchas personas en este apasionante mundo del Excel que compartíamos.
Fue nombrado en 16 oportunidades, por Microsoft, como Most Value Professional (MVP) de Excel, galardón sin duda más que merecido. Creo que es uno de los expertos en Excel anglo parlantes más conocido por nosotros los hispano parlantes. Hace muy pocos días quise hace runa consulta en su web, como tantas veces hice, y estaba “fuera de línea”, realmente pensé que estaba migrando de “Hosting” o actualizando su web, o haciendo alguna copia de seguridad, no sé, algo de eso, jamás pensé en otra cosa. Hoy a través del Facebook de mi querido amigo Sergio Alejandro Campos (MVP Excel – México) me enteré que el gran “Chip” sufrió un accidente automovilístico hace unos días y que el día 19 de este mes falleció producto de las heridas sufridas. Una gran pérdida para su familia, pero también para esta gran familia mundial del Excel de la que muchos somos parte. Que en paz descanse el gran “CPearson”.
La función DESREF de Excel es, desde mi punto de vista, quizá una de las que podría ser más útil en cosas habituales pero aun así no es de las más usadas. Pero ¿qué hace y/o cómo trabaja dicha función? Microsoft define dicha función del siguiente modo: “Devuelve una referencia a un rango que es un número de filas y de columnas de una celda o rango de celdas. La referencia devuelta puede ser una celda o un rango de celdas. Puede especificar el número de filas y el número de columnas a devolver”.
¿Se entendió esa definición? ¿No tanto? Bueno, continuemos hasta entenderla completamente. Microsoft también nos dice que la sintaxis de la función es la siguiente:
DESREF (ref, filas, columnas, [alto], [ancho])
Asimismo, la definición de los argumentos de la función son los siguientes:
Nombre |
Descripción |
Ref |
Argumento obligatorio. Es la referencia en la que desea basar la desviación. La referencia debe referirse a una celda o un rango de celdas que son adyacentes entre ellas. |
Filas |
Argumento obligatorio. Es el número de filas, hacia arriba o hacia abajo, al que se desea hacer referencia tomando como base la celda del argumento “Ref”. Filas hacia abajo se señala en positivo y hacia arriba en negativo. |
Columnas |
Argumento obligatorio. Es el número de columnas, hacia la derecha o izquierda, al que desea que haga referencia tomando como base la celda del argumento “Ref”. Columnas hacia la derecha se señala en positivo y hacia la izquierda en negativo. |
Alto |
Argumento opcional. Es el alto, en número de filas, que se desea que tenga la referencia devuelta. El alto debe ser un número positivo. |
Ancho |
Argumento opcional. Es el ancho, en número de columnas, que se desea que tenga la referencia devuelta. El argumento ancho debe ser un número positivo. |
Es importante comentar que si en el argumento “Ref” usamos un rango de dos celdas o más, y usamos la función “DESREF” como única fórmula, no dará como resultado un error del tipo #¡VALOR!. Otra cosa a resaltar es que si alguno de los argumentos de la función se “salen” de la hoja, es decir que hacen referencia a filas menores a uno o columnas “menores” a la “A”, pues dará como resultado el error #¡REF!.
Comencemos con ejemplos simples para que se entienda como funciona:
Si bien las fórmulas están en las celdas de la columna “E”, las he colocado en texto en la columna “F” (en las celdas adyacentes correspondientes de la derecha) para que se aprecie más fácilmente. Las referencias son a una sola celda y a la posición, usando “Filas” y “Columnas”, de la que se requiere obtener el dato.
En este caso tomamos también una sola celda como referencia pero “mantenemos” dicha celda (Argumentos “Filas” y “Columnas” en cero – 0) y lo que hacemos es usar los argumentos “Alto” y “Ancho” para indicarle a la función “Suma” que es un rango más amplio que la referencia el que queremos usar.
En este caso, tenemos como referencia un rango de dos columnas de ancho y tres filas de alto, en el primer caso hacemos referencia a dos filas abajo y tres columnas a la izquierda y al no usar los argumentos “Alto” y “Ancho”, la referencia inicial mantiene su tamaño (dos columnas de ancho y tres filas de alto) por lo que el rango sumado corresponde a “B3:C5”. Para que quizá se entienda mejor, es como desplazar todo el rango inicial de referencia sin cambiarle el tamaño.
Con esos mismos datos, miren la segunda fórmula, solo suma el rango “B3:B4” dado que sí hemos usado los argumentos “Alto” y “Ancho” con los valores 2 y 1 respectivamente, cambiando, por decirlo de un modo, el tamaño inicial.
¿Hasta el momento no les convencen los ejemplos? Bueno, veamos algo quizá más habitual y/o práctico. Vamos a suponer que queremos usar una lista desplegable de validación que constantemente incrementa sus registros/elementos/datos y en cada ocasión tenemos que agregar más celdas o datos al origen, entonces, para evitar eso usaremos “DESREF”.
Supongamos que la lista está en la columna “A”, entonces vamos en la pestaña “Fórmulas” al grupo “Nombres definidos”, le damos “Click” a “Asignar nombre”, en “Nombre” colocamos “Mi_Lista” (o el nombre que quieran – sin las comillas) y en “Se refiere a” vamos a ingresar la siguiente fórmula: “=DESREF($A$1,0,0,CONTARA($A:$A),1)” (sin las comillas, no se olviden). Ahora van a la celda en donde desean la lista desplegable y en la pestaña “Datos”, en el grupo “Herramientas de datos” le dan “Click” al botón “Validación de datos”, eligen la pestaña “Configuración”, en “Permitir” eligen “Lista” y en “Origen” colocan “=Mi_Lista” (o el nombre que hayan elegido, y sin las comillas). Como resultado, cada vez que agreguemos datos en la columna “A”, dichos datos se agregaran automáticamente en la lista desplegable.
Espero haberlos ayudado. Hasta la próxima.
Abraham Valencia
Fue aproximadamente el año 2007 cuando empecé a escuchar los primero rumores, en redes, acerca de la desaparición de Microsoft Excel. Para muchas personas el cambio radical que hubo entre las versiones de Excel 2003 y Excel 2007 era ya de por sí un indicio a pesar de la ventaja que fue para muchos el incremento en el número de filas y columnas. Lo mismo se dijo del VBA pues Microsoft había anunciado ya años antes que la versión 6.3 (la de Excel 2003 y 2007) no sería renovada/actualizada es decir, sería la última. Un hecho que “alertó” más a los usuarios de Excel sobre lo que creían sería el final del Visual Basic For Application (VBA) y de forma posterior del Excel es que Microsoft decide no incluir VBA en la versión de su suite 2008 para Mac.
Otro hecho que no pasaba desapercibió para los usuarios de Excel y su VBA es la aparición de Visual Studio Tools For Office (VSTO) en el año 2003. Si bien desde Microsoft se comentaba que VSTO no había sido desarrollado como sustituto de VBA ,sino como un conjunto de herramientas de desarrollo disponibles en forma de complemento de Visual Studio que permiten crear aplicaciones de Office que se alojen en .NET Framework Common Language Runtime (CLR) para exponer su funcionalidad a través de .NET; para muchos el “marketing” aplicado por Microsoft sobre dicho paquete, era otro indicio de que VSTO era el reemplazo de VBA tal como Visual Basic Net lo fue de Visual Basic “clásico”.
A pesar de todo Office trajo la novedad, entre otras, de que la versión de VBA que incluía era la 7.1, es decir, a pesar del anuncio de años atrás, Microsoft había decidido renovar dicho entorno de programación. A esto se le sumó que la versión de Office 2011 para Mac volvió a incluir VBA.
Con los años Office ha ido incluyendo nuevas funciones y herramientas como Power Query, Power Pivot y Power Viewer que maximizan, por ejemplo, el trabajo con datos a través de Excel. Asimismo, se calcula que para el año 2017, a nivel mundial Excel contaba con entre 750 y 1000 millones de usuarios, lo que lo deja muy lejos de ser un programa que va camino al olvido. Es más, incluso a nivel de celulares/móviles se calcula que ese mismo año ya existían 120 millones de usuarios. Y si mencionamos los foros de Excel, definitivamente siguen siendo de los más visitados y activos que hay en el mundo virtual.
Bueno, después de todo esto yo considero que Excel no está ni medianamente cerca de su fin, así como tampoco el VBA tampoco; Microsoft, a pesar de los antiguos rumores, no parece estar interesado en desaparecer dicho programa, o no aún al menos. Solo un último comentario, si bien VSTO no debe ser visto como el reemplazo de VBA, si considero que podría usarse más y justamente ayudaría a que el desarrollo de aplicaciones de Excel con programación “protegida”, como siempre se expresan desean muchos, sea más distribuido.
Hasta la próxima.
Abraham Valencia
A pesar que desde Office 2007 (como complemento en dicha versión), Microsoft ha incluido la posibilidad de exportar en formato PDF sus archivos, es una pregunta muy frecuente en los foros de Excel la forma en la cuál se puede hacer mediante macros (VBA). Quizá la lógica nos diga a algunos/as que bastaría obtener dicho código usando la grabadora de macros con lo que tendríamos algo así:
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Abraham\Desktop\Libro1.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
End Sub Como se puede ver lo que se ha hecho es exportar la hoja activa (ActiveSheet) haciendo uso del método “ExportAsFixedFormat” y después vemos una serie de parámetros que la grabadora no nos explicará y que justamente ayudaremos a entender.
La web MSDN nos muestra la siguiente sintaxis para el método:
Expresión.ExportAsFixedFormat (Type, Filename, Quality, IncludeDocProperties, IgnorePrintAreas, From, To, OpenAfterPublish, FixedFormatExtClassPtr)
Para usar dicho método es obligatorio incluir la “Expresión” que puede ser cualquiera de estos tipos de objeto: Workbook , Sheet , Chart o Range. Entonces, podemos mandar a exportar en pdf el libro completo, hojas, gráficos e incluso solo un rango especifico. Ejemplos:
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Abraham\Desktop\Libro1.pdf" Range("A1:C2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Abraham\Desktop\Libro1.pdf" Sheets("Hoja2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Abraham\Desktop\Libro1.pdf" Los otros parámetros del método trabajan y/o se usan del siguiente modo:
Nombre |
Descripción |
Type |
Es el parámetro que define el tipo de archivo en que se exportará. Puede ser de dos tipos: xlTypePDF (pdf) o xlTypeXPS (xps). Su uso es obligatorio. |
Filename |
Es la cadena que indica la ruta y/o el nombre del archivo que se guardará. Su uso es opcional. De no llenarse por defecto se guarda en la misma carpeta que el archivo Excel y con el mismo nombre. |
Quality |
Es opcional. Define la calidad del archivo que se exportara, puede ser xlQualityStandard (normal) o xlQualityMinimum (mínima). |
IncludeDocProperties |
Establece si las propiedades del documento deben incluirse o no. Sus valores son True (para incluir) o False (no las incluye). Es opcional. |
IgnorePrintAreas |
Es opcional. Establece si se ignora las áreas de impresión establecidas al publicar. Puede ser True para dicho efecto o False para que use las áreas de impresión establecidas al momento de la publicación. |
From |
Establece el número de página desde donde se comenzará a publicar/exportar. Si se omite el parámetro, comienza desde la primera página. No confundir página con hoja. |
To |
Establece el número de la última página que se publicará/exportará. Si se omite el parámetro, se publica/exporta hasta la última página. No confundir página con hoja. |
OpenAfterPublish |
Establece si el archivo se abrirá después de ser publicado/exportado. Sus valores pueden ser True o False. Es opcional. |
FixedFormatExtClassPtr |
Es opcional. Representa un puntero de la clase personalizada en un complemento que implementa la interfaz IMsoDocExporter COM que permite llamadas a una implementación alternativa de código para el formato del documento. El valor predeterminado es un puntero nulo. |
Siguiendo con los ejemplos, en éste solo se exporta a PDF desde la página 3 a la 6 de la “Hoja2”:
Sheets("Hoja2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Abraham\Desktop\Libro1.pdf", From:= 3, To:= 6 En este otro ejemplo, se exporta un rango en calidad “Standard” y se abre el documento posterior a ser exportado:
Range("A1:C2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Abraham\Desktop\Libro1.pdf", Quality:= xlQualityStandard, OpenAfterPublish:=True No olvidemos que los valores de parámetros como “Filename”, “From”, “To”, etc., pueden ser tomados de celdas o desde variables sin ningún problema si los valores ha sido bien establecidos:
Sheets("Hoja2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= Range("C12").Value , From:= Range("R3").Value, To:= Range("R4").ValueDim PrimeraPagina As Long, UltimaPagina As Long
Dim RutayNombre as String
Let PrimeraPagina = Range("R3").Value
Let UltimaPagina = Range("R4").Value
Let RutayNombre= Range("C12").Value
Sheets("Hoja2").ExportAsFixedFormat Type:=xlTypePDF, Filename:= RutayNombre, From:= PrimeraPagina, To:= UltimaPagina Y esas son algunas cosas que es importante concoer para exportar a PDF (o XPS) nuestros archivos de Excel.
Hasta la próxima.
Abraham Valencia
En innumerables ocasiones, cuando de manejar las hojas de Excel como objetos en VBA se trata, he visto que se hace de manera indiferente tanto usando “Sheets” como usando “Worksheets” pero ¿son ambas lo mismo?
Según las definiciones que hay en el “Microsoft Developer Network” (MSDN), “Sheets” hace referencia a “la colección de hojas que puede contener objetos gráfico y hojas de trabajo” y “Worksheets” es “la colección que contiene todas las hojas de trabajo en un libro”.
Entonces:
- Worsheets: Solo hojas de trabajo (u hojas de cálculo como normalmente las llamamos u hojas a secas)
- Sheets: Hojas de trabajo y hojas tipo gráfico (no confundir con los gráficos insertados en hojas)
Para que se entienda mejor, vamos a suponer que tenemos un archivo con tres hojas así:
Entonces usamos esta macro muy simple:
Sub ContarHojas()
MsgBox Worksheets.Count
End Sub Sub ContarHojas()
MsgBox Sheets.Count
End Sub La colección “Worksheets” nos da como resultado “3” y el resultado es el mismo cuando usamos “Sheets” porque dicha colección contiene, también, a las hojas en cuestión. Veamos ahora que ocurre en este otro caso:
Si aplicamos las macros del ejemplo anterior, con “Worksheets.Count” tenemos como resultado “2” pero en esta ocasión con “Sheets.Count” tendremos como resultado “4”. “Sheets” considera todas las hojas del libro (4 en total) y “Worksheets” solo las “hojas de trabajo” (hojas de cálculo).
Pero ¿son estos los únicos tipos de hojas que trae Excel? Vamos a recordar una imagen que vimos líneas arriba:
Hay tres objetos ahí que nos interesan: Macro de Microsoft Excel 4.0, Hoja internacional de macros y el Dialogo de Excel 5.0. Las dos primeras son herencia de las macros de Excel 4.0 y el tercero es herencia de Excel 5.0. En los tres casos las versiones posteriores de Excel las mantienen por una cuestión de compatibilidad (evidentemente no con todas las características de sus versiones iniciales).
En el MSDN nos dicen que hay cinco tipos de hojas:
Nombre |
Constante |
Descripción |
xlChart |
-4109 |
Hoja de Gráfico |
xlDialogSheet |
-4116 |
Hoja de Dialogo |
xlExcel4IntlMacroSheet |
4 |
Hoja Internacional de Macros |
xlExcel4MacroSheet |
3 |
Hoja Macro de Microsoft Excel 4.0 |
xlWorksheet |
-4167 |
Hoja de trabajo |
(Enumeración XlSheetType)
Entonces, si en nuestro archivo tenemos algo como esto:
Al usar “Worksheets.Count” el resultado será “2” pero con “Sheets.Count” será “6”, ya que la colección “Sheets” considera a todas las hojas sin excepción. Si bien en la definición del MSDN sobre la colección “Sheets” habla solo de dos tipos de hojas, al parecer podría deberse a lo ya mencionado: Que los otros tipos se mantienen solo por compatibilidad y por eso han obviado mencionarlo (O por lo menos eso es lo que yo, y otras personas, creemos).
Hasta la próxima.
Abraham Valencia
Muchas veces deseamos que nuestros trabajos en Excel, o en otros programas de Office incluso, se puedan enviar a través de nuestro correo electrónico. Podríamos decir que si tenemos dicho correo configurado en nuestro Microsoft Outlook no será muy difícil lograrlo.
A través de VBA podemos manejar desde Excel nuestro Microsoft Outlook como un objeto y así enviar mensajes adjuntando incluso archivos o nuestra firma digital, si es que la tenemos.
Lo primero que recomiendo hacer es activar la referencia respectiva en el editor de VBA:
En mi caso dice 15.0 por la versión de Office que tengo en la respectiva PC pero se debe activar la que a cada uno le corresponda.
Para continuar, lo que vamos a hacer ahora es crear un objeto “Outlook” y también usaremos el método “CreateItem” con el parámetro del tipo “olMailItem” que creará un nuevo mensaje (objeto “MailItem”):
Dim OutlookApp As Outlook.Application
Dim objItem As MailItem
Set OutlookApp = CreateObject("Outlook.Application")
Set objItem = OutlookApp.CreateItem(olMailItem) With objItem
.To = "destinatario@ejemplo.com" 'Para
.CC = "segundodestinatario@ejemplo.com" 'Con copia
.Subject = "Prueba" 'Asunto
.Body = "Este es un mensaje de prueba" 'Cuerpo
End With Lo único que faltaría es que usemos el método “Send” para enviar el mensaje:
.Send Toda nuestra macro junta podría quedar así:
Sub EnviarMensajeOutlook() Dim OutlookApp As Outlook.Application Dim objItem As MailItemSet OutlookApp = CreateObject("Outlook.Application") Set objItem = OutlookApp.CreateItem(olMailItem)With objItem .To = "destinatario@ejemplo.com" 'Para .CC = "segundodestinatario@ejemplo.com" 'Con copia .Subject = "Prueba" 'Asunto .Body = "Este es un mensaje de prueba" 'Cuerpo .Send End With Set OutlookApp = Nothing Set objItem = Nothing End Sub
Si deseamos poder ir cambiando el correo de destino o el texto del cuerpo del mensaje o etc., sin tener que modificar la macro, podemos colocar los datos en celdas o variables:
With objItem
.To = Range("A1").Value
.CC = Range("A2").Value
.Subject = Range("A3").Value
.Body = Range("A4").Value
.Send
End With En este momento ya se deben de haber dado cuenta (o ya sabían) que el cuerpo del mensaje se envía sin formato y quizá lo que queremos es más bien que dicho mensaje tenga formato como ya estamos, casi todos, acostumbrados hoy en día cuando usamos el correo electronico. Para lograr eso ya no debemos usar la propiedad “Body” sino que ahora usaremos la propiedad “HTMLBody” pero tendremos que aplicar algo de “HTML” en nuestro VBA:
.HTMLBody = "<html>" & _
"<body><font color=""#FF0000"" size=""6"" face=""Comic Sans MS, cursive""><strong>Hola mi querido y estimado amigo</strong></font>" & _
"</body></html>" Si lo que queremos, además, es adjuntar algún archivo a nuestro mensaje, debemos usar la propiedad “Attachments” y su método “Add”. Podemos agregar más de un archivo del siguiente modo:
.Attachments.Add "D:\Miarchivouno.xlsm"
.Attachments.Add "D:\MiArchivoNuevo.xlsx" Es bueno recordar que también podemos usar un bucle y/o variables para agregar más archivos.
Siguiendo con los adjuntos, probablemente tengamos nuestra firma en formato “JPG” y queremos incluirla en el cuerpo del mensaje; de ser así, lo primero que debemos hacer es adjuntar el archivo:
.Attachments.Add “D:\MiFirma.jpg"Después incluiremos dicha imagen en el cuerpo del mensaje del siguiente modo y/o similar:
.HTMLBody = "<html>" & _
"<body>" & _
"<p>Aqui tu mensaje</p>" & _
"<br>" & _
"<br>" & _
"<img src='cid:'" & .Attachments.Item(1).Filename & "'' height=100 width=75>" & _
"</body>" & _
"</html>" Hasta la próxima.
Abraham Valencia
PD: En realidad cuando se usa el objeto "Outlook", y sobre todo se nota cuando se hacen envíos masivos, lo que ocurre es que se van colocando los mensajes en la "Bandeja de salida" así que es recomendable que se tenga el Microsoft Outlook configurado para el envío automático al abrir/cerrar para que el proceso continúe el envío así se cierre el Excel. Igual si se desea que se haga casi al instante el envío desde la macro, se puede añadir un Application.Wait después del "Send" o tener el Outlook abierto.
Extras:
En ocasiones deseamos enviar un rango de nuestra hoja como parte del cuerpo del mensaje, hay algunas alternativas como convertir el rango en imagen y colocarla en el cuerpo del mensaje, similar a lo que se hace con las firmas pero la mejor alternativa que he visto es usar una “Función Definida por el Usuario” (UDF) que convierte el rango en lenguaje “HTML” y permite incluirlo en el cuerpo del mensaje a través de la propiedad “HTMLBody”. EL autor es Ron de Bruin y la UDF podemos encontrarla aquí:
http://www.rondebruin.nl/win/s1/outlook/bmail2.htm
Cuando tenemos más de un correo configurado en el Outlook, recomiendo usar una UDF (Autor: Antoni - Galicia, España) para poder elegir el correo que queremos usar:
https://ayudaexcel.com/foro/topic/32737-elegir-cuenta-de-correo-de-outlook/
De no elegir alguno de los correos con la UDF, se usará el correo configurado como predeterminado.