prepare excel file for Salesforce upload through VBA

Adriaan_VBA

New Member
Joined
Dec 23, 2013
Messages
1
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 1Product 2Product 2Product 2
volumepricevolumeprice
Company A1000230004
Company B5000670008

<tbody>
</tbody>


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

ProductMetricValue
Company A1volume1000
Company A1price2
Company A2volume3000
Company A2price4
Company B1volume5000
Company B1price6
Company B2volume7
Company B2price8000

<tbody>
</tbody>

Any tips and tricks are helpful

Thanks,
Adriaan
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,377
example data matrix i have:
Product 1Product 2Product 2Product 2
volumepricevolumeprice
Company A1000230004
Company B5000670008

<tbody>
</tbody>

Just to check should this read:

Product 1Product 1Product 2Product 2
volumepricevolumeprice
Company A1000230004
Company B5000670008

<tbody>
</tbody>
 

Comfy

Well-known Member
Joined
Dec 21, 2009
Messages
3,377
Select your data and run this:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,939
Messages
5,525,739
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top