{"id":2064,"date":"2016-12-04T21:30:18","date_gmt":"2016-12-04T20:30:18","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=2064"},"modified":"2016-12-04T21:30:18","modified_gmt":"2016-12-04T20:30:18","slug":"excel-und-vba-durchlaufen-aller-worksheetstabellen-in-einem-workbookeiner-arbeitsmappe-und-exportieren-in-textdatei","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=2064","title":{"rendered":"Excel und VBA: Durchlaufen aller Worksheets\/Tabellen in einem Workbook\/einer Arbeitsmappe und exportieren in Textdatei"},"content":{"rendered":"<h1>Problem<\/h1>\n<p>Es sollen alle Tabellen einer Excelmappe durchlaufen und in eine Textdatei geschrieben werden<\/p>\n<h1>L\u00f6sung<\/h1>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\nSub Schaltfl\u00e4che1_Klicken()\r\n    &#039; Erzeugt eine Textdatei\r\n    On Error Resume Next\r\n    \r\n    Dim i, j As Integer\r\n    Dim letzteZeile As Integer\r\n    Dim workbookName As String\r\n    Dim artikelname As String\r\n    Dim EANNummer As String\r\n    Dim UmsatzVKBrutto As String\r\n    Dim Absatz As String\r\n    Dim VKPreisSap As String\r\n    Dim UST As String\r\n    \r\n    Dim csvZeile As String\r\n    \r\n    Open &quot;d:\\daten\\liste.txt&quot; For Output As #1\r\n    \r\n    For i = 2 To ThisWorkbook.Sheets.Count - 1\r\n        workbookName = ThisWorkbook.Sheets(i).Name\r\n        &#039; Finde die letzte Zeile\r\n        letzteZeile = Worksheets(workbookName).Cells.Find(&quot;*&quot;, &#x5B;A1], , , xlByRows, xlPrevious).Row + 1\r\n        \r\n        For j = 1 To letzteZeile\r\n            If Trim(ThisWorkbook.Sheets(i).Cells(j, 1)) &lt;&gt; &quot;&quot; Then\r\n                artikelname = ThisWorkbook.Sheets(i).Cells(j, 1)\r\n                EANNummer = ThisWorkbook.Sheets(i).Cells(j, 2)\r\n                UmsatzVKBrutto = ThisWorkbook.Sheets(i).Cells(j, 3)\r\n                Absatz = ThisWorkbook.Sheets(i).Cells(j, 4)\r\n                \r\n                If IsError(ThisWorkbook.Sheets(i).Cells(j, 5).Value) = True Then\r\n                    VKPreisSap = &quot;#nv&quot;\r\n                Else\r\n                    VKPreisSap = CStr(ThisWorkbook.Sheets(i).Cells(j, 5))\r\n                End If\r\n                \r\n                If IsError(ThisWorkbook.Sheets(i).Cells(j, 6).Value) = True Then\r\n                    UST = &quot;#nv&quot;\r\n                Else\r\n                    UST = CStr(ThisWorkbook.Sheets(i).Cells(j, 6))\r\n                End If\r\n                \r\n                csvZeile = workbookName &amp; &quot;;&quot; &amp; artikelname &amp; &quot;;&quot; &amp; EANNummer &amp; _\r\n                &quot;;&quot; &amp; UmsatzVKBrutto &amp; &quot;;&quot; &amp; Absatz &amp; &quot;;&quot; &amp; VKPreisSap &amp; &quot;;&quot; &amp; UST\r\n                Print #1, csvZeile\r\n                \r\n            End If\r\n        Next j\r\n    Next i\r\n    \r\n    Close #1\r\n\r\nEnd Sub\r\n<\/pre>\n<iframe src=\"http:\/\/www.facebook.com\/plugins\/like.php?href=https%3A%2F%2Fwww.capri-soft.de%2Fblog%2F%3Fp%3D2064&amp;layout=standard&amp;show_faces=true&amp;width=450&amp;action=like&amp;colorscheme=light\" scrolling=\"no\" frameborder=\"0\" allowTransparency=\"true\" style=\"border:none; overflow:hidden; width:450px;margin-top:5px;\"><\/iframe>","protected":false},"excerpt":{"rendered":"<p>Problem Es sollen alle Tabellen einer Excelmappe durchlaufen und in eine Textdatei geschrieben werden L\u00f6sung Sub Schaltfl\u00e4che1_Klicken() &#039; Erzeugt eine Textdatei On Error Resume Next Dim i, j As Integer Dim letzteZeile As Integer Dim workbookName As String Dim artikelname As String Dim EANNummer As String Dim UmsatzVKBrutto As String Dim Absatz As String Dim &hellip; <a href=\"https:\/\/www.capri-soft.de\/blog\/?p=2064\" class=\"more-link\"><span class=\"screen-reader-text\">Excel und VBA: Durchlaufen aller Worksheets\/Tabellen in einem Workbook\/einer Arbeitsmappe und exportieren in Textdatei<\/span> weiterlesen <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[12],"tags":[],"class_list":["post-2064","post","type-post","status-publish","format-standard","hentry","category-vba"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4yGeN-xi","jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2064","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2064"}],"version-history":[{"count":1,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2064\/revisions"}],"predecessor-version":[{"id":2065,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/2064\/revisions\/2065"}],"wp:attachment":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2064"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2064"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2064"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}