Export CSV - VBA Novice

[PtitePomme]

Hi there,

Thanks for spending time considering my problem,

I'm new to VBA (and not familiar with coding in general)..
Anyway, my company wants me to create a VBA macro in order to export some columns of a sheet into a CSV file. (with semicolon separator)
This CSV file will then be used to add data in our ERP and need a specific format.

My problem is :

I have to copy columns : B, C, E, F & J.
I'm able to do this.
What I can't handle is that I need to paste data included in columns B & C into the same section (two columns into a new one), the same for E & F, J column is still single.
Then, I create a new "column" which will contain a "1" in each cell.

Example (with fake data) :
[TABLE="width: 500"]
<tbody>[TR]
[TD]Brand (B)[/TD]
[TD]Name (C)[/TD]
[TD]Power (E)[/TD]
[TD]Unit (F)[/TD]
[TD]Diesel/Petrol (J)[/TD]
[/TR]
[TR]
[TD]Porsche[/TD]
[TD]911 GT3[/TD]
[TD]415[/TD]
[TD]hp[/TD]
[TD]Petrol[/TD]
[/TR]
[TR]
[TD]Ferrari[/TD]
[TD][/TD]
[TD]500[/TD]
[TD]hp[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aston Martin[/TD]
[TD]V8 Vantage[/TD]
[TD][/TD]
[TD]hp[/TD]
[TD]Petrol[/TD]
[/TR]
[TR]
[TD]Citroën[/TD]
[TD]Xsara[/TD]
[TD]90[/TD]
[TD]cv[/TD]
[TD]Diesel[/TD]
[/TR]
</tbody>[/TABLE]

Output :
Porsche 911 GT3; 415hp; Petrol; 1
Citroen Xsara; 90cv; Diesel; 1

Another problem I cannot handle is that I must not paste rows for which one of the column is empty.

If I create a CSVMSDOS file (semicolon separator) I also have to check if I have an accented character and replace them, what I did doesn't work... (yes, I'm French )

Thanks if you can provide me some help!

Regards,

Quentin.

 

[alansidman]

Can a blank cell appear in columns B or F? or will they always be populated?

Not being able to speak, read or understand French, please list all characters which may be accented and the non accented equivalents you wish to replace them with.

 

[PtitePomme]

Each column may have blank cell, in this case, the complete line must be skipped only if column J is empty.

<code class="vb string">Here is the list :
"ÀÂÃÄÇÈÉÊËÎÏÔÖÙÛÜàáâäçèéêëîïôöùûü"</code>
"AAAACEEEEIOOUUUaaaaceeeeiioouuu"

But don't worry about accented characters, by using "local:=true" CSV file format uses semicolon (in France), and can handle accented characters.

Also, I made a mistake :

Output must be :
column(J); columns(E,F); columns(B,C); 1

Range is from 5 to 260, and will never change (before row 5, there are headers, after row 260, no data).

Thanks again for you help!

 

[alansidman]

Here is the code to consolidate the data and place it on sheet 2 of your workbook. I don't have time now to figure out how to replace the accented items.

Option Explicit


Sub csv()
    Dim lr As Long, i As Long, nxt As Long
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim bs As String, ds As String
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Sheet2")
    lr = sh1.Range("B" & Rows.Count).End(xlUp).Row
    
    For i = 2 To lr
    nxt = sh2.Range("A" & Rows.Count).End(xlUp).Row + 1
    With sh1
        If .Range("B" & i) <> "" Then
            If .Range("C" & i) <> "" Then
                If .Range("E" & i) <> "" Then
                    If .Range("F" & i) <> "" Then
                        If .Range("J" & i) <> "" Then
                        bs = .Range("B" & i) & .Range("C" & i)
                        ds = .Range("E" & i) & .Range("F" & i)
                        sh2.Range("A" & nxt) = bs
                        sh2.Range("B" & nxt) = ds
                        .Range("J" & i).Copy sh2.Range("C" & nxt)
                        sh2.Range("D" & nxt) = 1
                        End If
                    End If
                End If
            End If
        End If
    End With
    Next i
End Sub

 

[PtitePomme]

That's great!

Don't bother with accented characters, I've found the solution (won't work in every single country, but in France it works)

Thank you Alan!

Regards,

Quentin.