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:

	Product 1	Product 2	Product 2	Product 2
	volume	price	volume	price
Company A	1000	2	3000	4
Company B	5000	6	7000	8

<tbody>
</tbody>

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

	Product	Metric	Value
Company A	1	volume	1000
Company A	1	price	2
Company A	2	volume	3000
Company A	2	price	4
Company B	1	volume	5000
Company B	1	price	6
Company B	2	volume	7
Company B	2	price	8000

<tbody>
</tbody>

Any tips and tricks are helpful

Thanks,
Adriaan

 

[Comfy]

example data matrix i have:

	Product 1	Product 2	Product 2	Product 2
	volume	price	volume	price
Company A	1000	2	3000	4
Company B	5000	6	7000	8

<tbody>
</tbody>

Just to check should this read:

	Product 1	Product 1	Product 2	Product 2
	volume	price	volume	price
Company A	1000	2	3000	4
Company B	5000	6	7000	8

<tbody>
</tbody>

 

[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.