lunes, 18 de febrero de 2019

Extraer datos de tablas de webs

Ya hemos visto en un artículo anterior (Enlace) como identificar objetos/elementos de una página web y sobre todo como interactuar con ellos, pero algo de lo que no hablamos fue de cómo extraer datos de por ejemplo las tablas que no es difícil encontrar en ellas.

Por supuesto que cuando hablamos de tablas de una página web lo primero que probablemente se nos viene a la cabeza es usar la herramienta de Excel “Obtener datos externos”, que nos permite elegir las tablas que identifica en dicha web e importarlas a nuestra hoja de Excel. En muchos casos la página quizá tenga usuario y contraseña, pero la herramienta no permite visualizar la web y sus campos, así como ingresar esos datos y seguir navegando hasta que cargue la página con las tablas; una vez que ocurre eso elegimos la tabla que deseemos y listo, tendremos los datos en nuestra hoja de Excel.

:

Es muy probable que queramos que esto sea automático y para ello podemos por supuesto usar VBA. Esto puede lograrse usando el objeto “QueryTable” y obteniendo así los datos de la web deseada a través de macros y de tener usuario y clave, podremos usar su propiedad “PostText” para “enviarlos” y poder seguir navegando. La desventaja de este método (con VBA o no), es que nos importa los datos completos de la tabla, no hay forma de indicar que sean solo algunas filas y/o columnas. Entonces ¿cuál es la solución?.

La forma que voy a sugerir en esta ocasión es trabajar directamente sobre los objetos/elementos que en las webs está definidos por la etiqueta “Table”. Para eso usaremos el método “getElementsByTagName” y debemos recordar/saber también que los índices de las tablas de una web comienzan en cero (0). Aprovecharemos que la Universidad de Valencia (España) tiene una web con varias tablas de ejemplo: Enlace

En esta ocasión no solo usaremos el objeto “Internet Explorer” sino que también haremos lo propio con objetos HTMLTable y HTMLDocument. Para ello lo primero será activar las referencias a “Microsoft HTML Object Library”, en seguida vamos a declarar tres variables

Dim ie As Object
Dim doc As HTMLDocument
Dim htmTable As HTMLTable  

La primera es para nuestro objeto “Internet Explorer”, la segunda es para hacer referencia a la propiedad “Document” (Obtiene, a su vez, el objeto de la web activa) de dicho objeto y la tercera hará referencia a la etiqueta “Table”, es decir a la tabla o tablas de las que extraeremos los datos. Dicho eso, y suponiendo que queremos los datos de la primera tabla de la web de ejemplo, agregaremos estas líneas:

Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "https://www.uv.es/jac/guia/tablaeje.htm"
Do: DoEvents: Loop Until ie.ReadyState = 4 
Set doc = ie.Document 
Set htmTable = doc.getElementsByTagName("Table")(0) 

Hasta ese momento ya estamos conectados a la tabla, pero para extraer los datos necesitamos saber algunas cosas como por ejemplo que dichas tablas tienen una colección “Rows” (filas) y estás a su vez tienen la propiedad “Lenght” que retorna el número de elementos de la colección correspondiente. Entonces, para obtener el número de filas de la tabla usaremos lo siguiente:

htmTable.Rows.Length

Del mismo modo, para saber cuántas celdas /columnas tiene una fila, usaremos la colección “Cells” que tienen dichas filas y podemos nuevamente usar la propiedad “Lenght”. Ah, no olvidar que, según su índice, la primera fila es la cero (0), entonces para saber/obtener el número de celdas/columnas, usaremos lo siguiente:

htmTable.Rows(0).Cells.Length

Para obtener el valor de las celdas usaremos la propiedad “InnerText”. Por ejemplo, en la misma tabla para obtener el valor de la primera celda de la fila superior (en este caso la letra “A”):

htmTable.Rows(0).Cells(0).innerText

Agreguemos todo en la macro de este modo:

Sub tablaenWeb()

Dim ie As Object 
Dim htmTable As HTMLTable 
Dim doc As HTMLDocument 

Set ie = CreateObject("InternetExplorer.Application") 
ie.Navigate "https://www.uv.es/jac/guia/tablaeje.htm" 
Do: DoEvents: Loop Until ie.ReadyState = 4 

Set doc = ie.Document 
Set htmTable = doc.getElementsByTagName("Table")(0) 

MsgBox "La primera tabla tiene " & htmTable.Rows.Length & " filas" 
MsgBox "Además, su primera fila tiene " & htmTable.Rows(0).Cells.Length & " columnas" 
MsgBox "Y la primera celda de la fila superior tiene el siguiente valor en ella " & htmTable.Rows(0).Cells(0).innerText 

ie.Visible = True 

Set ie = Nothing: Set doc = Nothing: Set htmTable = Nothing 

End Sub 

Ahora vamos a usar la decima tabla de la web (índice 9) para extraer sus datos y llevarlos a nuestra hoja. Ya con las colecciones mencionadas bastará colocar todo en bucles:

Sub tablaenWeb() 
Dim ie As Object 
Dim htmTable As HTMLTable 
Dim doc As HTMLDocument 
Dim nFilas As Integer, nColumnas As Integer, x As Integer, y As Integer 

Set ie = CreateObject("InternetExplorer.Application") 

ie.Navigate "https://www.uv.es/jac/guia/tablaeje.htm" 
Do: DoEvents: Loop Until ie.ReadyState = 4 

Set doc = ie.Document 
Set htmTable = doc.getElementsByTagName("Table")(9) 
Let nFilas = htmTable.Rows.Length 
Let nColumnas = htmTable.Rows(0).Cells.Length 

For x = 1 To nFilas
     For y = 1 To nColumnas
         Cells(x, y).Value = htmTable.Rows(x - 1).Cells(y - 1).innerText
     Next y 
