Problem exporting as csv file
 

Hi,

I have the following code that creates a csv file from an excel
worksheet, however when cell data contains a comma it should place
inverted commas around this cell data. It doesn't do this. For
example:

Rachel HR B 28/02/1978
Matthew Finance C 21/05/1981
Ian Bus, IT D 21/08/1977

The csv should look like:

Rachel,HR,B,28/02/1978
Matthew,Finance,C,21/05/1981
Ian,"Bus, IT",D,21/08/1977

When my code below runs, the inverted commas are missing, any ideas
please. x




Public Sub DoTheExport()
Dim FName As Variant

FName = Application.GetSaveAsFilename()
If FName = False Then
MsgBox "You didn't select a file", vbExclamation
Exit Sub
End If

Application.StatusBar = "Creating CSV file, please wait....."

ExportAsCSVFile CStr(FName)
End Sub

'FName = Temp file name to Print the data to

Public Sub ExportAsCSVFile(FName As String)

Dim ExpRng As Range
Set ExpRng = ActiveCell.CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count - 1
FirstRow = ExpRng.Rows(1).Row
LastRow = FirstRow + ExpRng.Rows.Count - 1

'Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum

For r = FirstRow To LastRow
For c = FirstCol To LastCol

vdata = ExpRng.Cells(r, c).Value

If c < LastCol Then
Print #FNum, vdata & ",";
Else
Print #FNum, vdata
End If

Next c
Next r

EndMacro:
On Error GoTo 0
'Application.ScreenUpdating = True
Close #FNum

MsgBox "CSV file created and saved under: " & _
FName, vbInformation, "job done"

Application.StatusBar = False


response = MsgBox("Do you want to create another CSV file?",
vbQuestion + vbYesNo)

If response = vbNo Then
Workbooks("CSVChecker.XLS").Close SaveChanges:=False
End If
If response = vbYes Then
Call ClearSheets
End If

End Sub

Problem exporting as csv file
 

If you're writing the file using Print#, you have to add the "s yourself.

Perhaps something like:

If Instr(vdata,",") Then vdata = """" & vdata & """"


In article ,
(Felicity Geronimo) wrote:

When my code below runs, the inverted commas are missing, any ideas
please. x

Problem exporting as csv file
 

why don't you just save the workbook as a .csv file?

ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\Owner\My Documents\Book2.csv",
FileFormat:=xlCSV, _
CreateBackup:=False

"Felicity Geronimo" wrote:

Hi,

I have the following code that creates a csv file from an excel
worksheet, however when cell data contains a comma it should place
inverted commas around this cell data. It doesn't do this. For
example:

Rachel HR B 28/02/1978
Matthew Finance C 21/05/1981
Ian Bus, IT D 21/08/1977

The csv should look like:

Rachel,HR,B,28/02/1978
Matthew,Finance,C,21/05/1981
Ian,"Bus, IT",D,21/08/1977

When my code below runs, the inverted commas are missing, any ideas
please. x




Public Sub DoTheExport()
Dim FName As Variant

FName = Application.GetSaveAsFilename()
If FName = False Then
MsgBox "You didn't select a file", vbExclamation
Exit Sub
End If

Application.StatusBar = "Creating CSV file, please wait....."

ExportAsCSVFile CStr(FName)
End Sub

'FName = Temp file name to Print the data to

Public Sub ExportAsCSVFile(FName As String)

Dim ExpRng As Range
Set ExpRng = ActiveCell.CurrentRegion
FirstCol = ExpRng.Columns(1).Column
LastCol = FirstCol + ExpRng.Columns.Count - 1
FirstRow = ExpRng.Rows(1).Row
LastRow = FirstRow + ExpRng.Rows.Count - 1

'Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

Open FName For Output Access Write As #FNum

For r = FirstRow To LastRow
For c = FirstCol To LastCol

vdata = ExpRng.Cells(r, c).Value

If c < LastCol Then
Print #FNum, vdata & ",";
Else
Print #FNum, vdata
End If

Next c
Next r

EndMacro:
On Error GoTo 0
'Application.ScreenUpdating = True
Close #FNum

MsgBox "CSV file created and saved under: " & _
FName, vbInformation, "job done"

Application.StatusBar = False


response = MsgBox("Do you want to create another CSV file?",
vbQuestion + vbYesNo)

If response = vbNo Then
Workbooks("CSVChecker.XLS").Close SaveChanges:=False
End If
If response = vbYes Then
Call ClearSheets
End If

End Sub