Utility - Equipment Export | RedGuides: EverQuest Multi-boxing Community

Server Type: 🏢 Live

Track equipment stats across all your EQ characters in one organized spreadsheet. I'm new(ish) to the game and tracking gear across multiple characters is a huge PITA.

First off, this wasn't my idea. Inspiration came from this gear.mac (credit: Lemon and CWTN) and this thread about the spreadsheet (credit: Szazor).

How does this work:

Run /mac equipment on one or all characters
Data exports to /config/<charactername>_equipment.ini
Use the Excel importer to consolidate all characters into one view
- In Excel, press alt + F8 and run ImportAllData
- (your character names will show, mine are hidden for privacy)

Spreadsheet w/ VB Script

The link below contains an Excel file with several tabs and conditional formatting. The VB script (bottom of this post) contains the code to import all of your <charactername>_equipment.ini in the /config folder.

Google Drive

Setup:

After downloading the Excel file you need to update the config folder path in the Setup tab. If you don't complete this super important task, the script will fail. If you're not sure how to find the folder, this is what I do:

Open the Redguide launcher
Click Very Vanilla
Open MQ Folder
Make sure the path you copy contains \config on it. For example:
C:\Users\<username>\AppData\Local\VeryVanilla\MacroQuest\Release\config

Next, you need to import the VB script:

In Excel, press alt + F11
Click Insert -> Module
Paste the code at the bottom of this post into the blank window that popped up
Close the Visual Basic window
Save your Excel file AS A MACRO ENABLED WORKBOOK - it should have the extension XLSM when you're done
- You will not be able to run the script without completing this step
Press alt + F8 and run ImportAllData

Conditional formatting is only set up on the Equipment tab. I might add it later for the Type5/7 augs, but each character has their own sortable table and didn't think it was necessary. Other aug types might be added later once I actually get some of them.

Here's the VB script. Check it yourself or toss it at chatGPT for a review if you're concerned about what it does. The short version:

The script gets the config folder path from the Setup tab
It checks the folder for any files matching the format <charactername>_equipment.ini
It reads the file(s), parses the data, and imports each characters' equipment and augs into the different tabs
Missing equipment slots or augs will be left blank

VB Script:

