0

i need to save a spreadsheet from excel as .csv file where delimiters are semicolons. Problem is that my macro saves the .csv file with commas as delimiters

here is code I used:(*s are used co cover directory path)

 sub test    
   Cells.Copy
            Workbooks.Add
                Range("A1").PasteSpecial
            ActiveWorkbook.SaveAs Filename:="*****" _ 
                & ActiveWorkbook.Name & ".csv", FileFormat:=xlCSV
            ActiveWorkbook.Close SaveChanges:=False
end sub

Funny thing is, that when I save the spreasheet manually, delimiters are OK. So I tried to record a Macro for that, this is Excel generated code:

Sub Makro2()

Cells.Select
   Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\****\Zošit17.csv", FileFormat:= _
    xlCSV, CreateBackup:=False
ActiveWindow.Close
End Sub

I changed nothing, added nothing, and when I run this code, the result is same as with code written by myself.

I wonder why it's happening because I have set system delimiters to ";" as list delimiter and "," as decimal delimiter, Also My Excel is set to use system delimiters.

thank you guys for help figure this out :)

m.siget
  • 43
  • 3

1 Answers1

0

How to save semi-colon delimited csv file using vba?

You need to add 

Local:=True

So:

ActiveWorkbook.SaveAs Filename:="C:\Users\****\Zošit17.csv", _
                      FileFormat:= xlCSV, CreateBackup:=False, _
                      Local:=True
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I tried that just now,and it didn't work...maybe there is something with my excel or something... – m.siget Mar 12 '19 at 18:25
  • 1
    When you close the workbook make sure to use `SaveChanges := False` - if the save repeats you'll get commas again... – Tim Williams Mar 12 '19 at 18:42