{"id":24062,"date":"2022-05-25T11:53:43","date_gmt":"2022-05-25T10:53:43","guid":{"rendered":"https:\/\/ekiwi-blog.de\/24062\/vba-webseite-auslesen\/"},"modified":"2022-12-17T20:06:22","modified_gmt":"2022-12-17T19:06:22","slug":"vba-get-data-from-website","status":"publish","type":"post","link":"https:\/\/ekiwi-blog.de\/en\/24062\/vba-get-data-from-website\/","title":{"rendered":"VBA get data from website"},"content":{"rendered":"<p><abbr title=\"Visual Basic for Application\">VBA<\/abbr> is a very useful helper in the Microsoft Office world to automate everyday recurring processes. Such a process can be that you need to read information from a certain web page and transfer it to Excel, Word or another file, for example. Of course, this can also be done by copy and paste. But it would be much better if you could read the source code of a web page with one click and then process it further.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of content<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/ekiwi-blog.de\/en\/24062\/vba-get-data-from-website\/#Fetching_HTML_code_from_web_page\" >Fetching HTML code from web page<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/ekiwi-blog.de\/en\/24062\/vba-get-data-from-website\/#VBA_code_for_scraping_URL\" >VBA code for scraping URL<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Fetching_HTML_code_from_web_page\"><\/span>Fetching HTML code from web page<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The following image shows an <a href=\"https:\/\/ekiwi-blog.de\/en\/53044\/excel-shortcut-change-spreadsheet\/\" title=\"Excel Shortcut Change Spreadsheet\">Excel spreadsheet<\/a> as an example, in which an Internet address\/URL is stored in the first cell. In addition, a &#8220;<em>Read web page<\/em>&#8221; button has been added to the Excel table. This is done via the <em>&#8220;Developer tools&#8221; \u27a5 &#8220;Insert&#8221;<\/em> menu.<\/p>\n<p>Now, when you click on the button, the web page\/URL that is entered in the first cell is to be <a href=\"https:\/\/ekiwi-blog.de\/en\/49271\/vba-read-out-folder-and-directory-names-and-list-in-excel\/\">read out<\/a> for further use.<\/p>\n<figure id=\"attachment_24058\" aria-describedby=\"caption-attachment-24058\" style=\"width: 899px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-24058\" src=\"http:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/vba-read-website-sourc-code-1.jpg\" alt=\"Screenshot VBA-Editor Sub to read a website\" width=\"899\" height=\"741\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/vba-read-website-sourc-code-1.jpg 899w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/vba-read-website-sourc-code-1-300x247.jpg 300w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/vba-read-website-sourc-code-1-768x633.jpg 768w\" sizes=\"auto, (max-width: 899px) 100vw, 899px\" \/><figcaption id=\"caption-attachment-24058\" class=\"wp-caption-text\"><a title=\"VBA read PDF\" href=\"https:\/\/ekiwi-blog.de\/en\/24960\/vba-read-pdf\/\">VBA code for reading<\/a> out a web page (the source code\/HTML code is read out)<\/figcaption><\/figure>\n<p>By right-clicking on the button and selecting &#8220;Assign Macro&#8230;&#8221;, you then assign the <a href=\"https:\/\/ekiwi-blog.de\/en\/17115\/vba-function-to-open-a-form-userform-in-microsoft-office\/\">VBA function<\/a> to be executed to the button. In this case the function is called <em>&#8220;BtnReadWebsite_Click&#8221;<\/em>.<\/p>\n<figure id=\"attachment_24056\" aria-describedby=\"caption-attachment-24056\" style=\"width: 330px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-24056 size-full\" src=\"http:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/makro-zuweisen.jpg\" alt=\"Screenshot Excel assign macro\" width=\"330\" height=\"282\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/makro-zuweisen.jpg 330w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/makro-zuweisen-300x256.jpg 300w\" sizes=\"auto, (max-width: 330px) 100vw, 330px\" \/><figcaption id=\"caption-attachment-24056\" class=\"wp-caption-text\">right click and assign macro<\/figcaption><\/figure>\n<p>In the dialog that opens, you can select the SUB-method that reads the <a title=\"HTML\/CSS-Code for indent text or tab text\" href=\"https:\/\/ekiwi.de\/en\/index.php\/4697\/html-indent-or-tab-text\/\" target=\"_blank\" rel=\"noopener\">HTML code<\/a> of the web page.<\/p>\n<figure id=\"attachment_24059\" aria-describedby=\"caption-attachment-24059\" style=\"width: 389px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-24060\" src=\"http:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/makro-zuweisen-2.jpg\" alt=\"Screenshot Excel Makro zuweisen zum Auslesen des Quelltexts einer Webseite\" width=\"389\" height=\"363\" srcset=\"https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/makro-zuweisen-2.jpg 389w, https:\/\/ekiwi-blog.de\/wp-content\/uploads\/2022\/05\/makro-zuweisen-2-300x280.jpg 300w\" sizes=\"auto, (max-width: 389px) 100vw, 389px\" \/><figcaption id=\"caption-attachment-24059\" class=\"wp-caption-text\">Makro zuweisen zum Auslesen des Quelltexts einer Webseite<\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"VBA_code_for_scraping_URL\"><\/span>VBA code for scraping URL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To read the content of a web page, use the following VBA code in the VBA editor:<\/p>\n<pre><code><span style=\"color: #0000ff;\">Sub<\/span> BtnReadWebsite_Click()\r\n    <span style=\"color: #0000ff;\">Dim<\/span> url <span style=\"color: #0000ff;\">As String<\/span>\r\n    url = Cells(1, 1).Value\r\n    URL_Load url\r\n<span style=\"color: #0000ff;\">End Sub<\/span>\r\n\r\n\r\n<span style=\"color: #0000ff;\">Private Sub<\/span> URL_Load(<span style=\"color: #0000ff;\">ByVal<\/span> sURL <span style=\"color: #0000ff;\">As String<\/span>)\r\n    <span style=\"color: #0000ff;\">Dim<\/span> appInternetExplorer <span style=\"color: #0000ff;\">As Object<\/span>\r\n    <span style=\"color: #0000ff;\">Dim<\/span> htmlTxt <span style=\"color: #0000ff;\">As String<\/span>\r\n    <span style=\"color: #0000ff;\">Set<\/span> appInternetExplorer = CreateObject(\"InternetExplorer.Application\")\r\n    appInternetExplorer.navigate sURL\r\n    <span style=\"color: #0000ff;\">Do<\/span>: <span style=\"color: #0000ff;\">Loop Until<\/span> appInternetExplorer.Busy = <span style=\"color: #0000ff;\">False<\/span>\r\n    <span style=\"color: #0000ff;\">Do<\/span>: <span style=\"color: #0000ff;\">Loop Until<\/span> appInternetExplorer.Busy = <span style=\"color: #0000ff;\">False<\/span>\r\n    htmlTxt = appInternetExplorer.document.DocumentElement.outerHTML\r\n    <span style=\"color: #0000ff;\">Debug.Print<\/span> htmlTxt\r\n    <span style=\"color: #0000ff;\">Set<\/span> appInternetExplorer = <span style=\"color: #0000ff;\">Nothing<\/span>\r\n    <span style=\"color: #0000ff;\">Close<\/span>\r\n    <span style=\"color: #008000;\">'Do something here with html source code: parse, output, save,...<\/span>\r\n    MsgBox \"The text was read out!!\"\r\n<span style=\"color: #0000ff;\">End Sub<\/span><\/code><\/pre>\n<p>In the first Sub-routine the internet address is read <a title=\"Excel search from bottom to top (backwards search)\" href=\"https:\/\/ekiwi-blog.de\/en\/25078\/excel-find-search-from-bottom-to-top-backwards-search\/\">from the Excel<\/a> cell and passed to the URL_Load function.<\/p>\n<p>The <em>URL_Load<\/em> function then creates an Internet Explorer instance that loads the web page. The &#8220;<em>Loop<\/em>&#8221; functions are used to bridge the time needed for loading the web page.<\/p>\n<p>Via <em>appInternetExplorer.document.DocumentElement.outerHTML<\/em> the assignment of the read HTML code takes place.<\/p>\n<p>Now you can use the string variable &#8220;<em>htmlText<\/em>&#8221; to process the text, e.g on position of the <a title=\"How to comment or uncomment multiple mines VBA code in one step\" href=\"https:\/\/ekiwi-blog.de\/en\/17245\/vba-comment-multiple-lines\/\">comment<\/a>. In the example here the text is printed via <em>Debug.Print<\/em> in the direct window of the VBA editor. But you can also add the text to the clipboard or paste it into a concrete Excel cell.<\/p>\n<p>In most cases it will be necessary to parse the source code of the web page, because you need only a specific information from the web page.<\/p>\n\n<div class=\"aawp\">\n\n            \n            \n<div class=\"aawp-product aawp-product--horizontal aawp-product--bestseller aawp-product--ribbon\"  data-aawp-product-asin=\"3367105732\" data-aawp-product-id=\"112260\" data-aawp-tracking-id=\"ekiwide0b-21\" data-aawp-product-title=\"VBA mit Excel  Das umfassende Handbuch f\u00fcr Einsteiger und fortgeschrittene Anwender Aktuell zu Excel 2024 und Microsoft 365 \u2013 Ausgabe 2025\">\n\n    <span class=\"aawp-product__ribbon aawp-product__ribbon--bestseller\">Bestseller No. 1<\/span>\n    <div class=\"aawp-product__thumb\">\n        <a class=\"aawp-product__image-link\"\n           href=\"https:\/\/www.amazon.de\/dp\/3367105732?tag=ekiwide0b-21&linkCode=ogi&th=1&psc=1&keywords=vba%20excel\" title=\"VBA mit Excel: Das umfassende Handbuch f\u00fcr...\" rel=\"nofollow noopener sponsored\" target=\"_blank\">\n            <img decoding=\"async\" class=\"aawp-product__image\" src=\"https:\/\/m.media-amazon.com\/images\/I\/51EVPKCMSSL._SL160_.jpg\" alt=\"VBA mit Excel: Das umfassende Handbuch f\u00fcr...\"  \/>\n        <\/a>\n\n            <\/div>\n\n    <div class=\"aawp-product__content\">\n        <a class=\"aawp-product__title\" href=\"https:\/\/www.amazon.de\/dp\/3367105732?tag=ekiwide0b-21&linkCode=ogi&th=1&psc=1&keywords=vba%20excel\" title=\"VBA mit Excel: Das umfassende Handbuch f\u00fcr...\" rel=\"nofollow noopener sponsored\" target=\"_blank\">\n            VBA mit Excel: Das umfassende Handbuch f\u00fcr...        <\/a>\n        <div class=\"aawp-product__description\">\n                    <\/div>\n    <\/div>\n\n    <div class=\"aawp-product__footer\">\n\n        <div class=\"aawp-product__pricing\">\n            \n                            <span class=\"aawp-product__price aawp-product__price--current\">49,90 EUR<\/span>\n            \n            <a href=\"https:\/\/www.amazon.de\/gp\/prime\/?tag=ekiwide0b-21\" title=\"Amazon Prime\" rel=\"nofollow noopener sponsored\" target=\"_blank\" class=\"aawp-check-prime\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ekiwi-blog.de\/wp-content\/plugins\/aawp\/assets\/img\/icon-check-prime.svg\" height=\"16\" width=\"55\" alt=\"Amazon Prime\" \/><\/a>        <\/div>\n\n                <a class=\"aawp-button aawp-button--buy aawp-button--icon aawp-button--icon-black\" href=\"https:\/\/www.amazon.de\/dp\/3367105732?tag=ekiwide0b-21&#038;linkCode=ogi&#038;th=1&#038;psc=1&#038;keywords=vba%20excel\" title=\"Buy on Amazon\" target=\"_blank\" rel=\"nofollow noopener sponsored\">Buy on Amazon<\/a>\n            <\/div>\n\n<\/div>\n\n            \n            \n<div class=\"aawp-product aawp-product--horizontal aawp-product--bestseller aawp-product--ribbon\"  data-aawp-product-asin=\"3832803033\" data-aawp-product-id=\"111673\" data-aawp-tracking-id=\"ekiwide0b-21\" data-aawp-product-title=\"VBA mit Excel - Der leichte Einstieg  Vom ersten Makro zur eigenen Eingabemaske - F\u00fcr Excel 2010 bis 2021 365  Die anschauliche Anleitung f\u00fcr .. Eingabemaske - F\u00fcr Excel 2010 bis 2019\">\n\n    <span class=\"aawp-product__ribbon aawp-product__ribbon--bestseller\">Bestseller No. 2<\/span>\n    <div class=\"aawp-product__thumb\">\n        <a class=\"aawp-product__image-link\"\n           href=\"https:\/\/www.amazon.de\/dp\/3832803033?tag=ekiwide0b-21&linkCode=ogi&th=1&psc=1&keywords=vba%20excel\" title=\"VBA mit Excel - Der leichte Einstieg: Vom ersten...\" rel=\"nofollow noopener sponsored\" target=\"_blank\">\n            <img decoding=\"async\" class=\"aawp-product__image\" src=\"https:\/\/m.media-amazon.com\/images\/I\/51Ssb4JGplL._SL160_.jpg\" alt=\"VBA mit Excel - Der leichte Einstieg: Vom ersten...\"  \/>\n        <\/a>\n\n            <\/div>\n\n    <div class=\"aawp-product__content\">\n        <a class=\"aawp-product__title\" href=\"https:\/\/www.amazon.de\/dp\/3832803033?tag=ekiwide0b-21&linkCode=ogi&th=1&psc=1&keywords=vba%20excel\" title=\"VBA mit Excel - Der leichte Einstieg: Vom ersten...\" rel=\"nofollow noopener sponsored\" target=\"_blank\">\n            VBA mit Excel - Der leichte Einstieg: Vom ersten...        <\/a>\n        <div class=\"aawp-product__description\">\n                    <\/div>\n    <\/div>\n\n    <div class=\"aawp-product__footer\">\n\n        <div class=\"aawp-product__pricing\">\n            \n                            <span class=\"aawp-product__price aawp-product__price--current\">19,90 EUR<\/span>\n            \n            <a href=\"https:\/\/www.amazon.de\/gp\/prime\/?tag=ekiwide0b-21\" title=\"Amazon Prime\" rel=\"nofollow noopener sponsored\" target=\"_blank\" class=\"aawp-check-prime\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/ekiwi-blog.de\/wp-content\/plugins\/aawp\/assets\/img\/icon-check-prime.svg\" height=\"16\" width=\"55\" alt=\"Amazon Prime\" \/><\/a>        <\/div>\n\n                <a class=\"aawp-button aawp-button--buy aawp-button--icon aawp-button--icon-black\" href=\"https:\/\/www.amazon.de\/dp\/3832803033?tag=ekiwide0b-21&#038;linkCode=ogi&#038;th=1&#038;psc=1&#038;keywords=vba%20excel\" title=\"Buy on Amazon\" target=\"_blank\" rel=\"nofollow noopener sponsored\">Buy on Amazon<\/a>\n            <\/div>\n\n<\/div>\n\n    \n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>VBA is a very useful helper in the Microsoft Office world to automate everyday recurring processes. Such a process can<\/p>\n","protected":false},"author":2,"featured_media":13630,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1555],"tags":[2175,2165,2166,1704,2167,2168,2169,2172,1705,1706,2173,2174],"class_list":["post-24062","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-software-en","tag-homepage-en","tag-html-en","tag-html-code-en","tag-microsoft-office-en","tag-mircosoft-en","tag-office-en","tag-office-365-en","tag-source-code","tag-vba-en","tag-visual-basic-for-application-en","tag-web-page","tag-website-en"],"_links":{"self":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/24062","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/comments?post=24062"}],"version-history":[{"count":0,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/posts\/24062\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media\/13630"}],"wp:attachment":[{"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/media?parent=24062"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/categories?post=24062"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ekiwi-blog.de\/en\/wp-json\/wp\/v2\/tags?post=24062"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}