VBA script to export excel to cvs

[rasticle]

I have the script running perfectly, but there is just one problem. In the event that someone enters a value with a comma in a cell it prints the csv file incorrectly.

Here is the code:

Public Sub WriteSheet(curSheet As Worksheet, pathstr As String, col As Long, row As Long)

Dim txtStr As String
Dim x As Long
Dim y As Long
Dim i As Long
Dim StateCol As Long
Dim CountyCol As Long

On Error GoTo err_handler

FirstFlag = True

For y = 1 To row
txtStr = ""
For x = 1 To col
txtStr = txtStr & "," & curSheet.Cells(y, x).Text
Next x
'write out the file
txtStr = Right(txtStr, Len(txtStr) - 1)
Call writetxt(pathstr, txtStr)
Next y


Exit Sub
err_handler:

MsgBox Err.Description

End Sub

Thanks for any help!

 

[SkipVought]

Why are you writing a file?

Why not just SaveAs as .csv?

Skip,

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

 

[rasticle]

It is for a template to pass out to people to put data in a format for entry into a database. It is just easier to give people a button to push.

 

[SkipVought]

You seem to be using the rows and columns in order in a sheet.

Why not Macro Record saveas .csv?

Skip,

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

 

[HughLerwill]

I note your routine saves out a range of the sheet to the csv. If the nrows and ncols always represent all rows and all cols of the cursheet then using SaveAs like Skip suggests is the way to go; your code should be reduced to a single line something like;

ActiveWorkbook.SaveAs Filename:="C:\Users\MyName\Documents\Book1.csv", _
FileFormat:=xlCSV, CreateBackup:=False

Excel automatically will place any text fields in quote marks so that column formats are not disturbed by embedded commas.

Otherwise manually wrap text in quotes in your existing code; replace your;

txtStr = txtStr & "," & curSheet.Cells(y, x).Text

with someting like;

If IsNumeric(curSheet.Cells(y, x).Text) Then
txtStr = txtStr & "," & curSheet.Cells(y, x).Text
Else
txtStr = txtStr & "," & """ & curSheet.Cells(y, x).Text & """
End If

Take care with this if your code has to cater for international use because some regions use a comma as the decimal point character.