Saving Excel File as Comma-Quote Delimited

A

acollins

Board Regular
Joined
Aug 1, 2007
Messages
63
Hi Everyone. I have a tab-delimited file that I open with Excel, make a few tweaks to, then save as a comma-delimited file. However, the export file has to use quotes as a text qualifier as well, and I can't seem to find an option to add the quotes automatically when saving as a CSV file. Does anyone know how to achieve this. I must admit it has been many years since I have used Lotus 1-2-3, but I seem to remember it being quite easy to do in that program.

Since quotes are used quite commonly as a text qualifier in a delimited file, I find it hard to believe that Excel would not support this feature, but I can't find it if it does.

I don't have to do this that often, but often enough that I would really like to find a solution that doesn't involve me opening the CSV file and manually adding the quotes where necessary.

Any help would be much appreciated.

Alan
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
See if you can adapt this:

Code: 
Sub Test()
'   Change to suit
    Const FileName As String = "P:\TEMP\MrExcel\TextFiles\MyFile.csv"
    Dim FileNo As Integer
    Dim Rng As Range
    Dim x As Long
    Dim y As Integer
    Dim Txt As String
    FileNo = FreeFile
    Open FileName For Output As #FileNo
    Set Rng = Worksheets("Sheet1").Range("A1").CurrentRegion
    For x = 1 To Rng.Rows.Count
        Txt = ""
        For y = 1 To Rng.Columns.Count
            If Txt = "" Then
                Txt = """" & Rng.Cells(x, y) & """"
            Else
                Txt = Txt & "," & """" & Rng.Cells(x, y) & """"
            End If
        Next y
    Print #FileNo, Txt
    Next x
    Close #FileNo
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Setting the default save path with prompt
Replies
2
Views
798
CaptainGravyBum
C
I
  • Question Question
Searching multiple occurence of same keyword from text file and saving its output in different Excel worksheets
Replies
5
Views
337
myall_blues
myall_blues
J
  • Solved
VB help please - Save as pdf in specific folder
Replies
2
Views
101
JV0710
J
M
  • Question Question
Memory resource issue when opening and closing excel files
Replies
0
Views
277
Mike UK
M
P
  • Question Question
Combining PDF files from a folder using a list from excel (not importing to excel, combining as a single PDF)
Replies
2
Views
313
Patrick Desarzens
P

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top