将Excel文件另存为CSV VBA时使用三引号 [英] Triple Quotes when saving an excel file as csv VBA

问题描述

将excel文件另存为CSV时，我在每个单元格上都加了三引号，它必须像 Hello World ，但我却获得了 Hello World ，当我打开CSV文件时。 CSV以逗号分隔

I'm getting triple quotes on each cell when saving an excel file as CSV, it needs to be like "Hello World" but I'm getting """Hello World""" when I open the CSV file. the CSV is comma delimited

编辑：如果我不加引号保存它，它的保存就像 Hello world

edit: if I save it without the quotes, its save like Hello world

Do While xExcelFile <> ""
    newFileName = Replace(xExcelFile, " ", "_")

'*****************************************************
    For Each c In Range("A1").CurrentRegion
        If Not IsNumeric(c.Value) Then
            c.Value = Chr(34) & c.Value & Chr(34)
        End If
    Next c
'******************************************************
    'Saving file as csv
SaveFile:
    ActiveWorkbook.SaveAs fileName:=xSPath & newFileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close

    'workbooks transformed log into cells
    Cells(cont, 6).Value = newFileName
    cont = cont + 1

NextLoop:
    'next file
    xExcelFile = Dir
Loop

推荐答案

尝试一下

Sub test()
    Dim xSpath As String
    Dim newFileName As String
    Dim rngDB As Range
    xSpath = ThisWorkbook.Path & "\"
    newFileName = "test1"

    Set rngDB = Range("a1").CurrentRegion
    TransToCsv rngDB, XPath & newFileName & ".csv"
End Sub

Sub TransToCsv(rngDB As Range, strFile As String)
    Dim vDB, vR() As String, vTxt()
    Dim i As Long, j As Integer
    Dim objStream
    Dim strTxt As String

    Set objStream = CreateObject("ADODB.Stream")

    vDB = rngDB

    For i = 1 To UBound(vDB, 1)
        n = n + 1
        ReDim vR(1 To UBound(vDB, 2))
        For j = 1 To UBound(vDB, 2)
            If IsNumeric(vDB(i, j)) Then
                vR(j) = vDB(i, j)
            Else
                vR(j) = Chr(34) & vDB(i, j) & Chr(34)
            End If
        Next j
        ReDim Preserve vTxt(1 To n)
        vTxt(n) = Join(vR, ",")
    Next i
    strTxt = Join(vTxt, vbCrLf)
    With objStream
        '.Charset = "utf-8"
        .Open
        .WriteText strTxt
        .SaveToFile strFile, 2
        .Close
    End With
    Set objStream = Nothing

End Sub

这篇关于将Excel文件另存为CSV VBA时使用三引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！