CSV Import File Creation Macro

Step-by-step guide

The info below is excerpted from the Microsoft Office online help and modified to include specific instructions for the CreateCSV macro. These instructions are designed for Office 2010 and later.

Macros and the Visual Basic Editor

Microsoft Office allows you to run pre-recorded instructions on Excel data. To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. Although it is installed by default, many users do not know that it is even available until it is enabled on the ribbon.

Developer Tab

All Office 2010 and later applications use the ribbon. One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools. Because Office 2010 does not display the Developer tab by default, you must enable it using the following procedure:

To enable the Developer tab

1. On the File tab, choose Options to open the Excel Options dialog box.
2. Click Customize Ribbon on the left side of the dialog box.
3. Under Choose commands from on the left side of the dialog box, select Popular Commands.
4. Under Customize the ribbon on the right side of the dialog box, select Main tabs, and then select the Developer check box.
5. Click OK.

After Excel displays the Developer tab, note the location of the Visual Basic, Macros, and Macro Security buttons on the tab.

Figure 1. Developer tab in Excel 2010

Security Issues

Click the Macro Security button to specify which macros can run and under what conditions. Although rogue macro code can seriously damage your computer, security conditions that prevent you from running helpful macros can seriously undermine your productivity. Macro security is a complex and involved topic that you should study and understand if you work with Excel macros.

For the purposes of this article, be aware that if the Security Warning: Macros have been disabled bar appears between the ribbon and the worksheet when you open a workbook that contains a macro, you can click the Enable Content button to enable the macros.

Also, as a security measure, you cannot save a macro in the default Excel file format (.xlsx); instead, you must save the macro in a file with a special extension, .xlsm.

Visual Basic Editor

This following procedure shows you how to create a new blank workbook in which to store your macros. You can then save the workbook in the .xlsm format.

To create a new blank workbook

1. Click the Macros button on the Developer tab.
2. In the Macro dialog box that appears, type, CreateCSV under Macro Name.
3. Click the Create button to open the Visual Basic Editor with the outlines of a new macro already typed in.

VBA is a full-featured programming language with a correspondingly full-featured programming environment. This article examines only those tools that you use to get started with programming, and that excludes most of the tools in the Visual Basic Editor. With this caveat, close the Properties window on the left side of the Visual Basic Editor, and ignore the two dropdown lists that appear above the code.

Figure 2. Visual Basic Editor

The Visual Basic Editor contains the following code.

VB

Sub CreateCSV()

End Sub

Sub stands for Subroutine, which you can define for now as "macro". Running the CreateCSV macro runs any code that is between Sub CreateCSV  () and End Sub.

Now edit the macro so that it looks similar to the following code - feel free to copy and paste the code below - everything between Sub CreateCSV() and End Sub.

VB

Sub CreateCSV()

            Dim lColumnCount As Long

            Dim lRowCount As Long

            Dim strFileName As String

            MsgBox ("This Macro will create a CSV version of the SELECTED ROWS AND COLUMNS.  It will surround the cell data with quotes and separate each cell with a comma.  Finally, it will replace any chr(10)s with chr(13)s.")

            strFileName = InputBox("Please enter the destination file path.", "", "C:\Temp\Imports\" & LCase(ActiveSheet.Name) & ".csv")

            If (strFileName = "") Then

            Exit Sub

            End If

            On Error Resume Next

            Open strFileName For Output As #1

            If Err.Number > 0 Then

            MsgBox ("Error: The path you selected may be bad.")

            Exit Sub

            End If

            lColumnCount = Selection.Columns.Count

            lRowCount = Selection.Rows.Count

            Dim sValue As String

            For lRow = 1 To lRowCount

            For lColumn = 1 To lColumnCount

            sValue = Selection.Cells(lRow, lColumn).Value

            If lColumn = lColumnCount Then

                        If sValue = "" Then

                        Write #1, ""

                        Else

                        Write #1, sValue

                        End If

            Else

                        If sValue = "" Then

                        Write #1, "",

                        Else

                        Write #1, Replace(Replace(Replace(Replace(sValue, Chr(10), Chr(13)), Chr(14), ""), Chr(24), ""), Chr(25), ""),

                        End If

            End If

            Next lColumn

            Next lRow

            Close #1

            MsgBox ("CSV file created successfully.")

End Sub

When used this macro will take whatever columns and rows are currently selected in the workbook and save them in the C:\Temp\Imports directory (if you have not already created this directory, an "Invalid path" error will be thrown) - the csv file will use the Worksheet name as the file name and save it with a .csv file extension.

Go back to the Developer tab in Excel and click the Macros button again.

You should now see CreateCSV as an option.

You just created and implemented custom VBA code in Excel. 

Making Macros Accessible

You can also get to the Macros dialog box from the View tab, but if you use a macro frequently, you might find it more convenient to access it with a keyboard shortcut or a Quick Access Toolbar button.

To create a button for the CreateCSV macro on the Quick Access Toolbar, use the following procedure.

The following procedure describes how to make a button for a macro on the Quick Access Toolbar:

To create a button for a macro on the Quick Access Toolbar

1. Click the File tab.
2. Click Options to open the Excel Options dialog box, and then click Quick Access Toolbar.
3. In the list under Choose commands from:, choose Macros. Find the text that is similar to Book1!CreateCSV in the list that appears and select that text.
4. Click the Add >> button to add the macro to the list on the right side, and then click the Modify… button to select a button image to associate with the macro.  In the image below I have selected an icon that looks a bit like our ClientSpace logo
   
5. Click OK. You should see your new button on the Quick Access Toolbar above the File tab.  This CreateCSV button will be available in any workbook now.
   

Now you can quickly run your macro at any time without using the Developer tab—give it a try.