prepare excel file for Salesforce upload through VBA

[Adriaan_VBA]

I need to convert a data matrix in Excel to a csv-file and would like to use a VBA for it, as i need to multiple large sheets. I have searched the web but can't find solutions for it. Who can help me out here?

example data matrix i have:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 2[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]volume[/TD]
[TD]price[/TD]
[TD]volume[/TD]
[TD]price[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]1000[/TD]
[TD]2[/TD]
[TD]3000[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]5000[/TD]
[TD]6[/TD]
[TD]7000[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]


it needs to look like, so I can convert it to a CSV file for uploading it to Salesforce:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Product[/TD]
[TD]Metric[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]1[/TD]
[TD]volume[/TD]
[TD]1000[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]1[/TD]
[TD]price[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]2[/TD]
[TD]volume[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]2[/TD]
[TD]price[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]1[/TD]
[TD]volume[/TD]
[TD]5000[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]1[/TD]
[TD]price[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]2[/TD]
[TD]volume[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]2[/TD]
[TD]price[/TD]
[TD]8000[/TD]
[/TR]
</tbody>[/TABLE]

Any tips and tricks are helpful

Thanks,
Adriaan

 

[Comfy]

example data matrix i have:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 2[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]volume[/TD]
[TD]price[/TD]
[TD]volume[/TD]
[TD]price[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]1000[/TD]
[TD]2[/TD]
[TD]3000[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]5000[/TD]
[TD]6[/TD]
[TD]7000[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

Just to check should this read:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Product 1[/TD]
[TD]Product 1[/TD]
[TD]Product 2[/TD]
[TD]Product 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]volume[/TD]
[TD]price[/TD]
[TD]volume[/TD]
[TD]price[/TD]
[/TR]
[TR]
[TD]Company A[/TD]
[TD]1000[/TD]
[TD]2[/TD]
[TD]3000[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Company B[/TD]
[TD]5000[/TD]
[TD]6[/TD]
[TD]7000[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[Comfy]

Select your data and run this:

Sub CSVFormat()
    Dim rng As Range
    Dim rRow As Range
    Dim ws As Worksheet
    Dim i As Long
    Dim j As Long
    
    Set rng = Selection
    Set ws = Sheets.Add
    ws.Name = "Output"
    i = 2
    
    For Each rRow In rng.Rows
        If Not rRow.Cells(1, 1).Value = "" Then
            For j = 2 To rRow.Columns.Count
                ws.Cells(i, 1).Value = rRow.Cells(1, 1).Value
                ws.Cells(i, 2).Value = rng.Cells(1, j).Value
                ws.Cells(i, 3).Value = rng.Cells(2, j).Value
                ws.Cells(i, 4).Value = rRow.Cells(1, j).Value
            i = i + 1
            Next j
            
        End If
    Next rRow
End Sub

Note that it doesn't check to see if a worksheet already exists with the name "Output" and it doesn't add any headers in the output.