' ===== MASTER IMPORT =====
Sub ImportAllData()
    Dim folderPath As String
    Dim fileName As String
    Dim characterName As String
    Dim setupSheet As Worksheet
    Dim equipSheet As Worksheet
    Dim type5Sheet As Worksheet
    Dim type7Sheet As Worksheet
    Dim type4Sheet As Worksheet
    Dim fso As Object
    Dim fileNum As Integer
    Dim line As String
    Dim lineData() As String
    Dim slotName As String
    
    ' Equipment data
    Dim equipRow As Long
    Dim slotColumn As Integer
    
    ' Aug data
    Dim type5Data As Object
    Dim type7Data As Object
    Dim type4Row As Long
    Dim slotNames() As Variant
    Dim i As Integer
    Dim type5Row As Long
    Dim type7Row As Long
    
    ' Reference sheets
    Set setupSheet = ThisWorkbook.Sheets("Setup")
    Set equipSheet = ThisWorkbook.Sheets("Equipment")
    Set type5Sheet = ThisWorkbook.Sheets("Type5 Aug")
    Set type7Sheet = ThisWorkbook.Sheets("Type7 Aug")
    Set type4Sheet = ThisWorkbook.Sheets("Type4 Aug")
    
    ' Get folder path from Setup sheet
    folderPath = setupSheet.Range("B1").value
    
    ' Check if folder path is empty
    If Trim(folderPath) = "" Then
        MsgBox "Please enter the MQ config folder path in the Setup tab", vbExclamation
        Exit Sub
    End If
    
    ' Make sure folder path ends with backslash
    If Right(folderPath, 1) <> "\" Then
        folderPath = folderPath & "\"
    End If
    
    ' Create FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    ' Check if folder exists
    If Not fso.FolderExists(folderPath) Then
        MsgBox "Invalid config folder path - update on Setup tab", vbExclamation
        Exit Sub
    End If
    
    ' Clear existing data and delete existing tables
    Call ClearSheetAndTables(equipSheet, "A2:SZ1000")
    Call ClearSheetAndTables(type5Sheet, "A1:N1000")
    Call ClearSheetAndTables(type7Sheet, "A1:N1000")
    Call ClearSheetAndTables(type4Sheet, "A2:C1000")
    
    ' Write Equipment sheet headers (only needs to happen once)
    Call WriteEquipmentHeaders(equipSheet)
    
    ' Define slot names in order
    slotNames = Array("Chest", "Head", "Legs", "Arms", "Hands", "LeftWrist", "RightWrist", _
                      "Face", "Neck", "Back", "Shoulder", "Waist", "LeftEar", "RightEar", _
                      "LeftRing", "RightRing", "Charm", "PowerSource", "Primary", "Secondary", "Ranged")
    
    ' Initialize row counters
    equipRow = 2
    type5Row = 1
    type7Row = 1
    type4Row = 2
    
    ' Loop through all files in folder
    fileName = Dir(folderPath & "*_equipment.ini")
    
    Do While fileName <> ""
        ' Extract character name from filename
        characterName = Left(fileName, InStr(fileName, "_equipment.ini") - 1)
        
        ' Write character name to Equipment sheet (SANITIZED)
        equipSheet.Cells(equipRow, 1).value = SanitizeValue(characterName)
        
        ' Create dictionaries to store aug data for this character
        Set type5Data = CreateObject("Scripting.Dictionary")
        Set type7Data = CreateObject("Scripting.Dictionary")
        
        ' Open and parse the file ONCE
        fileNum = FreeFile
        Open folderPath & fileName For Input As #fileNum
        
        Do While Not EOF(fileNum)
            Line Input #fileNum, line
            
            ' Skip section headers and empty lines
            If Left(line, 1) <> "[" And Trim(line) <> "" And InStr(line, "=") > 0 Then
                ' Get the data part (after the =)
                line = Mid(line, InStr(line, "=") + 1)
                
                ' Split by comma
                lineData = Split(line, ",")
                
                If UBound(lineData) >= 3 Then
                    slotName = Trim(lineData(0))
                    
                    ' Check what type of line this is
                    If InStr(slotName, "-Type5") > 0 Then
                        ' Type5 aug line
                        slotName = Left(slotName, InStr(slotName, "-Type5") - 1)
                        If UBound(lineData) >= 12 Then
                            Dim type5Stats() As Variant
                            Dim itemName5 As String
                            itemName5 = SanitizeValue(lineData(1))  ' SANITIZED Item name
                            ' Store: ItemName, AC, HP, Mana, Endurance, HeroicSTR-CHA (positions 1-12)
                            type5Stats = Array(itemName5, SanitizeValue(lineData(2)), SanitizeValue(lineData(3)), SanitizeValue(lineData(4)), _
                                              SanitizeValue(lineData(5)), SanitizeValue(lineData(6)), SanitizeValue(lineData(7)), _
                                              SanitizeValue(lineData(8)), SanitizeValue(lineData(9)), SanitizeValue(lineData(10)), _
                                              SanitizeValue(lineData(11)), SanitizeValue(lineData(12)))
                            type5Data(slotName) = type5Stats
                            
                            ' Write to Equipment sheet (skip item name, just stats positions 2-12)
                            Dim equipStats5() As Variant
                            equipStats5 = Array(SanitizeValue(lineData(2)), SanitizeValue(lineData(3)), SanitizeValue(lineData(4)), _
                                               SanitizeValue(lineData(5)), SanitizeValue(lineData(6)), SanitizeValue(lineData(7)), _
                                               SanitizeValue(lineData(8)), SanitizeValue(lineData(9)), SanitizeValue(lineData(10)), _
                                               SanitizeValue(lineData(11)), SanitizeValue(lineData(12)))
                            Call WriteAugToEquipment(equipSheet, equipRow, slotName, equipStats5, "Type5")
                        End If
                        
                    ElseIf InStr(slotName, "-Type7") > 0 Then
                        ' Type7 aug line
                        slotName = Left(slotName, InStr(slotName, "-Type7") - 1)
                        If UBound(lineData) >= 12 Then
                            Dim type7Stats() As Variant
                            Dim itemName7 As String
                            itemName7 = SanitizeValue(lineData(1))  ' SANITIZED Item name
                            ' Store: ItemName, AC, HP, Mana, Endurance, HeroicSTR-CHA (positions 1-12)
                            type7Stats = Array(itemName7, SanitizeValue(lineData(2)), SanitizeValue(lineData(3)), SanitizeValue(lineData(4)), _
                                              SanitizeValue(lineData(5)), SanitizeValue(lineData(6)), SanitizeValue(lineData(7)), _
                                              SanitizeValue(lineData(8)), SanitizeValue(lineData(9)), SanitizeValue(lineData(10)), _
                                              SanitizeValue(lineData(11)), SanitizeValue(lineData(12)))
                            type7Data(slotName) = type7Stats
                            
                            ' Write to Equipment sheet (skip item name, just stats positions 2-12)
                            Dim equipStats7() As Variant
                            equipStats7 = Array(SanitizeValue(lineData(2)), SanitizeValue(lineData(3)), SanitizeValue(lineData(4)), _
                                               SanitizeValue(lineData(5)), SanitizeValue(lineData(6)), SanitizeValue(lineData(7)), _
                                               SanitizeValue(lineData(8)), SanitizeValue(lineData(9)), SanitizeValue(lineData(10)), _
                                               SanitizeValue(lineData(11)), SanitizeValue(lineData(12)))
                            Call WriteAugToEquipment(equipSheet, equipRow, slotName, equipStats7, "Type7")
                        End If
                        
                    ElseIf InStr(slotName, "-Type4") > 0 Then
                        ' Type4 aug line (weapons only) - ALL SANITIZED
                        If UBound(lineData) >= 13 Then
                            ' Write character name, item name, and damage value
                            type4Sheet.Cells(type4Row, 1).value = SanitizeValue(characterName)
                            type4Sheet.Cells(type4Row, 2).value = SanitizeValue(lineData(1)) ' Item Name
                            type4Sheet.Cells(type4Row, 3).value = SanitizeValue(lineData(13)) ' Damage column
                            type4Row = type4Row + 1
                        End If
                        
                    ElseIf InStr(slotName, "-Type") = 0 Then
                        ' Base equipment line (no "-Type" in name) - SANITIZED
                        slotColumn = GetSlotColumn(slotName)
                        If slotColumn > 0 Then
                            equipSheet.Cells(equipRow, slotColumn).value = SanitizeValue(lineData(3)) ' HP value (position 3)
                        End If
                    End If
                End If
            End If
        Loop
        
        Close #fileNum
        
        ' Write Type5 aug data for this character and create table
        Call WriteAugBlockWithTable(type5Sheet, type5Row, characterName, slotNames, type5Data)
        type5Row = type5Row + 22 + 2 ' 1 header + 21 data rows + 2 blank rows
        
        ' Write Type7 aug data for this character and create table
        Call WriteAugBlockWithTable(type7Sheet, type7Row, characterName, slotNames, type7Data)
        type7Row = type7Row + 22 + 2 ' 1 header + 21 data rows + 2 blank rows
        
        ' Move to next row for equipment
        equipRow = equipRow + 1
        
        ' Get next file
        fileName = Dir()
    Loop
    
    ' Left-justify all data in all sheets
    equipSheet.UsedRange.HorizontalAlignment = xlLeft
    type5Sheet.UsedRange.HorizontalAlignment = xlLeft
    type7Sheet.UsedRange.HorizontalAlignment = xlLeft
    type4Sheet.UsedRange.HorizontalAlignment = xlLeft
    
    MsgBox "All data imported successfully!", vbInformation
    
