Convert excel to CSV format using a join

[JoeRooney]

Hi

I am hoping someone can help me with the below. I have the below code and it almost doing what I need it to do

The below code will join my columns like this:

policy_id;"trans_process_date;"trans_type;"trans_reason;

But I need it to join them like this

policy_id;"trans_process_date";"trans_type";"trans_reason";

I need the ; separator to come after the " when joining each column.

I am sure it is obvious what needs to be changed but I cant work it out , any help with this is greatly appreciated.

Thanks

Sub V1_Policy_Creator()

    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "V.1 Policy"
    
    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Range("B" & Rows.Count).End(xlUp).Row

    For i = 2 To Lastrow
        Range("A" & i).Formula = Join(Application.Index(Range("B" & i & ":DE" & i).Value, 1, 0), ";" & """")
    Next i

 

[Fluff]

How about

        Range("A" & i).Formula = Join(Application.Index(Range("B" & i & ":DE" & i).Value, 1, 0), """;""")

 

[JoeRooney]

works perfect , thank you

 

[Fluff]

You're welcome & thanks for the feedback.

 

[JoeRooney]

Hi I need to make a slight adjustment to the below code

The problem is on the first cell , the codes reports it as:

policy_id;";"trans_process_date";"trans_type";

But I need to report it as

policy_id;"trans_process_date";"trans_type";

It is only affecting the first cell the rest of the range is perfect.

The code for V1_Policy_Creator2() was missing the ; after the first cell so I thought if I add it before i run the V1_Policy_Creator2() code this would resolve it but unfortunately not.

Sub V1_Policy_Creator1()

 Dim i As Long
    Dim Lastrow As Long
    Lastrow = Range("B" & Rows.Count).End(xlUp).Row

 Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    Selection.NumberFormat = "General"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&"";"""
    Range("A1").AutoFill Destination:=Range("A1:A" & Lastrow)
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    
    Call V1_Policy_Creator2
    
    End Sub
    
Sub V1_Policy_Creator2()

Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "TOM Policy"
    
    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Range("B" & Rows.Count).End(xlUp).Row
    

    For i = 2 To Lastrow
    
         Range("A" & i).Formula = Join(Application.Index(Range("B" & i & ":CS" & i).Value, 1, 0), """;""")
        
    Next i
    
    End Sub

 

[Fluff]

How about

        Range("A" & i).Formula = Range("B" & i) & Chr(34) & Join(Application.Index(Range("c" & i & ":DE" & i).Value, 1, 0), """;""")

 

[JoeRooney]

Hi Fluff sorry for the delay on your feedback. Unfortunaltey it is still missing the ; after policy_id

policy_id"trans_process_date";"trans_type";

 

[Fluff]

I thought you had added that with the other code. If not try

Range("A" & i).Formula = Range("B" & i) & ";" & Chr(34) & Join(Application.Index(Range("c" & i & ":DE" & i).Value, 1, 0), """;""")

 

[JoeRooney]

Perfect , thanks so much

 

[Fluff]

You're welcome & thanks for the feedback.