Next x 

ie.Visible = True 

Set ie = Nothing: Set doc = Nothing: Set htmTable = Nothing 

End Sub 
Esto último, sobre todo, creo yo, será útil cuando no se sabe la cantidad de filas y columnas de una tabla, sobre todo en las webs de consulta de datos. Y eso es todo por hoy.

Abraham Valencia

miércoles, 30 de enero de 2019

Interactuando con internet

Sin duda el uso de internet para diversas, tareas, consultas, trabajos, etc., es cada vez mayor y del mismo modo el número de usuarios/as de Microsoft Excel sigue en ascenso lo que ocasiona que muchas de las preguntas en diversos foros sea sobre cómo usar/navegar por internet desde Excel.

Por supuesto que la respuesta es el uso de VBA pero lo más importante es reconocer y/o identificar los objetos de las páginas web con las que queremos interactuar. Algo que es importante aclarar es que si no deseamos usar aplicaciones (o librerías u otros) de terceros necesariamente interactuaremos con Internet Explorer, no con Edge o Chrome..

Lo primero será crear un objeto “Internet Explorer” del siguiente modo:

Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application") 

Después usaremos su método “Navigate” para abrir una página web y la propiedad “Visible” para que se vea la aplicación recién abierta.

IE.navigate "https://www.google.com.pe" 
IE.Visible = True Set 
IE = Nothing 

De ese modo tendremos el IE abierto con google cargado. Por cierto, para ver/leer sobre otras propiedades, eventos y métodos del objeto Internet Explorer, miren por aquí: Enlace

Ahora vamos a interactuar con los objetos (elementos) dentro de Google, pero para eso debemos saber que será necesario usar la propiedad “Document” así como cualquiera de los cuatro métodos siguientes:

getElementById
getElementsByTagName  
getElementsByClassName  
getElementsByName 

Con cualquiera de ellos podremos identificar el objeto/elemento con el cual queremos interactuar, solo basta encontrar el Id, Tag, Clase (Class) o Nombre (name) y usar el método correspondiente. No todos los objetos/elementos tienen/usan las cuatro, pero nos bastará hallar una para poder lograr nuestro cometido.

Siguiendo con Google, vamos a suponer que lo tenemos abierto en nuestro Chrome, entonces situaremos el cursor dentro del cuadro de texto de la web y le daremos click derecho con lo que se nos mostrará el siguiente menú contextual:

Automáticamente le daremos Click a “Inspeccionar” y nos dará el siguiente resultado:

Se puede apreciar que el cuadro de texto tiene dos formas que nos ayudan a identificarlo, su clase (Class = gLFyf gsfi) y su nombre (Name = q). Usaremos el nombre y a su vez la propiedad “Value” del objeto/elemento, por lo que tendríamos que colocar así:

IE.document.getElementsByName("q").Value = "Hola a todos"
Previo a eso agregaremos un bucle que permita que, antes de intentar enviar valores al cuadro de texto, la página web cargue completamente. Para eso usaremos la propiedad “ReadyState” con el valor cuatro (4) equivalente a que el objeto ha recibido todos los datos. Entonces, hasta el momento tendremos esto:Entonces, dicho todo eso, nuestra macro debería quedar así:
Sub Navegar() 
Dim IE As Object 
Set IE = CreateObject("InternetExplorer.Application") 
IE.Navigate "https://www.google.com.pe" 
Do Until IE.ReadyState = 4     
 DoEvents 
Loop 
IE.document.getElementsByName("q").Value = "Hola a todos" 
IE.Visible = True 
Set IE = Nothing 
End Sub 

Al correr la macro nos dará un error en la línea en donde estamos intentando enviar un valor (una frase) al cuadro de texto de la web. Dado que en el caso de las web muchas veces los métodos arrojan resultados de lista de nodos, algunas veces se hace necesario identificar dicho índice. Entonces, para evitar dicho error agregaremos el índice del objeto/elemento del siguiente modo:

IE.document.getElementsByName("q")(0).Value = " Hola a todos"

Ahora solo nos falta hacer “click” al botón correspondiente para terminar. Si usamos otra vez “Inspeccionar” pero esta vez previamente colocando el cursor sobre el botón “Buscar con Google”, veremos que su nombre es “btnK”. Con ese nombre (al que agregaremos también el índice) y su evento “Click” habremos terminado. Coloca esta línea posterior a la que vimos antes de este párrafo:

IE.Document.getElementsByName("btnK")(0).Click

Listo, ahora al correr la macro abrirá Internet Explorer, cargará Google, colocará “Hola a todos” y buscará esa frase dándonos como resultado miles de enlaces.

Hasta la próxima.

Abraham Valencia

martes, 25 de diciembre de 2018

¡Felices Fiestas!

Quiero desearles a todos mis amigos y amigas que visitan este blog unas muy felices fiestas de fin de año, espero que lo reciban con su familia y/o con las personas que más quieran y espero que el año 2019 sea un año de mucha prosperidad.


¡UN ABRAZO EXCELIANO PARA TODOS/AS!


Abraham Valencia



(Imagen tomada de internet)

viernes, 14 de diciembre de 2018

Usando BUSCARV

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:

Si usásemos uno (1) nos daría como resultado lo correspondiente al código “A1005” dado a que así se buscaría una coincidencia aproximada. Quizás en casos como este no sea tan importante, pero veamos un ejemplo en donde sería muy útil.

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.

Abraham Valencia

domingo, 25 de noviembre de 2018

Insertar fórmulas con macros

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)" 
La desventaja, para mí, es que solo será útil en computadoras con Office en nuestro mismo idioma y con el mismo separador de lista; en otros casos insertará las fórmulas con errores.

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