1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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?

Discussion in 'VBA Macros' started by Anjamen, Mar 14, 2019.

  1. Anjamen

    Anjamen New Member

    Messages:
    11
    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 (vb):
    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.

    Attached Files:

    Last edited by a moderator: Mar 14, 2019
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    Please use <> Code tag when posting code(s). Corrected it this time.

    I'd recommend that you upload sample workbook if you need help.
  3. Anjamen

    Anjamen New Member

    Messages:
    11
    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.

    Attached Files:

    • 1.xlsm
      File size:
      85.4 KB
      Views:
      3
  4. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    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.)
  5. Anjamen

    Anjamen New Member

    Messages:
    11
    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?
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,299
    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.
  7. Marc L

    Marc L Excel Ninja

    Messages:
    4,274
    Hi !

    A demonstration as a beginner starter
    (with column H empty - even hidden) :​
    Code (vb):
    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 !

Share This Page