End Sub

' Write headers for Equipment sheet
Sub WriteEquipmentHeaders(equipSheet As Worksheet)
    Dim slotNames() As Variant
    Dim statNames() As Variant
    Dim i As Integer
    Dim j As Integer
    Dim col As Integer
    Dim headerText As String
    
    ' Define slot names
    slotNames = Array("Chest", "Head", "Legs", "Arms", "Hands", "Left Wrist", "Right Wrist", _
                      "Face", "Neck", "Back", "Shoulder", "Waist", "Left Ear", "Right Ear", _
                      "Left Ring", "Right Ring", "Charm", "Power Source", "Primary", "Secondary", "Ranged")
    
    ' Define stat names for Type5 and Type7
    statNames = Array("AC", "HP", "Mana", "Endurance", "HeroicSTR", "HeroicSTA", "HeroicAGI", _
                      "HeroicDEX", "HeroicINT", "HeroicWIS", "HeroicCHA")
    
    ' Column A: Character
    equipSheet.Cells(1, 1).value = "Character"
    
    ' Columns B-V: Base equipment HP (already exist from original code)
    For i = 0 To UBound(slotNames)
        equipSheet.Cells(1, 2 + i).value = slotNames(i) & " HP"
    Next i
    
    ' Column W (23): Empty separator
    
    ' Columns X onwards (24+): Type5 stats
    col = 24
    For i = 0 To UBound(slotNames)
        For j = 0 To UBound(statNames)
            headerText = "Type5 " & slotNames(i) & " " & statNames(j)
            equipSheet.Cells(1, col).value = headerText
            col = col + 1
        Next j
    Next i
    
    ' Empty separator column (col is now at 255)
    col = col + 1
    
    ' Type7 stats
    For i = 0 To UBound(slotNames)
        For j = 0 To UBound(statNames)
            headerText = "Type7 " & slotNames(i) & " " & statNames(j)
            equipSheet.Cells(1, col).value = headerText
            col = col + 1
        Next j
    Next i
