Microsoft Excel VBA for CSV Primer Tutorial
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 here) and output files (not shown here, but Excel can output CSV files).
- Accountancy journal entry data has a header-detail structure here with the SAP Business One data we use as input, but this translates fine when opening with Excel via the creation of two worksheets for header and detail.
- The main point of this tutorial, in my opinion, is that there is a way into programming via the incredibly useful and cute VBA 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.
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.
In this tutorial you can see some VBA Macro code primed by Record New Macro and then the Macro manipulated “by hand” (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).
Interesting stop press … was on Yahoo Answers answering Excel VBA and did the old “answered a different question” (doh! … 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 macro below (used the techniques advocated above):
Sub TakeSelection_PutCount_inA1()
'
' TakeSelection_PutCount_inA1 Macro
' Macro recorded 16/7/2013 by Robert Metcalfe
res = Selection.Count
Range("A1").Select
ActiveCell.FormulaR1C1 = res
Range("A2").Select
End Sub
… this macro expects you to have selected a range of cells somewhere, and will put into cell A1 the count of this selection … would be easy to change it to put the sum of selection … or put the result somewhere else, or check that you have selected something Vs prompt and wait for them to do the selection(?) … or get it to create toast for breakfast … 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.
Link to Microsoft Excel more information … via Wikipedia, where quote above came from.
Link to Microsoft Excel from Microsoft Office Suite “spiritual home” … via Microsoft.
The mention of SAP Business One could lead the user into the study of EDI (Electronic Data Interchange) in relation to Accountancy.
If this was interesting you may be interested in this too.
