Wrap quotes around cell values in a CSV

[jae113]

have an excel spreadsheet that I need to save as a CSV file to import into another application. For this other application, the values in the CSV file must be wrapped in quotes (which Excel does not do by default). So I need each row to look like this:

"value 1", "value 2", "value 3"

instead of

value 1, value 2, value 3

 

[Andrew Poulsom]

See if you can adapt this:

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

 

[jae113]

I'm sorry, I'm not familiar with macros. Could you give me the steps for entering this?

 

[Andrew Poulsom]

Open your workbook and press Alt+F11 to go to the Visual Basic Editor. Click your workbook in the Project window and choose Insert|Module from the menu. Copy the code from the Board and paste it into the window on the right. Change the FileName constant to suit. The code assumes that your data is on Sheet1 starting at A1, so you may need to adjust those references as well.

Press Alt+F11 to return to your workbook, press Alt+F8, select the macro and click Run.

 

[mieniestereikseil]

I get

Run-time error '70':
Permission denied

If I click debug,
Open FileName For Output As #FileNo
is indicated.

 

[Andrew Poulsom]

Did you change the FileName constant?