• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How To Save Worksheet Data As Csv File With / Without Double Quotes?

Anjamen

New Member
Hey everybody,

I'm trying to write a macro that will export one of the sheets from workbook as csv file. The sheet I want to export looks like this (picture 1). The problem I have is that I always get double quotes around my text when I open a exported csv file (picture 2). This is the macro that I use to create that csv file.

Code:
Sub Export_fak()

Application.DisplayAlerts = False
Dim xStr As String
Dim Path As String
Dim filename As String
strpath = Worksheets("Export").Range("J5").Value
filename = Range("AV1")
If Right(strpath, 1) <> Application.PathSeparator Then
        strpath = strpath & Application.PathSeparator
End If
Application.ScreenUpdating = False
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "New fak"
Sheets("Export").Select
Columns("G:I").Select
Selection.EntireColumn.Hidden = False
Range("H4").Select
Selection.Copy
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Range("H4:H" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Range("AW1").Select
Selection.Copy
Range("H4").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Range("A4").Select
Selection.Copy
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Range("A4:A" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Range("H4").Select
lastrow = Cells(Rows.Count, "C").End(xlUp).Row
Range("H4:H" & lastrow).Select
Selection.Copy
Sheets("New fak").Select
Range("A1").Select
xStr = ""
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("A1").Select
Sheets("New fak").Copy
ActiveWorkbook.SaveAs filename:=strpath & filename & ".csv", FileFormat:=xlCSV, _
        CreateBackup:=False
'If xFile = "" Then
'MsgBox "U folderu nema fajlova", vbInformation, "Obaveštenje"
    'Exit Sub
'End If
ActiveWorkbook.Close
Sheets("New fak").Delete
Sheets("Export").Select
Range("H4").Select
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
Range("H4:H" & lastrow).Select
Selection.ClearContents
Range("AW1").Select
Selection.Copy
Range("H4").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Range("A4").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A4:A" & lastrow).Select
Selection.ClearContents
Range("AW2").Select
Selection.Copy
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Range("A1").Select
Columns("H:H").Select
Selection.EntireColumn.Hidden = True
Range("C1").Select

Any suggestion how to solve this problem?

-------------------------------------------------------------------------------------------
MOD EDIT: Added code tag.
 

Attachments

  • Picture 1.png
    Picture 1.png
    44.1 KB · Views: 16
  • Picture 2.png
    Picture 2.png
    115.3 KB · Views: 19
Last edited by a moderator:
Please use <> Code tag when posting code(s). Corrected it this time.

I'd recommend that you upload sample workbook if you need help.
 
Sorry, I don't often use help from this forum so I don't know how to post macro like you correct me. I'm not sure did you mean to put a file that I use or something else so I posted a file which I use to export csv file.

Also, thanks for helping me last time I posted question here, about 2 weeks ago when I was looking a solution for importing unknown number of txt files with macro. I found some macro on the internet, correct it a little bit and it works.
 

Attachments

  • 1.xlsm
    85.4 KB · Views: 5
Ok, there is one major issue here.

You are saving as CSV. Which uses "," as separator. However, you are using pipeline "|" as separator in your code.

Is this intended?

If it isn't, then you should consider either changing separator, or changing format the file is saved as.

If it is intended to use "|" as separator. Then I'd recommend altering code logic. Currently you will have issue when region setting is changed (i.e. you may find unexpected value exported for Column E values.)
 
My client already made application which will input my csv file in other program and it use "|" as separator and, also, he use "," as decimal separator so I can't change neither "|" or ",". Any suggestion how to change code so I could get same result?
 
I'm bit busy. I'll try and look at it tomorrow.
But you should export out as .txt file rather than csv.

If it needs to remain csv. Then rather than use saveas. I'd use another method to write to file.
 
Hi !​
A demonstration as a beginner starter (with column H empty - even hidden) :​
Code:
Sub Demo()
    FF% = FreeFile
    Open ThisWorkbook.Path & "\Export .txt" For Output As #FF
For Each V In Sheet8.[A3].CurrentRegion.Rows
    Print #FF, Join(Application.Index(V.Value2, 0), "|")
Next
    Close #FF
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Back
Top