vba macro for saving excel in csv

[Nifrabar]

Hi,

I need to save an excelsheet as csv file but with seperator being a semicolon.
Also I don't want any window to pop up during execution of this save macro, even if there is already an existing file which may be overwritten.

Any suggestions?

TIA
-Bart

 

[SkipVought]

hi,

You can turn on your macro recorder for parts of this.

Add a new sheet.

In 2 loops, read down and across to create a ONE COLUMN result of each value separated by you delimiter.

Save the sheet as fileformat xlTextPrinter extension .prn

delete the sheet

use Application.displayalerts = false to supress messages

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I need to save an excelsheet as csv file but with seperator being a semicolon."
That is like saying: I want to save my JPG file as a music file.

csv is Comma Separated Value

Have fun.

---- Andy

 

[PHV]

csv is Comma Separated Value
Yes, in the USA.
In fact the xlCSV FileFormat use the local list separator, eg the semicolon in Europe.

 

[SkipVought]

Well then, in that case, since the OP seems to be European, simply SaveAs using xlCSV as the fileformat. and use application.displayalerts = false

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Nifrabar]

Hi!
Yes my Q is indeed misleading. I should have asked for saving a sheet as textfile which looks like a csv but with semicolon as delimiter. True, I'm from the Netherlands. Can I also set the type of delimiter within the macro to semicolon?
TIA
-Bart

 

[SkipVought]

Why don't you try simply doing a manual SaveAs .csv filetype and see what the results are when you open that file in NotePad, for instance.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Or you could just write the data yourself to a text file with whateever you want as delimiter.

Let's say you want columns A-F, first 100 rows, write into C:\TEMP\Test.csv

Dim i As Integer

Open "C:\TEMP\Test.csv" For Output As #1

For i = 1 To 100
    Print #1, Range("A" & i).Value & ";" & _
              Range("B" & i).Value & ";" & _
              Range("C" & i).Value & ";" & _
              Range("D" & i).Value & ";" & _
              Range("E" & i).Value & ";" & _
              Range("F" & i).Value
Next i

Close #1

Have fun.

---- Andy

 

[Nifrabar]

Skip,

I tried save as CSV as well as CSV for MSDOS.
The last gives weird results. Sometimes a textfile with comma as sepeartor while another time it results in semicolon seperator.

Now I feel Andy's option is most reliable.

-Bart