Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting Data from MAS500 into Excel problem | Excel Discussion (Misc queries) | |||
Problem exporting to excel with Windows XP | Excel Discussion (Misc queries) | |||
Exporting CSV file to unicode .txt file - " around strings | Excel Discussion (Misc queries) | |||
Exporting To Excel With ASP - Number Problem | Excel Programming | |||
Exporting to Excel using ASP Problem | Excel Programming |