An Example of How I Automated Excel With VBA

You know I try to save time whenever I can and one way to do that is to automate a task. I had to split a CSV file into 11 files today and thought I would turn to ChatGPT. Instead of spending 15 minutes on a boring task, I completed it in less than five minutes and improved my prompting skills.

Want to learn more ways to leverage AI in Finance? Read more posts like this here.

The Task I Needed to Automate – Split CSV File by Dept

I have a large CSV file in the following format:

GL Account	Vendor Name	Dept Name	Q1	Q2	Q3	Q4
62000	XXX	GA	100	500	500	500
62000	XXX	GA	100	100	100	100
62000	XXX	HR	100	750	100	750
63000	XXX	HR	500	750	750	750

It has hundreds of rows and 11 departments. My goal was to divide the dataset into 11 CSV files containing only the rows for that particular department. IIf I had all the time in the world and didn’t mind some mindless work, I could manually copy, paste, and save. It will probably take me ~15 minutes. However, my rule of thumb is: if a task takes more than five minutes manually, I try automating it with ChatGPT. I am glad I did.

How I Got the Working VBA Codes in Less Than Five Minutes

My initial prompt included the context (master CSV file) and the objective (split it into multiple files by a specific column). As you can see below, ChatGPT’s first solution was Python’s pandas library. While I had no doubt it will work based on my basic knowledge of the programming language, I was pretty sure VBA can achieve the same.

I then asked ChatGPT if I could achieve my goal without Python. In hindsight, I realized I should have asked to use VBA in the first place. Nonetheless, I got my first VBA code after my 3rd prompt shown below. The best part? I only had to update the name of my sheet in the code before it worked like a charm.

Don’t worry if you’re new to VBA; ChatGPT provided clear instructions on how to open the VBA editor and where in the code required updating.

The first solution asked me to “manually save each worksheet as a separate CSV file.” However, I was determined to automate from beginning to end so I asked my 4th question. ChatGPT returned another VBA code that worked without a hitch after I updated the path of the folder I wanted the CSV files to be saved in. Again, the provided code called out what I had to update. Theoretically, you can save yourself a step if you provide your folder path (e.g., C:\Your\Save\Directory\) in your prompt. However, since I avoid providing ChatGPT any more info than necessary, I took the “long” route.

How I Could Have Done This In A Minute

Prompting ChatGPT effectively and efficiently is a skill. Like with all skills, practice makes perfect.

Now that I have successfully automated the task with four prompts, I wanted to know if I could have achieved the same with one prompt. The answer is YES! I improved my initial prompt by being more specific about my objective (split and save), preferred solution (VBA). The end result was a working VBA code the first try.

Improved Prompt for VBA Code to Split CSV into Files by Column

On Excel, I got a master CSV file that I want to split into multiple CSV files based on Column D. For example, I want a single CSV file for all rows if Column D = ACCT. Another file if Column D = GA.
I need to save each CSV file to C:\Your\Save\Directory.
How can I do that with VBA?

VBA Code to Split CSV Into Files by Column by ChatGPT

Sub SplitCSVBasedOnColumnD()
    Dim ws As Worksheet
    Dim lastRow As Long, r As Long
    Dim outputPath As String
    Dim Dict As Object
    Dim key As Variant
    Dim outputWS As Worksheet
    Dim outputWB As Workbook
    Set ws = ThisWorkbook.Sheets(1) ' Assumes your data is in the first sheet
    Set Dict = CreateObject("Scripting.Dictionary")
    outputPath = "C:\Your\Save\Directory\"
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    ' Loop through all rows and collect unique values from Column D
    For r = 2 To lastRow ' Assumes row 1 has headers
        If Not Dict.exists(ws.Cells(r, 4).Value) Then
            Dict.Add ws.Cells(r, 4).Value, Nothing
        End If
    Next r
    ' Create a new workbook for each unique value in Column D and save as CSV
    For Each key In Dict.keys
        Set outputWB = Application.Workbooks.Add
        Set outputWS = outputWB.Sheets(1)
        ws.Rows(1).Copy Destination:=outputWS.Rows(1) ' Copy header row
        For r = 2 To lastRow
            If ws.Cells(r, 4).Value = key Then
                ws.Rows(r).Copy Destination:=outputWS.Rows(outputWS.Cells(outputWS.Rows.Count, "D").End(xlUp).Row + 1)
            End If
        Next r
        ' Remove any extra sheets
        Do While outputWB.Sheets.Count > 1
            Application.DisplayAlerts = False
            outputWB.Sheets(outputWB.Sheets.Count).Delete
            Application.DisplayAlerts = True
        Loop
        ' Save the new workbook as CSV in the specified directory
        outputWS.Name = "Data"
        outputWB.SaveAs Filename:=outputPath & key & ".csv", FileFormat:=xlCSV
        outputWB.Close SaveChanges:=False
    Next key
    MsgBox "CSV files have been created based on Column D values."
End Sub