Semi colon as csv separator

[usercogn]

Hi all,

I have a macro where i'm trying to convert an excel file to csv with semi-colon separator.
I thought the method was to convert to csv, and secondly to replace coma by semi-colon but it's not working.

It seems we can force the separator somehow, but I don't know how to do it inside my (already painful) code. below is mine :

    'variables
    Dim CompanyCode As String
    Dim Period As String
    Dim Chemin_INF As String
    Dim Nom_INF As String

Sub Data_Selection_INF()

    Sheets("INF").Select
    
    CompanyCode = Worksheets("INF").Range("A3")
    Period = Worksheets("INF").Range("C3")
    
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Select
    ActiveSheet.Paste

End Sub

Sub Export_csv_INF()

   ActiveSheet.Select
    
    Dim objSaveBox As FileDialog
    Set objSaveBox = Application.FileDialog(msoFileDialogSaveAs)
    
    With objSaveBox
        .InitialFileName = CompanyCode & "_INF_" & Period & ".csv"
        
        .FilterIndex = 15
        
        .Show

        .Execute
    End With
    
    Nom_INF = ActiveWorkbook.Name
    Chemin_INF = ActiveWorkbook.Path
    
    ActiveWorkbook.Close (Savechanges = False)

End Sub

Sub Compilation()

Data_Selection_INF
Export_csv_INF

MsgBox "File saved in the directory." 

End Sub

how to include the trick in my code?

thanks for ideas

 

[kpark91]

I wouldn't use Notepad to do the replace the comma by semi-colon.

Hi, AlphaFrog.
Purely out of curiousity, why wouldn't you use the notepad's functionality?

Is it inconsistent?

Thanks,
kpark

 

[usercogn]

..to be honest it's rather that I didn't understand them. So was unable to apply this to my case.
So I thought simply to open notepad and replace...but that's not a piece of cake...
Would you be so kind to comment the following code, please?

Const DELIMITER = ";"
Const MYFILE = "C:\Users\Bradley's\Desktop\test.txt"

Dim Last_Column As Integer
Dim Last_Row As Long
Dim Row_Loop As Long
Dim Column_Loop As Integer
Dim FileNum As Integer

FileNum = FreeFile

If Dir(MYFILE) <> "" Then
    If MsgBox(MYFILE & " will be deleted if you click OK", vbOKCancel) = vbCancel Then
        MsgBox ("Exiting Code")
        End
    End If
End If
    
With ActiveSheet.Cells
    Last_Column = .Find("*", [A1], , , xlByColumns, xlPrevious).Column
    Last_Row = .Find("*", [A1], , , xlByRows, xlPrevious).Row
End With


Open MYFILE For Output As #FileNum

For Row_Loop = 1 To Last_Row
    For Column_Loop = 1 To Last_Column
        Print #FileNum, ActiveSheet.Cells(Row_Loop, Column_Loop).Value & DELIMITER;
    Next Column_Loop
    Print #FileNum,
Next Row_Loop

Close #FileNum

End Sub

 

[AlphaFrog]

Purely out of curiousity, why wouldn't you use the notepad's functionality?

Is it inconsistent?

usercogn is trying to create a macro to save a range cells as a semicolon separated text file. It's unnessassary to use Notepad to replace the commas in a saved CSV text file. Excel's VBA can write the semicolon separated text file directly as the code in the example links ilistrate. Using Notepad would be extranious in my opinion.

 

[kpark91]

usercogn is trying to create a macro to save a range cells as a semicolon separated text file. It's unnessassary to use Notepad to replace the commas in a saved CSV text file. Excel's VBA can write the semicolon separated text file directly as the code in the example links ilistrate. Using Notepad would be extranious in my opinion.

Oh I see, that makes sense.
I must have just glanced over his requirements.

I thought he was trying to convert a CSV file to semicolon-delimited file the whole time.



Anyways, user, here is the commented code
If it doesn't help, I suggest you search through MS Help to fully understand the code.

Const DELIMITER = ";" 'set delimiter
Const MYFILE = "C:\Users\Bradley's\Desktop\test.txt" 'Your pathway to write your file to
'Initialization of Variables
Dim Last_Column As Integer
Dim Last_Row As Long
Dim Row_Loop As Long
Dim Column_Loop As Integer
Dim FileNum As Integer
FileNum = FreeFile
'Checking directory of MYFILE ("C:\Users\...")
If Dir(MYFILE) <> "" Then
    If MsgBox(MYFILE & " will be deleted if you click OK", vbOKCancel) = vbCancel Then
        MsgBox ("Exiting Code")
        End
    End If
End If
    
'Find the last row and last column of your activesheet and set them as Last_Column and Last_Row
With ActiveSheet.Cells
    Last_Column = .Find("*", [A1], , , xlByColumns, xlPrevious).Column
    Last_Row = .Find("*", [A1], , , xlByRows, xlPrevious).Row
End With
'Write stuff to MYFILE as an output (#FileNum is just a 'replacement' name for MYFILE)
Open MYFILE For Output As #FileNum
'Loop through each cells and print each cell into the output file with delimiter attached
For Row_Loop = 1 To Last_Row
    For Column_Loop = 1 To Last_Column
        Print #FileNum, ActiveSheet.Cells(Row_Loop, Column_Loop).Value & DELIMITER;
    Next Column_Loop
    Print #FileNum,
Next Row_Loop
Close #FileNum
End Sub

 

[AlphaFrog]

I think this will do what you want.

Sub Export_As_Semicolon_delimited()

    Dim objSaveBox As FileDialog
    Dim MYFILE As String
    Dim r As Long, c As Long
    
    Const DELIMITER = ";"
    
    Sheets("INF").Select
    
'    ChDrive = "C:\"     ' set default save drive
'    ChDir = "C:\Temp"   ' set default save directory (can't change default drive with just this)
    Set objSaveBox = Application.FileDialog(msoFileDialogSaveAs)
    With objSaveBox
        .InitialFileName = Range("A3") & "_INF_" & Range("C3") & ".csv"
        '.FilterIndex = 15
        If .Show = -1 Then
            MYFILE = .SelectedItems.Item(1)
        Else
            Exit Sub
        End If
    End With
    
    'Write cells from A2 to (last row, last column) to a Semicolon delimted text file
    FileNum = FreeFile
    Open MYFILE For Output As #FileNum
    For r = 2 To Range("A2").End(xlDown).Row
        For c = 1 To Range("A2").End(xlToRight).Column
            Print #FileNum, Cells(r, c).Value & DELIMITER;
        Next c
        Print #FileNum,
    Next r
    Close #FileNum
    
    MsgBox "File saved as a Semicolon delimited text file." & _
            vbLf & vbLf & MYFILE, , "Save Complete"

End Sub

 

[usercogn]

Thank you guys. That works perfectly, and seems to be quite quick to run.

 

[usercogn]

Is there any way to keep the same figures format as initial excel file when printing the csv or shall we define the format of every column in the loop?

Actually in the excel file, some columns contain figures that must be formatted as 0.00000(5decimals mandatory even if zero). But when printing the csv with this loop, the useless ending 0 are deleted...

thanks