Today I spent an hour working with CoPilot on some Excel VBA code to do a custom sort that is not normally possible with the normal sorting functions, and import data from a CSV file with a system dialog to display the default choice, but allow changing it.

I was working on a weekly report I wanted to send to a client to let him know what tasks I’m working on and expected delivery dates.

The requirements:

1. Sort the list by Task ID ascending, but always put tasks in the Closed status at the bottom. (This immediately presents a challenge because it’s geared towards a specific single status with multiple other possible statuses) Normally you can’t sort by a particular value and everything else in the standard sorting mechanism. You typically would have to create a helper column to do this and sort on that.
2. I export a csv file with updated data to my Downloads directory from my online PM system. Then I open and copy the data from the csv file into the formatted Excel table with conditional formatting for highlighting due dates. Then I delete the csv file when I’m done.

So that was pretty much it. I was starting from scratch in terms of code. I had no modules or anything.

I started by explaining to CoPilot the type of sorting I wanted with the file open in Excel. I used the Copilot button in the Home ribbon to open a side window in Excel to chat with CoPilot:

“I want to be able to sort this list and put any tasks in the Closed status at the bottom in Task ID order, and all the other tasks at the top in Task ID order. What’s the easiest way to automate that?”

The first thing CoPilot did was to actually sort the rows for me and give me a link to download a CSV file with the sorted list. I wasn’t really interested in that since I was wanting to make this repeatable, so I clicked on a button for a response it suggested for me:

“Show me the macro code for this sorting.”

Which resulted in a nice VBA function to sort everything in my current sheet as I had asked. I followed the instructions it gave and pasted it’s code into a module and ran it. It sort of worked, but didn’t do everything I had expected. It did sort the list, but left the helper column. It tried to delete it in the code, but it didn’t work. Also, the sort method was assuming I had not formatted my data as a table, so I got an error initially on that method. It did work once I put the helper column in the table itself, but then still left the helper column. Also, once I manually sorted a column using the table mechanism, the sorting would remain and override the sorting it was trying to do.

Ok, we were getting somewhere and I really hadn’t had to do a whole lot yet in terms of coding, just try to debug some of it’s code. Next I asked it about creating a button or running the procedure from the ribbon.

CoPilot dutifully gave me the instructions on how to do both, so I added it to a new ribbon with a new group. Pretty cool.

Next I focused on the problems I was having:

“I need to force clear all other sorting in the macro before it sorts. It is also not deleting the helper column it is adding. It is adding it to a table, is that why”

This prompt caused CoPilot to rewrite the procedure (which it kept calling a macro) to account for the issues I was having. It now did everything I had asked for and was doing it ALMOST perfectly. But I noticed another issue as I tried various other sorting and having it reset:

“The first row of data is not being included in the sort”

It explained why it thought that was happening and provided an updated procedure that it said would fix it.

This time it fully worked and I could happily try to mess up the sorting all I wanted but pressing the ribbon button would put it all back into the original order.

Good! That was set. Now on to the next requirement, note that I try to be pretty verbose with CoPilot and give it a lot of context:

