Semi colon as csv separator

usercogn

New Member
Joined
Jun 19, 2011
Messages
8
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 :
Code:
    '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
 

usercogn

New Member
Joined
Jun 19, 2011
Messages
8
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

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
You have to concatenate the strings with "&"

so... It would be

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

usercogn

New Member
Joined
Jun 19, 2011
Messages
8
thank you, but I still have a syntax error on this line
Code:
Call Shell("C:\Windows\Notepad.exe" & Chemin_INF & " \ " & Nom_INF", vbMaximizedFocus)
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
You have an extra quote after Nom_INF" and spaces around the " \ "
Code:
Call Shell("C:\Windows\Notepad.exe " & Chemin_INF & "\" & Nom_INF, vbMaximizedFocus)
EDIT: also need a space after .exe "
 
Last edited:

usercogn

New Member
Joined
Jun 19, 2011
Messages
8
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
Code:
Cells.Replace What:=",", Replacement:=";"
does not have any effect. Is there another instruction to do find&replace with notepad ?

thanks
 

kpark91

Well-known Member
Joined
Jul 15, 2010
Messages
1,582
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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?
 

Forum statistics

Threads
1,081,690
Messages
5,360,624
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top