{"id":2557,"date":"2013-07-16T05:21:16","date_gmt":"2013-07-15T19:21:16","guid":{"rendered":"http:\/\/www.rjmprogramming.com.au\/wordpress\/?p=2557"},"modified":"2013-07-16T05:21:16","modified_gmt":"2013-07-15T19:21:16","slug":"microsoft-excel-vba-for-csv-primer-tutorial","status":"publish","type":"post","link":"https:\/\/www.rjmprogramming.com.au\/ITblog\/microsoft-excel-vba-for-csv-primer-tutorial\/","title":{"rendered":"Microsoft Excel VBA for CSV Primer Tutorial"},"content":{"rendered":"<div style=\"width: 230px\" class=\"wp-caption alignnone\"><a target=_blank href=\"http:\/\/www.rjmprogramming.com.au\/Microsoft\/Excel\/\"><img decoding=\"async\" style=\"float:left; border: 15px solid pink;\" alt=\"Microsoft Excel VBA for CSV Primer Tutorial\"\" src=\"http:\/\/www.rjmprogramming.com.au\/Microsoft\/Excel\/Excel_VBA-22of.jpg\" title=\"Microsoft Excel VBA for CSV Primer Tutorial\" \/><\/a><p class=\"wp-caption-text\">Microsoft Excel VBA for CSV Primer Tutorial<\/p><\/div>\n<p>There are a few different and varied concepts touched on in this Microsoft Excel VBA for CSV Primer Tutorial.<\/p>\n<ul>\n<li>The direct relationship Microsoft Excel <a target=_blank title='Spreadsheet information from Wikipedia ... spreadsheets go back to the earliest days of Personal Computers as the first big \"killer\" application idea' href='https:\/\/en.wikipedia.org\/wiki\/Spreadsheet'>spreadsheet<\/a> application has with <a target=_blank title='CSV information from Wikipedia' href='https:\/\/en.wikipedia.org\/wiki\/Comma-separated_values'>CSV<\/a> (comma separated values) files in terms of input file(s) (as shown here) and output files (not shown here, but Excel can output CSV files).<\/li>\n<li>Accountancy <a target=_blank title='Journal entry information from Wikipedia' href='http:\/\/en.wikipedia.org\/wiki\/Journal_entry'>journal entry<\/a> data has a header-detail structure here with the <a target=_blank title='SAP Business One information from Wikipedia' href='http:\/\/en.wikipedia.org\/wiki\/SAP_Business_One'>SAP Business One<\/a> data we use as input, but this translates fine when opening with Excel via the creation of two worksheets for header and detail.<\/li>\n<li>The main point of this tutorial, in my opinion, is that there is a way into programming via the incredibly useful and cute <a target=_blank title='VBA information from Wikipedia' href='http:\/\/en.wikipedia.org\/wiki\/Visual_Basic_for_Applications'>VBA<\/a> Macro Recording (of keyboard and mouse usage, converted into underlying VBA code) functionality of the Microsoft Office products of Excel, Word, Access, PowerPoint (at the very least).  You can learn VBA programming by the Record New Macro functionality, then Edit your Macro.   If things are not obvious try another recording and compare Macros, as needed.<\/li>\n<\/ul>\n<blockquote>\n<p>Microsoft Excel is a spreadsheet application developed by Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms, especially since version 5 in 1993, and it has replaced Lotus 1-2-3 as the industry standard for spreadsheets. Excel forms part of Microsoft Office.<\/p>\n<\/blockquote>\n<p>In this  <a target=_blank title='click picture' href='http:\/\/www.rjmprogramming.com.au\/Microsoft\/Excel\/'>tutorial<\/a> you can see some VBA Macro code primed by Record New Macro and then the Macro manipulated &#8220;by hand&#8221; (ie. the editor) to create nuanced differences, as an illustration of learning how Excel works and VBA Macro programming works, at least for Excel (Word is patently different for our case here where we deal with spreadsheet cell values, but you would get some good pointers to VBA for Microsoft Word even here, in the code conventions and project organization and language elements).<\/p>\n<p style=\"color:red;\"><strong>Interesting stop press &#8230; was on Yahoo Answers answering Excel VBA and did the old &#8220;answered a <a target=_blank title='Meant to answer ...' href='http:\/\/au.answers.yahoo.com\/question\/index;_ylt=AsY8yRgyoPzj0LZfOnw2O9NXCHRG;_ylv=3?qid=20130715154232AAJR4Fl'>different question<\/a>&#8221; (doh! &#8230; luckily stopped short of ultimate embarrassment (thanks to my fellow Yahoo Answers answerer!), but did something useful for somebody else) so want to explain the <a target=_blank title='In action' href='http:\/\/www.rjmprogramming.com.au\/Microsoft\/Excel\/ExcelCountMacro.JPG'>macro<\/a> below (used the techniques advocated above):<\/strong><\/p>\n<p style=\"color:orange;\">\n<code><br \/>\nSub TakeSelection_PutCount_inA1()<br \/>\n'<br \/>\n' TakeSelection_PutCount_inA1 Macro<br \/>\n' Macro recorded 16\/7\/2013 by Robert Metcalfe<br \/>\n    res = Selection.Count<br \/>\n    Range(\"A1\").Select<br \/>\n    ActiveCell.FormulaR1C1 = res<br \/>\n    Range(\"A2\").Select<br \/>\nEnd Sub<br \/>\n<\/code>\n<\/p>\n<p style=\"color:green;\"><strong> &#8230; this macro expects you to have selected a range of cells somewhere, and will put into cell A1 the count of this selection &#8230; would be easy to change it to put the sum of selection &#8230; or put the result somewhere else, or check that you have selected something Vs prompt and wait for them to do the selection(?) &#8230; or get it to create toast for breakfast &#8230; are you getting it?!   You may feel that this is not much use because you can do the same thing with a single button press, yourself, within Excel, but think you should be changing your mindset to see programming possibilities to frame it so that the data is a thing you make use of in generic approaches rather than continual one-off arrangements.<\/strong><\/p>\n<p>Link to Microsoft Excel more information &#8230; <a target=_blank href='http:\/\/en.wikipedia.org\/wiki\/Microsoft_Excel' title='Link to Excel more information'>via Wikipedia, where quote above came from<\/a>.<br \/>\nLink to Microsoft Excel from Microsoft Office Suite &#8220;spiritual home&#8221; &#8230; <a target=_blank href='http:\/\/office.microsoft.com\/en-au\/excel\/'>via Microsoft<\/a>.<br \/>\nThe mention of <a target=_blank title='Personal experience with SAP Business One' href='http:\/\/www.rjmprogramming.com.au\/SAPB1\/'>SAP Business One<\/a> could lead the user into the study of <a target=_blank title='EDI information from Wikipedia' href='https:\/\/en.wikipedia.org\/wiki\/Electronic_data_interchange'>EDI<\/a> (Electronic Data Interchange) in relation to Accountancy.\n<\/p>\n<p>If this was interesting you may be interested in <a title='Click here to see topics in which you might be interested' href='#d2557' onclick='var dv=document.getElementById(\"d2557\"); dv.innerHTML = \"&lt;iframe width=670 height=600 src=\" + \"http:\/\/www.rjmprogramming.com.au\/wordpress\/?s=Microsoft+Office#content\" + \"&gt;&lt;\/iframe&gt;\"; dv.style.display = \"block\";'>this<\/a> too.<\/p>\n<div id='d2557' style='display: none; border-left: 2px solid green; border-top: 2px solid green;'><\/div>\n","protected":false},"excerpt":{"rendered":"<p>There are a few different and varied concepts touched on in this Microsoft Excel VBA for CSV Primer Tutorial. The direct relationship Microsoft Excel spreadsheet application has with CSV (comma separated values) files in terms of input file(s) (as shown &hellip; <a href=\"https:\/\/www.rjmprogramming.com.au\/ITblog\/microsoft-excel-vba-for-csv-primer-tutorial\/\">Continue reading <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":{"footnotes":""},"categories":[12,37],"tags":[403,736,782,997,1200,1319,1364,1488],"class_list":["post-2557","post","type-post","status-publish","format-standard","hentry","category-elearning","category-tutorials","tag-excel","tag-macro","tag-microsoft","tag-programming","tag-stop-press","tag-tutorial","tag-vba","tag-yahoo-answers"],"_links":{"self":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/2557"}],"collection":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/comments?post=2557"}],"version-history":[{"count":0,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/posts\/2557\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/media?parent=2557"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/categories?post=2557"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rjmprogramming.com.au\/ITblog\/wp-json\/wp\/v2\/tags?post=2557"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}