excel - 用双引号封装输出

Question

我编写了一个接受 CSV 输入文件的宏，用硬编码调整更新一些字段，然后再次将文件另存为 CSV。

所有这些的代码都有效。但是，输出文件需要在所有字段周围加上引号才能被我们专有的 GUI 正确读取。

例如一行包含:TestStrips1, 1, 0.8, 0, -0.2

需要格式化为:“TestStrips1”、“1”、“0.8”、“0”、“-0.2”

这是我的代码中与实现这种情况有关的部分。使用硬编码的行/列编号，因为它们不会改变。 cellHold 和 newCell 与上面的变体一样是 DIMd，我将它们用作使连接按预期工作的一种方式:

For i = 1 To 5
    For j = 1 To 40
        cellHold = NewBook.Sheets(1).Cells(j, i).Value
        'NewBook.Sheets(1).Cells(j, i).NumberFormat = "@"
        newCell = Chr(34) & cellHold & Chr(34)
        NewBook.Sheets(1).Cells(j, i) = newCell
    Next j
Next i
If Dir(fPath & "OffsetCoordinates_orig.csv") <> "" Then Kill (fPath & "OffsetCoordinates_orig.csv")
wbOrig.SaveAs Filename:=fPath & "OffsetCoordinates_orig.csv", FileFormat:=xlCSV
wbOrig.Close
If Dir(fPath & "OffsetCoordinates.csv") <> "" Then Kill (fPath & "OffsetCoordinates.csv")
NewBook.SaveAs Filename:=fPath & "OffsetCoordinates.csv", FileFormat:=xlCSV
NewBook.Close
MsgBox ("Your Offset file has been updated successfully. Please see " & fPath & " for your new file.")  

无论是否将数字格式设置为字符串，我都试过了，它似乎不会影响输出。令人困惑的是，这段代码产生的输出在 Excel 中查看时实际上看起来是正确的(每个单元格周围的引号)，但是当使用 Notepad++ 查看时，实际上每个项目周围都有三重引号，如下所示:

"""TestStrips1""","""1""","""-1.2""","""0.6""","""0.4"""

当我查看我试图模拟的父文件时，在 Excel 中查看时，单元格中没有引号，但在记事本++ 中，输出符合预期，每个项目都带有引号。

我不清楚这是格式问题，还是 Excel 添加了额外的引号字符。

---

主要是通过 Tim 指出的以下代码解决的，其他答案看起来也很有用，但这首先完成了它。

    For i = 1 To 5
        For j = 1 To 40
            cellHold = NewBook.Sheets(1).Cells(j, i).Value
            NewBook.Sheets(1).Cells(j, i).NumberFormat = "@" 'not necessary?
            newCell = cellHold
            NewBook.Sheets(1).Cells(j, i) = newCell
            Debug.Print (NewBook.Sheets(1).Cells(j, i).Value)
        Next j
    Next i
    If Dir(fpath & "OffsetCoordinates_orig.csv") <> "" Then Kill (fpath & "OffsetCoordinates_orig.csv")
    wbOrig.SaveAs Filename:=fpath & "OffsetCoordinates_orig.csv", FileFormat:=xlCSV
    wbOrig.Close
    If Dir(fpath & "OffsetCoordinates.csv") <> "" Then Kill (fpath & "OffsetCoordinates.csv")
'    NewBook.SaveAs Filename:=fPath & "OffsetCoordinates.csv", FileFormat:=xlCSV
    Application.ActiveSheet.Range("A1:E40").Select
    Call QuoteCommaExport(fpath)
    Application.DisplayAlerts = False
    NewBook.Close
    Application.DisplayAlerts = True
    MsgBox ("Your Offset file has been updated successfully. Please see " & fpath & " for your new file.")
End Sub

Sub QuoteCommaExport(fpath)
'Comments from Microsoft's solution 
' Dimension all variables. 
    Dim DestFile As String
    Dim FileNum As Integer
    Dim ColumnCount As Integer
    Dim RowCount As Integer

    ' Prompt user for destination file name.
    DestFile = fpath & "OffsetCoordinates.csv"

    ' Obtain next free file handle number.
    FileNum = FreeFile()

    ' Turn error checking off.
    On Error Resume Next

    ' Attempt to open destination file for output.
    Open DestFile For Output As #FileNum

    ' If an error occurs report it and end.
    If Err <> 0 Then
        MsgBox "Cannot open filename " & DestFile
        End
    End If

    ' Turn error checking on.
    On Error GoTo 0

    ' Loop for each row in selection.
    For RowCount = 1 To 40

        ' Loop for each column in selection.
        For ColumnCount = 1 To 5

            ' Write current cell's text to file with quotation marks.
            Print #FileNum, """" & Selection.Cells(RowCount, _
              ColumnCount).Text & """";

            ' Check if cell is in last column.
            If ColumnCount = Selection.Columns.Count Then
                ' If so, then write a blank line.
                Print #FileNum,
            Else
                ' Otherwise, write a comma.
                Print #FileNum, ",";
            End If
        ' Start next iteration of ColumnCount loop.
        Next ColumnCount
    ' Start next iteration of RowCount loop.
    Next RowCount

    ' Close destination file.
    Close #FileNum
End Sub

Microsoft 提供的代码(参见子 QuoteCommaExport)主要按预期工作，除了我得到非常奇怪的行为，其中日期被错误地复制到输出“csv”文件中。该单元格没有显示在源文件中，而是被复制为“#######”。我意识到，当我逐步执行代码时，我有时会手动调整带有日期的列的大小以适合断点(以确保单元格中的日期正确，而不仅仅是一系列 # 字符)。每当我这样做时，它都会正确复制内容。所以代码复制的是显示的字符而不是单元格的内容。在调用 Sub 之前调整列的大小修复了该行为。

Answer 1

您可以使用 Powershell 在数字周围添加引号。

• 如果要覆盖，需要关闭文件
• 该文件必须包含此解决方案的标题行
• 打开文件并从 Excel 中保存将删除双引号

---

Sub AddQuotesToCSV(ByVal FileName As String, Optional ByVal NewFileName As String)
    Const PowershellCommand As String = "Powershell " & vbNewLine & _
        "$P = Import-Csv -Path '@FileName'" & vbNewLine & _
        "$P | Export-Csv -Path '@NewFileName' -NoTypeInformation -Encoding UTF8"
    Dim Command As String

    Command = Replace(PowershellCommand, "@FileName", FileName)
    Command = Replace(Command, "@NewFileName", IIf(Len(NewFileName) > 0, NewFileName, FileName))

    CreateObject("WScript.Shell").Exec Command
End Sub