Non riesco a salvare un foglio excel in formato csv con separatore ;

Question

Non riesco a salvare un foglio excel in formato CSV con separatore (;) punto e virgola

Questo il codice VBA

ActiveWorkbook.SaveAs Filename:= _

    "C:\Users\test\_screening\Documents\TAMPONI\" & nomefile, FileFormat:= \_ 

    xlCSVMSDOS, CreateBackup:=False 

ActiveWorkbook.Close SaveChanges:=True 

mi salva sempre con separatore (,) virgola

anche con questo codice vba

ActiveWorkbook.SaveAs Filename:= _

        "C:\Users\Gianmario\OneDrive\Documenti\rapidi.csv", FileFormat:=xlCSV, _

        CreateBackup:=False

Ho tentato in tutti i modi ma di da sempre il separatore (,) virgola

Grazie per le gentili risposte

Answer 1

Ciao Gianmario,

Good morning Norman

Last thing

I noticed that:

Double quotes must be removed.

where should I act on the code

Nella macro ConvertireSeparatorePerCSV , sostituisci la riga:

sStr = sStr & """" & rCell.Value & """" & sSeparatoreVoluto

con:

sStr = sStr & rCell.Value & sSeparatoreVoluto

===

Regards,

Norman

Answer 2

I also see the file with separator;

I am attaching the code

'Salva molecolari 

ActiveWorkbook.RefreshAll

nomefile = Format(Date, "yyyymmdd") + "_" + Format(Time, "hhmm") + "_MOLECOLARI" + ".csv"

ActiveWorkbook.RefreshAll 

  Sheets("MOLECOLARI").Select 

Sheets("MOLECOLARI").Copy 

'****************************************

 Dim srcRng As Range 

Dim rRow As Range 

Dim rCell As Range 

Dim sStr As String 

Dim FName As Variant 

Const sSeparatoreVoluto As String = ";" 

FName = Application.GetSaveAsFilename(nomefile, "CSV File (\*.csv), \*.csv") 

If FName <> False Then 

If Selection.Cells.Count > 1 Then 

Set srcRng = Selection 

Else 

Set srcRng = ActiveSheet.UsedRange 

End If 

Open FName For Output As #1 

For Each rRow In srcRng.Rows 

sStr = "" 

For Each rCell In rRow.Cells 

sStr = sStr & """" & rCell.Value & """" & sSeparatoreVoluto 

Next 

While Right(sStr, 1) = sSeparatoreVoluto 

sStr = Left(sStr, Len(sStr) - 1) 

Wend 

Print #1, sStr 

Next 

Close #1 

End If 

ActiveWorkbook.SaveAs Filename:= _

**"C:\Users\test\_screening\Documents\TAMPONI\" & nomefile, FileFormat:= \_** 

xlCSV, Local:=False, CreateBackup:=False

ActiveWorkbook.Close SaveChanges:=True

I already save the file in the right directory, but then the macro stops to ask for the file name again

by

gianmario

Answer 3

Ciao Gianmario,

After transforming the separator file (;) with your code, I need to save it

which instruction should I use

Because if I use my code it still saves it with the (,) comma

Io ho esguito il codice sul seguente foglio, passando alla macro il nome del file myTest:

Aprendo il file Test.csv con Blocco note, io vedo i dati separati da punto e virgola:

===

Regards,

Norman

Answer 4

After transforming the separator file (;) with your code, I need to save it

which instruction should I use

Because if I use my code it still saves it with the (,) comma

Buona giornata

Gianm<rio Polenghi

Bergamo

Answer 5

Ciao Gianmario,

Non riesco a salvare un foglio excel in formato CSV con separatore (;) punto e virgola

Questo il codice VBA

ActiveWorkbook.SaveAs Filename:= _

"C:\Users\test_screening\Documents\TAMPONI" & nomefile, FileFormat:= _

xlCSVMSDOS, CreateBackup:=False

ActiveWorkbook.Close SaveChanges:=True

mi salva sempre con separatore (,) virgola

anche con questo codice vba

ActiveWorkbook.SaveAs Filename:= _

        "C:\Users\Gianmario\OneDrive\Documenti\rapidi.csv", FileFormat:=xlCSV, _

        CreateBackup:=False

Ho tentato in tutti i modi ma di da sempre il separatore (,) virgola

Grazie per le gentili risposte

Prova qualcosa del genere:

'=========>>

Option Explicit

'--------->>

Public Sub ConvertireSeparatorePerCSV()

Dim srcRng As Range 

Dim rRow As Range 

Dim rCell As Range 

Dim sStr As String 

Dim FName As Variant 

Const sSeparatoreVoluto As String = ";" 

FName = Application.GetSaveAsFilename("", "CSV File (\*.csv), \*.csv") 

If FName <> False Then 

    If Selection.Cells.Count > 1 Then 

        Set srcRng = Selection 

    Else 

        Set srcRng = ActiveSheet.UsedRange 

    End If 

    Open FName For Output As #1 

    For Each rRow In srcRng.Rows 

        sStr = "" 

        For Each rCell In rRow.Cells 

            sStr = sStr & """" & rCell.Value & """" & sSeparatoreVoluto 

        Next 

        While Right(sStr, 1) = sSeparatoreVoluto 

            sStr = Left(sStr, Len(sStr) - 1) 

        Wend 

        Print #1, sStr 

    Next 

    Close #1 

End If 

End Sub

'<<=========

===

Regards,

Norman