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

 

[VoG]

This may give you some ideas http://www.cpearson.com/excel/imptext.aspx

 

[AlphaFrog]

More ideas:

Exporting excel to semicolon delimited text file

Save a file in "text" format with ";"delimiter

 

[usercogn]

thanks. I'm now trying to open notepad and just replace by. However I do not manage to open a file that have path&name as variables.

<code>
Call Shell("C:\Windows\Notepad.exe" Chemin_INF + " \ " + Nom_INF", vbMaximizedFocus)
</code>

where Chemin_INF + " \ " + Nom_INF" is the path+name, but it seems the syntax to open with shell instruction is not correct.

I thought this would have been quite easy to do, but it's starting to be a real pain.

 

[kpark91]

You have to concatenate the strings with "&"

so... It would be

Call Shell("C:\Windows\Notepad.exe" & Chemin_INF & " \ " & Nom_INF", vbMaximizedFocus)

 

[usercogn]

thank you, but I still have a syntax error on this line

Call Shell("C:\Windows\Notepad.exe" & Chemin_INF & " \ " & Nom_INF", vbMaximizedFocus)

 

[AlphaFrog]

You have an extra quote after Nom_INF" and spaces around the " \ "

Call Shell("C:\Windows\Notepad.exe " & Chemin_INF & "\" & Nom_INF, vbMaximizedFocus)

EDIT: also need a space after .exe "

 

[usercogn]

That's fine it works now, it opens the file.
If it's not too much, can I ask you how to replace the comma by semi-colon now, since

Cells.Replace What:=",", Replacement:=";"

does not have any effect. Is there another instruction to do find&replace with notepad ?

thanks

 

[kpark91]

The reason why the code is not working is because you have specified a delimiter when you're importing the text file onto your worksheet.

A) You can import without specifying any delimiters (but most likely you will run into a problem if your number of characters in a single line is over 256 characters)
B) Use a VBA code that reads a text file line by line and replace the commas with semicolons.
C) Ctrl+H in text editor to replace all "," with ";"

 

[AlphaFrog]

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

The links above gave working code examples on how to do this. Was there a problem incorporating that code?