{"id":789,"date":"2012-09-28T13:01:06","date_gmt":"2012-09-28T11:01:06","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=789"},"modified":"2012-11-29T01:52:08","modified_gmt":"2012-11-29T00:52:08","slug":"vba-in-datei-schreiben-tsv-datei-erstellen-write-to-file-create-tsv-file","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=789","title":{"rendered":"VBA (Import Excel in Doors): In Unicode Datei schreiben (TSV Datei erstellen) \/ Write to unicode file (create TSV file)"},"content":{"rendered":"<h1>Aufgabe &#8211; Problem<\/h1>\n<p>Der Inhalt eines Excel-Sheets mit Unicode soll in eine Datei geschrieben werden (damit z.B. IBM Doors \u00fcber File -> Importieren -> Spreadsheet den Inhalt importieren kann). <\/p>\n<p>A unicode file should be written with the contents of an Excel Sheet using VBA<\/p>\n<h1>Vorraussetzung &#8211; Prerequirement<\/h1>\n<p>Im VBA Editor muss im Men\u00fc unter &#8222;Verweise&#8220; der Punkt &#8222;Microsoft Scripting Runtime&#8220; aktiviert werden.<br \/>\nIn the VBA Editor a reference should be added &#8222;Microsoft Scripting Runtime&#8220;<\/p>\n<h1>Ansatz &#8211; Approach<\/h1>\n<p>(Es wird VBA Code im Excel sheet hinterlegt was den Inhalt in eine TSV-Datei, die von Doors importiert werden kann, auslagert).<br \/>\nDas folgende Skript zeigt ein Iterieren \u00fcber ein komplettes Excel sheet und schreibt die ersten 3 Spalten in Tabulator-Separiert in eine Datei. Somit k\u00f6nnte man eine TSV-Datei generieren die z.B. von IBM Doors eingelesen werden kann.<\/p>\n<h1>L\u00f6sung &#8211; Solution<\/h1>\n<pre class=\"brush: jscript; title: ; notranslate\" title=\"\">\r\n    Dim letzteZeile As Integer\r\n    Dim i As Integer\r\n    \r\n    Dim ID,Col1,Col2 As String\r\n    \r\n    Dim fso As New FileSystemObject\r\n\r\n    &#039; Declare a TextStream.\r\n    Dim stream As TextStream\r\n\r\n    &#039; Create a TextStream.\r\n    Set stream = fso.CreateTextFile(&quot;c:\\dev\\wingstext.csv&quot;, True, True)\r\n      \r\n    &#039; Finde die letzte Zeile\r\n    letzteZeile = Cells.Find(&quot;*&quot;, &#x5B;A1], , , xlByRows, xlPrevious).Row + 1\r\n               \r\n    For i = 1 To letzteZeile\r\n        ID = Tabelle1.Cells(i, 1)\r\n        Col1 = Tabelle1.Cells(i, 2)\r\n        Col2 = Tabelle1.Cells(i, 3)\r\n        \r\n        stream.WriteLine ID &amp; vbTab &amp; Col1 &amp; vbTab &amp; Col2\r\n\r\n    Next i\r\n    \r\n    &#039; Close the file.\r\n    stream.Close\r\n<\/pre>\n<iframe src=\"http:\/\/www.facebook.com\/plugins\/like.php?href=https%3A%2F%2Fwww.capri-soft.de%2Fblog%2F%3Fp%3D789&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>Aufgabe &#8211; Problem Der Inhalt eines Excel-Sheets mit Unicode soll in eine Datei geschrieben werden (damit z.B. IBM Doors \u00fcber File -> Importieren -> Spreadsheet den Inhalt importieren kann). A unicode file should be written with the contents of an Excel Sheet using VBA Vorraussetzung &#8211; Prerequirement Im VBA Editor muss im Men\u00fc unter &#8222;Verweise&#8220; &hellip; <a href=\"https:\/\/www.capri-soft.de\/blog\/?p=789\" class=\"more-link\"><span class=\"screen-reader-text\">VBA (Import Excel in Doors): In Unicode Datei schreiben (TSV Datei erstellen) \/ Write to unicode file (create TSV file)<\/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":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[19,12],"tags":[],"class_list":["post-789","post","type-post","status-publish","format-standard","hentry","category-ibm-doors","category-vba"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4yGeN-cJ","jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/789","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=789"}],"version-history":[{"count":8,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/789\/revisions"}],"predecessor-version":[{"id":791,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/789\/revisions\/791"}],"wp:attachment":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=789"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=789"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=789"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}