End Sub

' Write aug stats to Equipment sheet
Sub WriteAugToEquipment(equipSheet As Worksheet, row As Long, slotName As String, stats() As Variant, augType As String)
    Dim slotIndex As Integer
    Dim baseCol As Integer
    Dim i As Integer
    
    ' Get slot index (0-20)
    slotIndex = GetSlotIndex(slotName)
    If slotIndex < 0 Then Exit Sub
    
    ' Calculate base column
    If augType = "Type5" Then
        baseCol = 24 + (slotIndex * 11)
    ElseIf augType = "Type7" Then
        baseCol = 256 + (slotIndex * 11)
    Else
        Exit Sub
    End If
    
    ' Write all 11 stats (already sanitized when passed in)
    For i = 0 To UBound(stats)
        equipSheet.Cells(row, baseCol + i).value = stats(i)
    Next i
End Sub

' Get slot index (0-20) from slot name
Function GetSlotIndex(slotName As String) As Integer
    Select Case slotName
        Case "Chest": GetSlotIndex = 0
        Case "Head": GetSlotIndex = 1
        Case "Legs": GetSlotIndex = 2
        Case "Arms": GetSlotIndex = 3
        Case "Hands": GetSlotIndex = 4
        Case "LeftWrist": GetSlotIndex = 5
        Case "RightWrist": GetSlotIndex = 6
        Case "Face": GetSlotIndex = 7
        Case "Neck": GetSlotIndex = 8
        Case "Back": GetSlotIndex = 9
        Case "Shoulder": GetSlotIndex = 10
        Case "Waist": GetSlotIndex = 11
        Case "LeftEar": GetSlotIndex = 12
        Case "RightEar": GetSlotIndex = 13
        Case "LeftRing": GetSlotIndex = 14
        Case "RightRing": GetSlotIndex = 15
        Case "Charm": GetSlotIndex = 16
        Case "PowerSource": GetSlotIndex = 17
        Case "Primary": GetSlotIndex = 18
        Case "Secondary": GetSlotIndex = 19
        Case "Ranged": GetSlotIndex = 20
        Case Else: GetSlotIndex = -1
    End Select
End Function

' Helper function to clear sheet data and delete all tables
Sub ClearSheetAndTables(ws As Worksheet, clearRange As String)
    Dim tbl As ListObject
    
    ' Delete all tables in the sheet
    For Each tbl In ws.ListObjects
        tbl.Delete
    Next tbl
    
    ' Clear the range
    ws.Range(clearRange).ClearContents
End Sub

