¿Cómo importar Datos de un sitio web a Excel?
Es bien sabido que la World Wide Web contiene enormes datos útiles. Sin embargo, tenemos que importar los datos a Microsoft Excel antes de hacer cualquier tipo de análisis. Hay dos métodos que puedes usar para completar este tipo de tareas.
Obtener datos externos de la Web Manualmente
Supongamos que desea descargar ingresos sobre las mejores películas para hacer dinero de esta página web, aquí le muestra cómo hacerlo fácilmente.
Abra Microsoft Excel y haga clic en la pestaña Datos, en el grupo Obtener datos externos, haga clic en Desde Web. Después de que aparezca el cuadro de diálogo Nueva consulta web, copie la dirección web (http://www.the-numbers.com/movie/records/All-Time-Worldwide-Box-Office) en el campo Dirección y haga clic en el botón Ir. Excel comenzará a descargar la página web como se muestra en la Figura 1.1. Simplemente haga clic en No si obtendrá un cuadro de advertencia de error de script que es similar al de la siguiente figura. La caja desaparecerá y no tendrá ningún impacto en su proceso de importación.
Figura 1.1
Hay una flecha en el cuadro amarillo en la esquina superior derecha del cuadro de diálogo Nueva consulta web. Al hacer clic en él, puede determinar si desea mostrar iconos similares antes de las tablas o no. Por ejemplo, no hay un icono de flecha junto a la tabla en el panel izquierdo de la Figura 1.2. Y habrá un icono (en el panel derecho) después de hacer clic en el botón de flecha para mostrar los iconos.
Figura 1.2
Haga clic en el icono de flecha situado junto a las tablas que desea seleccionar. Los iconos y las tablas cambiarán y serán similares a los que se muestran en el panel izquierdo de la Figura 1.3. Después de hacer clic en Importar, se le solicitará un cuadro de diálogo Importar datos. Rellene el rango (de la columna A a la columna H en nuestro caso) donde desea colocar los datos y haga clic en ACEPTAR.
Figura 1.3
Los datos se importarán a Excel después de hacer clic en Aceptar. Si hace clic con el botón derecho en cualquier celda de la tabla y selecciona Actualizar, Excel saldrá y extraerá los datos más recientes de una página web.
Figura 1.4
E incluso puede configurar sus datos de consulta para que se actualicen de la manera que desee. Simplemente haga clic con el botón derecho en cualquier celda dentro de la tabla, seleccione Propiedades de rango de datos. En el cuadro de diálogo Propiedades del rango de datos externo solicitado, puede cambiar la configuración del Control de actualización para cambiar el estilo. Por ejemplo, puede especificar la consulta que se actualizará cada 60 minutos o cuando se abra el archivo.
Raspar datos mediante programación VBA
La programación VBA se puede utilizar para raspar datos de una página web. Es mucho más útil y difícil cuando se compara con el primer enfoque. Además, debe comprender qué es HTML antes de aprender a raspar datos usando programación VBA. Te recomiendo que aprendas conocimientos básicos de HTML de este sitio web si no sabes nada o poco sobre HTML. Y desde aquí, puede aprender casi todo lo relacionado con el raspado web usando VBA. Este artículo solo le mostrará dos ejemplos.
Raspar datos de una página web
Supongamos que queremos extraer el nombre de la empresa, la dirección de correo electrónico y el nombre de contacto de esta página web. Si abre esta página web, encontrará que hay un bloque de contactos en la parte inferior. La Figura 2.1 muestra el bloque de contactos y el código fuente correspondiente. La información en cajas rojas es lo que queremos necesitar. Y los que tienen un subrayado verde son exactamente lo que necesitamos extraer.
Figura 2.1
El siguiente código puede ayudarlo a extraer la información solicitada anteriormente y ponerla en la primera hoja de trabajo.
| Código fuente |    |
Sub Retrieve_Click () ' Create InternetExplorerSet IE = CreateObject("InternetExplorer.Aplicación") ' No veamos la ventana del navegador.Visible = False ' Abre la página web.Navegue "http://www.austrade.gov.au/SupplierDetails.aspx?ORGID=ORG8160044431&folderid=1736 "' Espere mientras IE se carga mientras IE.readyState < > 4 O IE.Busy = True DoEvents Loop ' Recuperar el nombre de la empresa, la dirección de correo electrónico & información de contacto Set contactobj = IE.documento.getElementsByClassName ("bloque de detalles de contacto oscuro") htext = contactobj (0).innerHTML MsgBox htext If InStr (htext, " < p> Nombre de la empresa:") Entonces ThisWorkbook.Hojas de trabajo(1).Cells(1, 1) = Split (Split (htext, " < p> Nombre de la empresa: ")(1), "<br") (0) End If If InStr (htext, "mailto:") Entonces ThisWorkbook.Hojas de trabajo(1).Cells(2, 1) = Split (Split (htext, " mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, "<p>Name: ") Entonces ThisWorkbook.Hojas de trabajo(1).Las células(3, 1) = Split(Split(htext, "<p>Nombre: ")(1), "<br")(0) End If ThisWorkbook.Hojas de trabajo(1).Celdas (4, 1) = IE.Ubicación Visita este libro de trabajo.Guardar conjunto IE = Nada Conjunto contactobj = Nada Fin Sub
«IE.documento.getElementsByClassName («contact-details block dark») » le permite obtener todos los elementos con la clase name – contact-details block dark. Esta página web enumera casi todas las propiedades y métodos que se pueden usar en elementos HTML. Puede elegir el adecuado para su propio problema.
La propiedad innerHTML puede permitirle establecer o devolver el contenido de un elemento HTML. En nuestro caso, devolvió el contenido del elemento con el nombre de clase «contact-details block dark» y lo estableció en una variable htext. El contenido (Figura 2.2) puede ser solicitado por ‘Msgbox htext’.
Figura 2.2
Se puede ver que el texto está bien estructurado. Es por eso que podemos usar la función de DIVISIÓN para extraer lo que necesitamos. Por ejemplo, si tomamos » < p> Nombre de la empresa:»como delimitador y fórmula» Split (htext, » < p> Nombre de la empresa:») (1)»puede devolver el texto completo después de» <p>Nombre de la empresa:». Para este texto recién devuelto, podemos tomar » < br «como delimitador y obtener el texto antes del primer» <br » y ese es el nombre de la empresa. En resumen, la función de DIVISIÓN es una herramienta flexible que puede usar para extraer casi todo. Otras funciones útiles incluyen LEN, INSTR, IZQUIERDA, DERECHA, MEDIA y REEMPLAZAR. No voy a discutir en detalle aquí.
Después de hacer clic en Aceptar en la Figura 2.2, los datos solicitados se pueden importar de la web a una hoja de cálculo de Excel. Por ejemplo, la celda A1 contiene el nombre de la empresa, mientras que la celda A4 contiene la dirección de la página web de la empresa.
Figura 2.3
Al agregar el siguiente código antes de guardar el libro de trabajo, puede agregar un hipervínculo a la celda A4.
| código Fuente | |
'Agregar hyperlinkThisWorkbook.Hojas de trabajo(1).Hipervínculo.Agregar ThisWorkbook.Hojas de trabajo(1).Celdas (4, 1), este libro de trabajo.Hojas de trabajo(1).Células(4, 1)
Si hace clic en la celda A4, puede volver a visitar la página web. Esto es útil especialmente cuando tiene que recuperar datos para muchas empresas. Ya que puede hacer clic en cualquier hipervínculo para volver a visitar la página web correspondiente y agregar o actualizar cualquier información manualmente durante la revisión posterior.
Figura 2.4
Interactuar con páginas web
El ejemplo anterior solo ilustra cómo recuperar datos de una página web estática. Pero más que a menudo, se nos requiere que extraigamos haber interactuado con páginas web para recuperar una gran cantidad de datos. Mira la Figura 3.1. Le muestra cómo llegar a la página web del ejemplo anterior. Pueden ver que hay muchas industrias y para cada industria, hay muchas empresas. Por ejemplo, hay 651 empresas en la industria agroalimentaria. ¿Qué debemos hacer si queremos extraer la información de contacto de todas las empresas de todas las industrias?
Figura 3.1
Bueno, el punto clave es cómo hacer que VBA interactúe con páginas web como lo que hacemos manualmente. Tomemos la ventaja de los SWIS como ejemplo. En general, esperamos que Excel pueda hacer clic en Agronegocios (panel superior de la Figura 3.1) y así activar IE para que nos dirija a la segunda página web. En la segunda página web (panel inferior de la Figura 3.1), Excel puede hacer clic en S. W. I. S Advantage, y luego IE nos dirige a la página como se muestra en la Figura 2.1 para que podamos recuperar la información de contacto de S. W. I. S Advantage.
Después de ingresar el siguiente código en Visual Basic Editor y enviar el código, verá que su IE está abierto. La primera página web aparece seguida de la segunda página web. Aquí aprenderá cómo obtener el elemento de la lista desplegable, seleccionar una opción y activar un evento después de seleccionar la opción. «m = IE.documento.getElementsByTagName(«opción»).Longitud-1″ le dará un número total de opciones. Esto se puede usar para el siguiente bucle.
| Código fuente | |
Sub retrieve () ' Create InternetExplorerSet IE = CreateObject("InternetExplorer.Aplicación") ' Veamos la ventana del navegador.Visible = True ' Abre la página web.Navegue "http://www.austrade.gov.au/international/buy # "' Espere mientras IE se carga mientras IE.Aplicación Ocupada.Espere la aplicación de bucle DateAdd("s", 1, Ahora).Espera (Ahora + Evaluación de tiempo("00:00:10")) 'Parte 1: Seleccione la lista desplegable y el evento de activación después de seleccionar una opción Set selectobj = IE.documento.getElementsByTagName ("seleccionar") m = IE.documento.getElementsByTagName("opción").Longitud-1 selectobj (0).selectedIndex = 1 selectobj (0).fireEvent ("onchange") 'Espere mientras IE se carga mientras IE.readyState < > 4 O IE.Busy = Aplicación verdadera.Espere la aplicación de bucle DateAdd("s", 1, Ahora).Espera (Ahora + Evaluación de tiempo("00:00:10")) Final Sub
Esta parte del código puede dirigirlo a la página web que se muestra en la Figura 2.1 después de que Excel haga clic en el primer nombre. Los nombres de todas las empresas se incluyen en el elemento con el nombre de clase «Name». Searchobj es una colección y searchobj(i)puede devolver el objeto (i+1). Por ejemplo, searchobj(1).Haga clic en puede permitirle visitar la página web de RIDLEY CORPORATION (Melbourne).
| Código fuente | |
'Parte 2-Seleccione el conjunto de nombres de la empresa searchobj = IE . documento.getElementsByClassName ("Nombre") objetivo de búsqueda (0).Haga clic en ' Esperar mientras IE se carga mientras IE.readyState < > 4 O IE.Bucle Busy = True DoEvents
Finalmente, aquí está todo el código que puede mostrarle el proceso de abrir IE, navegar por páginas web y extraer datos. Los datos extraídos serán los mismos que en la Figura 2.4.
| Código fuente | |
Sub Retrieve () ' Create InternetExplorerSet IE = CreateObject("InternetExplorer.Aplicación") ' Veamos la ventana del navegador.Visible = True ' Abre la página web.Navegue "http://www.austrade.gov.au/international/buy # "' Espere mientras IE se carga mientras IE.Aplicación Ocupada.Espere la aplicación de bucle DateAdd("s", 1, Ahora).Espera (Ahora + Evaluación de tiempo("00:00:10")) 'Parte 1: Seleccione la lista desplegable y el evento de activación después de seleccionar una opción Set selectobj = IE.documento.getElementsByTagName ("seleccionar") m = IE.documento.getElementsByTagName("opción").Longitud-1 selectobj (0).selectedIndex = 1 selectobj (0).fireEvent ("onchange") 'Espere mientras IE se carga mientras IE.readyState < > 4 O IE.Busy = Aplicación verdadera.Espere la aplicación de bucle DateAdd("s", 1, Ahora).Espera (Ahora + Evaluación de tiempo("00:00:10")) 'Parte 2-Seleccione el conjunto de nombres de la empresa searchobj = IE . documento.getElementsByClassName ("Nombre") objetivo de búsqueda (0).Haga clic en ' Esperar mientras IE se carga mientras IE.readyState < > 4 O IE.Busy = True DoEvents Loop 'Parte 3-Recuperar el nombre de la empresa, la dirección de correo electrónico & información de contacto Set contactobj = IE.documento.getElementsByClassName ("bloque de detalles de contacto oscuro") htext = contactobj (0).innerHTML If InStr (htext, "<p>Nombre de la empresa: ") Entonces ThisWorkbook.Hojas de trabajo(1).Cells(1, 1) = Split (Split (htext, " < p> Nombre de la empresa: ")(1), "<br") (0) End If If InStr (htext, "mailto:") Entonces ThisWorkbook.Hojas de trabajo(1).Cells(2, 1) = Split (Split (htext, " mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, "<p>Name: ") Entonces ThisWorkbook.Hojas de trabajo(1).Las células(3, 1) = Split(Split(htext, "<p>Nombre: ")(1), "<br")(0) End If ThisWorkbook.Hojas de trabajo(1).Celdas (4, 1) = IE.LocationURL ' Añadir hipervínculo a este libro de trabajo.Hojas de trabajo(1).Hipervínculo.Agregar ThisWorkbook.Hojas de trabajo(1).Celdas (4, 1), este libro de trabajo.Hojas de trabajo(1).Celdas (4, 1)
De hecho, lo que necesitamos hacer es extraer la información de contacto de todas las empresas de todas las industrias, por lo tanto, necesitamos usar for loop para completar esta tarea. A continuación se muestra el código completo. Y también puede encontrar el código en Recuperar información de contacto de todas las empresas.xlsm, que puedes descargar al final de este artículo.
| Código fuente | |
Sub Retrieve () Para idex = 2 A 18 'Create InternetExplorerSet IE = CreateObject ("InternetExplorer.Aplicación") ' Veamos la ventana del navegador.Visible = False ' Abre la página web.Navegue "http://www.austrade.gov.au/international/buy # "' Espere mientras IE se carga mientras IE.Aplicación Ocupada.Espere la aplicación de bucle DateAdd("s", 1, Ahora).Espera (Ahora + Evaluación de tiempo("00:00:10")) idexn = idex-1 ' Parte 1-Seleccionar Conjunto desplegable selectobj = IE.documento.getElementsByTagName ("seleccionar") m = IE.documento.getElementsByTagName("opción").Longitud-1 selectobj (0).selectedIndex = idexn selectobj (0).fireEvent ("onchange") 'Espere mientras IE se carga mientras IE.readyState < > 4 O IE.Busy = Aplicación verdadera.Espere la aplicación de bucle DateAdd("s", 1, Ahora).Esperar (Now + TimeValue("00:00:10")) wurl = IE.LocationURL tot = IE . documento.getElementsByClassName ("SearchTotal") (0).innerHTML pg = Int (tot / 25) + 1 Max = (tot Mod 25) - 1 ' Parte 2 - Seleccionar Clase = "Nombre" a = 2 Para j = 1 A pg Si j = 1 Entonces IE.Navegar (wurl) De lo contrario, es decir.Navegue (wurl & "& pg = " & j) Termine Si Lo Hace Mientras es DECIR.Aplicación Ocupada.Wait DateAdd ("s", 1, Now) Loop Si j <> pg Entonces Para i = 1 A 24 Set searchobj = IE.documento.getElementsByClassName ("Nombre") objeto de búsqueda (i).Haga clic en ' Esperar mientras IE se carga mientras IE.readyState < > 4 O IE.Busy = True DoEvents Loop 'Parte 3-Recuperar el nombre de la empresa, la dirección de correo electrónico & información de contacto Set contactobj = IE.documento.getElementsByClassName ("bloque de detalles de contacto oscuro") htext = contactobj (0).innerHTML ThisWorkbook.Hojas de trabajo(idex).Cells (a, 1) = j Este libro de trabajo.Hojas de trabajo(idex).Cells (a, 2) = a-1 Si InStr(htext, "<p>Nombre de la empresa:") Entonces ThisWorkbook.Hojas de trabajo(idex).Cells (a, 3) = Split (Split (htext, " < p> Nombre de la empresa: ")(1), "<br") (0) End If If InStr (htext, "mailto:") Entonces ThisWorkbook.Hojas de trabajo(idex).Cells (a, 4) = Split (Split (htext, " mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, "<p>Name: ") Entonces ThisWorkbook.Hojas de trabajo(idex).Las células(a, 5) = Split(Split(htext, "<p>Nombre: ")(1), "<br")(0) End If ThisWorkbook.Hojas de trabajo(idex).Celdas ( a, 6) = IE.Ubicación ES decir.Goback Hacer Mientras IE.Aplicación Ocupada.Wait DateAdd ("s", 1, Now) Bucle a = a + 1 Siguiente i Else Para i = 0 Al Conjunto Máximo searchobj = IE.documento.getElementsByClassName ("Nombre") objeto de búsqueda (i).Haga clic en ' Esperar mientras IE se carga mientras IE.readyState < > 4 O IE.Busy = True DoEvents Loop 'Parte 3-Recuperar el nombre de la empresa, la dirección de correo electrónico & información de contacto Set contactobj = IE.documento.getElementsByClassName ("bloque de detalles de contacto oscuro") htext = contactobj (0).innerHTML ThisWorkbook.Hojas de trabajo(idex).Cells (a, 1) = j Este libro de trabajo.Hojas de trabajo(idex).Cells (a, 2) = a-1 Si InStr(htext, "<p>Nombre de la empresa:") Entonces ThisWorkbook.Hojas de trabajo(idex).Cells (a, 3) = Split (Split (htext, " < p> Nombre de la empresa: ")(1), "<br") (0) End If If InStr (htext, "mailto:") Entonces ThisWorkbook.Hojas de trabajo(idex).Cells (a, 4) = Split (Split (htext, " mailto:") (1), Chr(34) & ">")(0) End If If InStr (htext, "<p>Name: ") Entonces ThisWorkbook.Hojas de trabajo(idex).Las células(a, 5) = Split(Split(htext, "<p>Nombre: ")(1), "<br")(0) End If ThisWorkbook.Hojas de trabajo(idex).Celdas ( a, 6) = IE.Ubicación Visita este libro de trabajo.Hojas de trabajo(idex).Hipervínculo.Agregar ThisWorkbook.Hojas de trabajo(idex).Celdas (a, 6), este libro de trabajo.Hojas de trabajo(idex).Células(a, 6), ES decir.Goback Hacer Mientras IE.Aplicación Ocupada.Wait DateAdd ("s", 1, Ahora) Bucle a = a + 1 Lo siguiente Que Termino Si Es este Libro de trabajo.Guardar Siguiente Conjunto j IE = Nada Conjunto contactobj = Nada Siguiente idex Fin Sub
El único punto que necesito explicar se ilustra en la Figura 3.2. Una web puede enumerar solo hasta 25 empresas. Cuando el número total de empresas sea superior a 25, habrá más de una página. La figura 3.2 muestra que hay una regla para obtener la dirección de las páginas después de la primera página. Es el resultado de la dirección de concatenación de la primera página, «& pg= » y el número de página real. Y para todas las páginas anteriores a la última página, el número total de objeto es 25. «IE.documento.getElementsByClassName («SearchTotal») (0).innerHTML » puede devolver el número total de empresas dentro de una industria. En nuestro caso, será 651. «Int (tot / 25) + 1» puede obtener el número total de páginas. Y «Max = (tot Mod 25) – 1» puede devolver el número máximo de empresas en la última página. Me detendré aquí y te dejaré para que averigües cómo aplicar esta idea al código. Es un enfoque mucho mejor para comprender el código. Puedes dejar un comentario si tienes preguntas.
Figura 3.2
Aquí le muestra parte del excel final. La información de contacto de todas las empresas de una industria se reúne en una hoja de trabajo.
Figura 3.3
- ¿Cómo extraer datos del sitio web a Excel automáticamente?
- Importe Datos (Oraciones, Párrafos, Tablas, Comentarios) de Word a Excel
- 6 Mejores Libros de Programación VBA de Excel (Para Principiantes & Usuarios Avanzados)
- Aprenda Programación VBA de Excel & Macros (Tutorial Gratuito – Paso a Paso)
- Consejos de Codificación VBA de Excel
- Lo que Puede Hacer con VBA
- Introducción a las Macros de VBA
Descargue el archivo de trabajo
Descargue el archivo de trabajo desde el enlace a continuación.
Extraer datos de la Web a Excel.rar