{"id":539,"date":"2019-02-20T18:55:26","date_gmt":"2019-02-20T18:55:26","guid":{"rendered":"http:\/\/www.vbastring.com\/blog\/?p=539"},"modified":"2019-02-20T18:56:14","modified_gmt":"2019-02-20T18:56:14","slug":"how-to-have-excel-vba-write-to-xml-file","status":"publish","type":"post","link":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/","title":{"rendered":"How To Have Excel VBA Write To XML File"},"content":{"rendered":"<p>This post is called &#8220;How To Have Excel VBA Write To XML File&#8221;, and is going to be an enhancement to my previous post:<\/p>\n<p><a href=\"http:\/\/www.vbastring.com\/blog\/how-to-create-an-excel-userform-to-add-edit-and-delete\/\" rel=\"noopener\" target=\"_blank\">http:\/\/www.vbastring.com\/blog\/how-to-create-an-excel-userform-to-add-edit-and-delete\/<\/a><\/p>\n<p>In this post we are going to add a button to our form where we can save the current contents of the worksheet to<br \/>\nan XML file.<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png?resize=300%2C241\" alt=\"\" width=\"300\" height=\"241\" class=\"alignleft size-medium wp-image-542\" \/><\/a><\/p>\n<p>Here is the code for the new button.  We are creating a text file and then writing the contents of each column to each tag:<\/p>\n<pre class=\"lang:vb decode:true \" >Private Sub btnXML_Click()\r\n    Dim lngRow As Long\r\n   \r\n    strpath = ActiveWorkbook.Path &amp; \"\\ContactList.xml\"\r\n    \r\n    Set fso = CreateObject(\"Scripting.FileSystemObject\")\r\n    \r\n    Dim objFile As Object\r\n    Set objFile = fso.CreateTextFile(strpath, True, True)\r\n    \r\n    objFile.Write \"&lt;?xml version='1.0'?&gt;\" &amp; vbCrLf\r\n    objFile.Write \"&lt;ContactList&gt;\" &amp; vbCrLf\r\n    \r\n    For lngRow = 2 To 2000\r\n        If Range(\"A\" &amp; lngRow) &lt;&gt; \"\" Then\r\n\r\n            objFile.Write \"&lt;CustomerID&gt;\" &amp; Range(\"A\" &amp; lngRow) &amp; \"&lt;\/CustomerID&gt;\" &amp; vbCrLf\r\n            objFile.Write \"&lt;CompanyName&gt;\" &amp; Range(\"B\" &amp; lngRow) &amp; \"&lt;\/CompanyName&gt;\" &amp; vbCrLf\r\n            objFile.Write \"&lt;ContactName&gt;\" &amp; Range(\"C\" &amp; lngRow) &amp; \"&lt;\/ContactName&gt;\" &amp; vbCrLf\r\n            objFile.Write \"&lt;ContactTitle&gt;\" &amp; Range(\"D\" &amp; lngRow) &amp; \"&lt;\/ContactTitle&gt;\" &amp; vbCrLf\r\n            objFile.Write \"&lt;Address&gt;\" &amp; Range(\"E\" &amp; lngRow) &amp; \"&lt;\/Address&gt;\" &amp; vbCrLf\r\n            \r\n        End If\r\n        \r\n    Next\r\n    \r\n    objFile.Write \"&lt;\/ContactList&gt;\" &amp; vbCrLf &amp; vbCrLf\r\n    \r\nEnd Sub\r\n<\/pre>\n<p><code><\/p>\n<p>When we open ContactList.xml in a XML Editor or the Microsoft Edge browser (because it renders XML good, and should show it, if you have done it correctly) we should see the file contents looking like this:<\/p>\n<p><a href=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File2.png\"><img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File2-300x254.png?resize=300%2C254\" alt=\"\" width=\"300\" height=\"254\" class=\"alignleft size-medium wp-image-543\" \/><\/a><\/p>\n<p>Now if we need to send this to someone to load into their database or otherwise, our data is structured.<\/p>\n<p>Let me know if you have any questions<\/p>\n<p>****************************************************<\/p>\n<table border=\"1\" width=\"100%\">\n<tr>\n<td align=\"middle\">\n<div class=\"AW-Form-1094354588\"><\/div>\n<p><script type=\"text\/javascript\">(function(d, s, id) {\n    var js, fjs = d.getElementsByTagName(s)[0];\n    if (d.getElementById(id)) return;\n    js = d.createElement(s); js.id = id;\n    js.src = \"\/\/forms.aweber.com\/form\/88\/1094354588.js\";\n    fjs.parentNode.insertBefore(js, fjs);\n    }(document, \"script\", \"aweber-wjs-kkrwj13ov\"));\n<\/script>\n<\/td>\n<\/tr>\n<\/table>\n","protected":false},"excerpt":{"rendered":"<p>This post is called &#8220;How To Have Excel VBA Write To XML File&#8221;, and is going to be an enhancement to my previous post: http:\/\/www.vbastring.com\/blog\/how-to-create-an-excel-userform-to-add-edit-and-delete\/ In this post we are going to add a button to our form where we can save the current contents of the worksheet to an XML file. Here is the [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","om_disable_all_campaigns":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[],"tags":[],"class_list":["post-539","post","type-post","status-publish","format-standard","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How To Have Excel VBA Write To XML File - My Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Have Excel VBA Write To XML File - My Blog\" \/>\n<meta property=\"og:description\" content=\"This post is called &#8220;How To Have Excel VBA Write To XML File&#8221;, and is going to be an enhancement to my previous post: http:\/\/www.vbastring.com\/blog\/how-to-create-an-excel-userform-to-add-edit-and-delete\/ In this post we are going to add a button to our form where we can save the current contents of the worksheet to an XML file. Here is the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/\" \/>\n<meta property=\"og:site_name\" content=\"My Blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-20T18:55:26+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-02-20T18:56:14+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/\"},\"author\":{\"name\":\"admin\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"headline\":\"How To Have Excel VBA Write To XML File\",\"datePublished\":\"2019-02-20T18:55:26+00:00\",\"dateModified\":\"2019-02-20T18:56:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/\"},\"wordCount\":157,\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png\",\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/\",\"name\":\"How To Have Excel VBA Write To XML File - My Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/#primaryimage\"},\"thumbnailUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png\",\"datePublished\":\"2019-02-20T18:55:26+00:00\",\"dateModified\":\"2019-02-20T18:56:14+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/#primaryimage\",\"url\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png\",\"contentUrl\":\"http:\\\/\\\/www.vbastring.com\\\/blog\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/2019\\\/02\\\/20\\\/how-to-have-excel-vba-write-to-xml-file\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Have Excel VBA Write To XML File\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/\",\"name\":\"My Blog\",\"description\":\"My WordPress Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/#\\\/schema\\\/person\\\/e1de0b30e98940381697872449c341f5\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g\",\"caption\":\"admin\"},\"sameAs\":[\"https:\\\/\\\/vbastring.com\\\/blog\"],\"url\":\"https:\\\/\\\/vbastring.com\\\/blog\\\/author\\\/admin\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Have Excel VBA Write To XML File - My Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/","og_locale":"en_US","og_type":"article","og_title":"How To Have Excel VBA Write To XML File - My Blog","og_description":"This post is called &#8220;How To Have Excel VBA Write To XML File&#8221;, and is going to be an enhancement to my previous post: http:\/\/www.vbastring.com\/blog\/how-to-create-an-excel-userform-to-add-edit-and-delete\/ In this post we are going to add a button to our form where we can save the current contents of the worksheet to an XML file. Here is the [&hellip;]","og_url":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/","og_site_name":"My Blog","article_published_time":"2019-02-20T18:55:26+00:00","article_modified_time":"2019-02-20T18:56:14+00:00","og_image":[{"url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png","type":"","width":"","height":""}],"author":"admin","twitter_card":"summary_large_image","twitter_misc":{"Written by":"admin","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/#article","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/"},"author":{"name":"admin","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"headline":"How To Have Excel VBA Write To XML File","datePublished":"2019-02-20T18:55:26+00:00","dateModified":"2019-02-20T18:56:14+00:00","mainEntityOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/"},"wordCount":157,"image":{"@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png","inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/","url":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/","name":"How To Have Excel VBA Write To XML File - My Blog","isPartOf":{"@id":"https:\/\/vbastring.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/#primaryimage"},"image":{"@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/#primaryimage"},"thumbnailUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png","datePublished":"2019-02-20T18:55:26+00:00","dateModified":"2019-02-20T18:56:14+00:00","author":{"@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5"},"breadcrumb":{"@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/#primaryimage","url":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png","contentUrl":"http:\/\/www.vbastring.com\/blog\/wp-content\/uploads\/2019\/02\/How_To_Have_Excel_VBA_Write_To_XML_File-300x241.png"},{"@type":"BreadcrumbList","@id":"https:\/\/vbastring.com\/blog\/2019\/02\/20\/how-to-have-excel-vba-write-to-xml-file\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vbastring.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How To Have Excel VBA Write To XML File"}]},{"@type":"WebSite","@id":"https:\/\/vbastring.com\/blog\/#website","url":"https:\/\/vbastring.com\/blog\/","name":"My Blog","description":"My WordPress Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vbastring.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/vbastring.com\/blog\/#\/schema\/person\/e1de0b30e98940381697872449c341f5","name":"admin","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b2feaa698a4bd91b409df1beb5ff6acc2d7842d4f78543d413ebd468bc0171af?s=96&d=mm&r=g","caption":"admin"},"sameAs":["https:\/\/vbastring.com\/blog"],"url":"https:\/\/vbastring.com\/blog\/author\/admin\/"}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/539","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/comments?post=539"}],"version-history":[{"count":5,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/539\/revisions"}],"predecessor-version":[{"id":550,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/posts\/539\/revisions\/550"}],"wp:attachment":[{"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/media?parent=539"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/categories?post=539"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vbastring.com\/blog\/wp-json\/wp\/v2\/tags?post=539"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}