' Helper function to write aug block to a sheet and create a table
Sub WriteAugBlockWithTable(augSheet As Worksheet, startRow As Long, characterName As String, slotNames As Variant, augData As Object)
    Dim i As Integer
    Dim j As Integer
    Dim currentRow As Long
    Dim tableRange As Range
    Dim tableName As String
    Dim newTable As ListObject
    
    ' Write headers
    augSheet.Cells(startRow, 1).value = "Character"
    augSheet.Cells(startRow, 2).value = "Slot"
    augSheet.Cells(startRow, 3).value = "Item Name"
    augSheet.Cells(startRow, 4).value = "AC"
    augSheet.Cells(startRow, 5).value = "HP"
    augSheet.Cells(startRow, 6).value = "Mana"
    augSheet.Cells(startRow, 7).value = "Endurance"
    augSheet.Cells(startRow, 8).value = "HeroicSTR"
    augSheet.Cells(startRow, 9).value = "HeroicSTA"
    augSheet.Cells(startRow, 10).value = "HeroicAGI"
    augSheet.Cells(startRow, 11).value = "HeroicDEX"
    augSheet.Cells(startRow, 12).value = "HeroicINT"
    augSheet.Cells(startRow, 13).value = "HeroicWIS"
    augSheet.Cells(startRow, 14).value = "HeroicCHA"
    
    ' Bold the header row
    augSheet.Range(augSheet.Cells(startRow, 1), augSheet.Cells(startRow, 14)).Font.Bold = True
    
    ' Write data rows
    For i = 0 To UBound(slotNames)
        currentRow = startRow + 1 + i
        
        ' Write character name only on first row (Chest) - SANITIZED
        If i = 0 Then
            augSheet.Cells(currentRow, 1).value = SanitizeValue(characterName)
        End If
        
        ' Write slot name and make it bold
        augSheet.Cells(currentRow, 2).value = FormatSlotName(slotNames(i))
        augSheet.Cells(currentRow, 2).Font.Bold = True
        
        ' Write aug stats if exists (already sanitized when stored in dictionary)
        If augData.Exists(slotNames(i)) Then
            Dim stats() As Variant
            stats = augData(slotNames(i))
            
            ' stats(0) is item name, stats(1-11) are AC through HeroicCHA
            For j = 0 To UBound(stats)
                augSheet.Cells(currentRow, 3 + j).value = stats(j)
            Next j
        End If
    Next i
    
    ' Create table for columns B to N (Slot through HeroicCHA)
    ' Table includes header row and 21 data rows
    Set tableRange = augSheet.Range(augSheet.Cells(startRow, 2), augSheet.Cells(startRow + 21, 14))
    
    ' Create unique table name
    tableName = "Table_" & characterName & "_" & augSheet.Name & "_" & startRow
    
    ' Remove spaces and special characters from table name
    tableName = Replace(tableName, " ", "_")
    tableName = Replace(tableName, "-", "_")
    
    ' Create the table with no style
    On Error Resume Next
    Set newTable = augSheet.ListObjects.Add(xlSrcRange, tableRange, , xlYes)
    newTable.Name = tableName
    newTable.TableStyle = ""
    On Error GoTo 0
    
End Sub

Function GetSlotColumn(slotName As String) As Integer
    ' Map slot names to column numbers for base HP
    Select Case slotName
        Case "Chest": GetSlotColumn = 2
        Case "Head": GetSlotColumn = 3
        Case "Legs": GetSlotColumn = 4
        Case "Arms": GetSlotColumn = 5
        Case "Hands": GetSlotColumn = 6
        Case "LeftWrist": GetSlotColumn = 7
        Case "RightWrist": GetSlotColumn = 8
        Case "Face": GetSlotColumn = 9
        Case "Neck": GetSlotColumn = 10
        Case "Back": GetSlotColumn = 11
        Case "Shoulder": GetSlotColumn = 12
        Case "Waist": GetSlotColumn = 13
        Case "LeftEar": GetSlotColumn = 14
        Case "RightEar": GetSlotColumn = 15
        Case "LeftRing": GetSlotColumn = 16
        Case "RightRing": GetSlotColumn = 17
        Case "Charm": GetSlotColumn = 18
        Case "PowerSource": GetSlotColumn = 19
        Case "Primary": GetSlotColumn = 20
        Case "Secondary": GetSlotColumn = 21
        Case "Ranged": GetSlotColumn = 22
        Case Else: GetSlotColumn = 0
    End Select
End Function

Function FormatSlotName(slotName As Variant) As String
    ' Convert slot names to display format
    Select Case slotName
        Case "LeftWrist": FormatSlotName = "Left Wrist"
        Case "RightWrist": FormatSlotName = "Right Wrist"
        Case "LeftEar": FormatSlotName = "Left Ear"
        Case "RightEar": FormatSlotName = "Right Ear"
        Case "LeftRing": FormatSlotName = "Left Ring"
        Case "RightRing": FormatSlotName = "Right Ring"
        Case "PowerSource": FormatSlotName = "Power Source"
        Case Else: FormatSlotName = slotName
    End Select
End Function

' ===== SECURITY FUNCTION - PREVENTS FORMULA INJECTION =====
Function SanitizeValue(value As String) As String
    value = Trim(value)
    ' Prevent formula injection by prefixing with single quote
    If Len(value) > 0 Then
        If Left(value, 1) Like "[=+@-]" Then
            value = "'" & value
        End If
    End If
    SanitizeValue = value
End Function

Utility Equipment Export 1

Share this resource