Exporting Auctionator data to Excel

Getting Excel to read in CSV-formatted data from Auctionator in WoW was surprisingly difficult and surprisingly undocumented, so I’m going to write out how I did it just now.

The WoW addon Auctionator lets you export market data easily with one button press in CSV format, see screenshot

The problem comes when you try to paste that into your Excel spreadsheet. You can do it, but you have to find and click the “Text import wizard”, and then click about 10 more times carefully to get the data imported. I’m not going to document it because it’s tedious as hell.

I want to have the data in Excel since it’s easy to do basic calculations once it’s there, but getting it there was turning out to be a huge chore. What I ended up doing was having Grok2 help me write a visual basic script to do it.

It took a lot of back and forth, because apparently everyone works with the clipboard in Excel using some “MSForms DataObject”, but the default way to load that object doesn’t work on my machine. I ended up having to work around it by loading that object at runtime using a GUID. Once I got the GUID-based sample code working, I just prompted Grok to give me a script with it and using Sheets(2).

Sub PasteCSVData()
    Dim objDataObject As Object
    Dim clipboardData As String
    Dim lines As Variant
    Dim values As Variant
    Dim i As Long, j As Long

    ' Create DataObject using the CLSID
    Set objDataObject = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    ' Get data from clipboard
    objDataObject.GetFromClipboard
    clipboardData = objDataObject.GetText(1)
    
    ' Select Sheet 2
    With Sheets(2)
        ' Split the data into lines by newline characters
        lines = Split(clipboardData, vbLf)
        
        ' Loop through each line of the CSV data
        For i = LBound(lines) To UBound(lines)
            ' Split each line by comma for CSV
            values = Split(lines(i), ",")
            
            ' Loop through each value in the current line
            For j = LBound(values) To UBound(values)
                ' Place each value in the current cell, moving to the next cell in the same row
                .Cells(i + 1, j + 1).Value = Trim(values(j))
            Next j
            
            ' After processing a line, the next iteration will naturally go to the next row
            ' This happens because 'i' in the outer loop represents rows
        Next i
    End With
    
    ' MsgBox "Data has been pasted into Sheet 2 starting at A1, with each newline going to a new row!", vbInformation
End Sub

Then I added a button to run my script, and voila!

Gere’s the Auctionator2 shopping list I used, anything should work so long as it doesn’t contain commas in the item names: JC 2^"Bismuth"^"Handful of Pebbles"^"Crushed Gemstones"^"Glittering Glass"^"Radiant Ruby"^"Extravagant Emerald"^"Stunning Sapphire"^"Ostentatious Onyx"^"Null Stone"^"Ambivalent Amber"^"Bismuth"^"Ambivalent Amber"^"Inverted Prism"^"Engraved Gemcutter"^"Ironclaw Ore"^"Marbled Stone"^"Aqirite"

Note that the price column is in units of copper coins, so you can divide by 10,000 to get gold values.

Done!