I have the merge some date in Excel 2003 and convert the output file to a
csv-file. I have made a Visual Basic program, which import the data and
create a csv-file. However the csv-file must be semi-colon separated. I have
changed the Regional and Language settings, so the list separator is
semi-colon.

I have tried to save the Excel file as csv-file manually, and the result
looks OK. However, I want to use Visual Basic to do both the import of data
and creation of the csv-file. The problem is that Visual Basic creates the
csv-file as comma separated – no matter of my regional and language settings.
How can I get the output file as semi-colon separated?

Try the below macro to generate a file with semi-colon separated values.
Specify the number of columns and number of rows....or you can get the last
col and last row...Launch VBE using Alt+F11. Insert module and copy the below
code...Try with the active sheet...


Sub OutputAsSCV()

Dim lngRow As Long
Dim lngCol As Long
Dim lngLastRow As Long
Dim lngLastCol As Long
Dim intFile As Integer
Dim strData As String
intFile = FreeFile

lngLastRow = 2
lngLastCol = 3

Open "c:\test.txt" For Output As #intFile
For lngRow = 1 To lngLastRow
strData = ""
For lngCol = 1 To lngLastCol
strData = strData & ";" & Cells(lngRow, lngCol)
Next lngCol
Print #intFile, Mid(strData, 2)
Next lngRow
Close #intFile

End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Kirchler" wrote:

>
> I have the merge some date in Excel 2003 and convert the output file to a
> csv-file. I have made a Visual Basic program, which import the data and
> create a csv-file. However the csv-file must be semi-colon separated. I have
> changed the Regional and Language settings, so the list separator is
> semi-colon.
>
> I have tried to save the Excel file as csv-file manually, and the result
> looks OK. However, I want to use Visual Basic to do both the import of data
> and creation of the csv-file. The problem is that Visual Basic creates the
> csv-file as comma separated – no matter of my regional and language settings.
> How can I get the output file as semi-colon separated?
>

I don't know all the international settings but check you VBa help menu fro

FileFormat Property

the file format can be used with SAVEAS to specify the type of file you are
saving. Do you have in your worksheet menu File - SAVEAS an option to save
as semicolon delimited? If you have an option in the menu then there should
be an option in VBA SAVEAS.

"Kirchler" wrote:

>
> I have the merge some date in Excel 2003 and convert the output file to a
> csv-file. I have made a Visual Basic program, which import the data and
> create a csv-file. However the csv-file must be semi-colon separated. I have
> changed the Regional and Language settings, so the list separator is
> semi-colon.
>
> I have tried to save the Excel file as csv-file manually, and the result
> looks OK. However, I want to use Visual Basic to do both the import of data
> and creation of the csv-file. The problem is that Visual Basic creates the
> csv-file as comma separated – no matter of my regional and language settings.
> How can I get the output file as semi-colon separated?
>