Introduction
Ever found yourself needing to separate Excel worksheets into CSV flat files to make it easier to ingest for ETL? Well I’m going to walk you through the steps to take to get this done using Visual Basic(VBA). With the help of VBA and Macros in Excel, we can get the job done in no time.
How To
Step 1: Open the Excel File you want to break apart into CSV files and go to View on the ribbon, then proceed to the Macros section of that ribbon and select Record Macro. A window should pop up and name the Macro what you would like and select Personal Macro Workbook for the Store Macro In option. Click OK. Then go back to Macros up in the ribbon and click Stop Recording. (We are going to create a Macro that can be used again in other workbooks and why we are selecting Personal Macro Workbook, but you can also carry this task out in the file you are trying to break the worksheets out from.)
Step 2: Next, we are going to launch the VBA screen so we can implement the code by hitting Alt + F11 keys together on your keyboard or if you have the Developer option on the ribbon items, you can get to it from there as well. When the window opens you will see the object explorer pane off to the left with a project that is named VBAProject (PERSONAL.XLSB). Click on the plus sign next to it and then click on the plus sign next to Modules. We are going to double click on Module1. You should then see what is in the image below.
Step 3: Paste in between Sub and End Sub lines the following code. (should look like the below image) Important: Make sure to change the C:\FlatFiles\ to where you want your flat files saved out to:
Dim ws As Worksheet
Dim flatfile As String
For Each ws In Worksheets
ws.Copy
flatfile = "C:\Flatfiles\" & ws.Name & ".csv"
Application.ActiveWorkbook.SaveAs Filename:=flatfile, FileFormat:= _
xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
Step 4: Go ahead and close out of the VBA window by clicking on the ‘X’ the red arrow is pointing to in the image below. Now you have created your Macro to break out tabs into csv files! Next we can run the Macro and see the magic happen.
Step 5: Click on the Macros section in the View ribbon and then click ‘View Macros’. Ensure on the window that pops up that you select our new macro we created and then click Run.
What I see when I check the target directory used:
Step 6: Close out of the Excel file you are in and click Save option on the pop up that you will get regarding the Personal Macro Workbook. Now you can open any Excel Workbook and use this Macro because we saved it in a Personal Workbook!
Explanation of the code:
So, in looking through the code we used, I wanted to point some things out to make it helpful in understanding what it is doing.
When looking at the following, the Dim is short for Dimension and is VBA speak for a variable. It is something that can be set to a value and changed if needed throughout the Macro. We are aligning the name ws to align with a Worksheet object. The second is assigning ‘flatfile’ as a variable that will contain a datatype of string which accepts letters, numbers and special characters.
Next we will look at the For Each event that is carried out after the variables are established. I have broken it down into chunks to explain. Please refer to the image below:
- This is where saying that for each worksheet in the file, the following code will get carried out each time. For worksheet 1, actions 2 through 5 will occur. Then for Worksheet 2 the same etc…. until there are no more worksheets to address.
- This is telling the application to create a copy of the given worksheet.
- This is setting the flatfile variable we set up to a particular value. In this case, we have a file directory location of C:\FlatFiles\ wrapped in double quotes so that the application sees this as a literal string that needs to be used followed by the & sign which tells the application to join what comes before and after together in a concatenation. The following part of ws.Name is referring to the variable we first created and tapping into the Worksheet Object and then getting the Name property of that worksheet. Lastly, the .csv is wrapped in double quotes to signify the literal string
- This is telling the application to refer to the active workbook and carry out a save as of the value of the flatfile variable you just set in the prior step.
- These 2 lines are giving further instruction for the Application to then save and close the active workbook.
Hopefully this gives you another tool to use in your professional arsenal. Visual Basic for Applications is a great skillset to learn and based on commentary I can post some more on the subject. Let me know if this helped you. I would really appreciate it!
Dale Levesque
Data Engineer, Patriot Consulting
Email: dlevesque@patriotconsultingcorp.com | Blogs: Patriot Consulting Blogs
LinkedIn: Personal: DaleLevesque | Company: Patriot Consulting