“Great, now I’d like another routine to update the table with new data from a freshly downloaded CSV file. It will typically be in my user Downloads folder and will usually be called Tasks.csv, but I want it to prompt the user with a file dialog to select a different file if desired (but the default file selected should be Tasks.csv in the downloads folder.The routine should verify that the headings in the CSV file are the same as the headings in the table, then paste the values from the data rows in the csv file over the values currently in the table.The routine should also either shorten or lengthen the table as needed to accomodate the exact number of data rows (excluding the header) from the csv file.”

It provided another subroutine and function, one to select the file and paste the data, and the function to put the data into an array to use for pasting.

CoPilot used a Scripting.FileSystemObject to try to read in the text, but it was not correctly getting the format. It kept using the initial BOM header in the file inside the first column name string which caused the validation to file and the file to not import.

CoPilot first attempted to make it’s routine BOM safe, but that didn’t work and ended up making it worse, so it decided to try another method native to Excel to import the CSV as a text file. This worked like a charm and I ran several tests (although I forgot to check whether different import sizes (smaller or larger) would work properly… Anyway, I then mentioned deleting the file if everything had properly executed in the data import. so it gave me some code to tag on to the end of the routine.

Here is the final module that was built (note that it’s specific to my file format, sheet names, etc., but with CoPilot’s help it could easily be changed or made more generic):

Option Explicit 

Sub UpdateTaskTableFromCSV() 
    Dim ws As Worksheet 
    Dim tbl As ListObject 
    Dim fd As FileDialog 
    Dim csvPath As String 
    Dim tempWB As Workbook 
    Dim tempWS As Worksheet 
    Dim csvData As Variant 
    Dim i As Long,j As Long 
    Dim headerMatch As Boolean 

    ' Set worksheet and table
    Set ws = ThisWorkbook.Sheets( "Tasks") 
    If ws.ListObjects.Count = 0 Then 
        MsgBox  "No table found on the sheet.",vbExclamation 
        Exit Sub 
    End If 
    Set tbl = ws.ListObjects(1) 

    ' Prompt user to select CSV file
    Set fd = Application.FileDialog(msoFileDialogFilePicker) 
    With fd 
        .Title =  "Select CSV File"
        .InitialFileName = Environ( "USERPROFILE") &  "\Downloads\Tasks.csv"
        .Filters.Clear 
        .Filters.Add  "CSV Files", "*.csv"
        If .Show <> -1 Then Exit Sub  ' User cancelled
        csvPath = .SelectedItems(1) 
    End With 

    ' Open CSV in a temporary workbook using UTF-8
    Workbooks.OpenText Filename:=csvPath,Origin:=xlWindows,_ 
        DataType:=xlDelimited,TextQualifier:=xlTextQualifierDoubleQuote,_ 
        ConsecutiveDelimiter:=False,Tab:=False,Semicolon:=False,_ 
        Comma:=True,Space:=False,Other:=False,_ 
        FieldInfo:=Array(1,1),TrailingMinusNumbers:=True 

    Set tempWB = ActiveWorkbook 
    Set tempWS = tempWB.Sheets(1) 

    ' Read CSV data into array
    csvData = tempWS.UsedRange.Value 

    ' Compare headers
    headerMatch = True 
    For i = 1 To tbl.ListColumns.Count 
        If i > UBound(csvData,2) Or csvData(1,i) <> tbl.HeaderRowRange.Cells(1,i).Value Then 
            headerMatch = False 
            Exit For 
        End If 
    Next i 

    If Not headerMatch Then 
        MsgBox  "CSV headers do not match table headers.",vbCritical 
        tempWB.Close False 
        Exit Sub 
    End If 

    ' Resize table to match CSV data rows
    With tbl 
        .Resize .Range.Resize(UBound(csvData,1),.ListColumns.Count) 
    End With 

    ' Paste CSV data into table (excluding header)
    For i = 2 To UBound(csvData,1) 
        For j = 1 To UBound(csvData,2) 
            tbl.DataBodyRange.Cells(i - 1,j).Value = csvData(i,j) 
        Next j 
    Next i 

    ' Close temp workbook
    tempWB.Close False 

    ' Delete the CSV file after successful import
    On Error Resume Next 
    Kill csvPath 
    If Err.Number <> 0 Then 
        MsgBox  "Table updated, but could not delete the CSV file: " & Err.Description,vbExclamation 
    Else 
        MsgBox  "Table updated successfully and CSV file deleted.",vbInformation 
    End If 
    On Error GoTo 0 
End Sub 

Sub SortTasksByStatusAndTaskID() 
    Dim ws As Worksheet 
    Dim tbl As ListObject 
    Dim helperCol As ListColumn 
    Dim i As Long 

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets( "Tasks") 

    ' Get the first table on the sheet
    If ws.ListObjects.Count = 0 Then 
        MsgBox  "No table found on the sheet.",vbExclamation 
        Exit Sub 
    End If 

    Set tbl = ws.ListObjects(1) 

    ' Clear any previous sort applied to the table
    On Error Resume Next 
    tbl.Sort.SortFields.Clear 
    On Error GoTo 0 

    ' Add helper column to the table
    Set helperCol = tbl.ListColumns.Add 
    helperCol.Name =  "SortOrder"

    ' Fill helper column: 1 for non-Closed, 2 for Closed
    For i = 1 To tbl.DataBodyRange.Rows.Count 
        If tbl.DataBodyRange.Cells(i,tbl.ListColumns( "Status").Index).Value =  "Closed" Then 
            tbl.DataBodyRange.Cells(i,helperCol.Index).Value = 2 
        Else 
            tbl.DataBodyRange.Cells(i,helperCol.Index).Value = 1 
        End If 
    Next i 

    ' Apply sort using table's built-in sort
    With tbl.Sort 
        .SortFields.Clear 
        .SortFields.Add Key:=helperCol.DataBodyRange,SortOn:=xlSortOnValues,Order:=xlAscending,DataOption:=xlSortNormal 
        .SortFields.Add Key:=tbl.ListColumns( "Task ID").DataBodyRange,SortOn:=xlSortOnValues,Order:=xlAscending,DataOption:=xlSortNormal 
        .Header = xlYes 
        .Apply 
    End With 

    ' Delete helper column
    tbl.ListColumns( "SortOrder").Delete 
End Sub