在Excel中保留公式的情况下将工作表保存为CSV文件

Question

我完全使用Excel的VBA进行工作。我的解决方案必须完全是程序驱动的，而不是用户驱动的。该解决方案的要求是用户启动单个宏以将工作簿保存为8个单独的CSV文件，保留公式并丢弃公式的计算结果。我有一个表格数组（sht），我遍历它们并将它们保存。以下代码可以完美地实现这一点。

For i = LBound(sht) To UBound(sht)
    If (SheetExists(csv(i))) Then
        Sheets(sht(i)).SaveAs _
                fullpath & csv(i) & ".csv", _
                FileFormat:=xlCSV, _
                CreateBackup:=False
    End If
Next i

fullpath包含文件保存位置的完整路径，我编写了一个布尔函数来测试工作簿中是否存在该工作表。

问题：

我需要CSV文档包含Excel公式，而不是公式计算出的结果。公式的结果可以被丢弃。 Microsoft网站说：

如果单元格显示公式而不是公式值，则将公式转换为文本。所有格式、图形、对象和其他工作表内容都将丢失。欧元符号将被转换为问号。

这意味着SaveAs函数可能永远不会按照我的意愿执行，但我需要一些方法来创建该文件。理想情况下，我想保留Excel对CSV单元格的转义能力。CSV文件将由Java和SQL程序读取，这些程序可以根据需要正确解析Excel函数。

- Reivax

---

你打算如何处理合并单元格？如果公式包含换行符，导出前是否需要将其删除？你需要处理数组公式吗？ - Tim Williams

在运行SaveAs之前，您可以尝试激活每个工作表并添加“ActiveWindow.DisplayFormulas = True”来显示公式。 - Tim Williams

@Tim Williams。非常聪明。比我的建议更好。 - brettdj

@TimWilliams 很好。完美地解决了问题。翻出规格的细节真是一种技能。如果你回复一个正确的解决方案，我会将其标记为已采纳。 - Reivax

此外，几乎所有的公式都是工作表引用，因此没有进行重要的处理。没有数组数据，CSV可以通过用双引号括起来来转义包含逗号的单元格，并且还可以转义嵌入的双引号。 - Reivax

Answer 1

您可以尝试依次激活每个工作表，然后添加。

ActiveWindow.DisplayFormulas = True

在调用SaveAs之前。

- Tim Williams

Answer 2

您需要这样做，逐个单元格地将公式导出到csv文件中，而不是将每个工作表保存为CSV，这会剥离公式。

这段代码与我的回答类似Generate a flat list of all excel cell formulas 对于一个包含三个工作表的工作簿，它将创建名为

C:\temp\output1.csv C:\temp\output2.csv C:\temp\output3.csv

的文件。

VBA（添加了分隔符的转义）

Const sFilePath = "C:\temp\output"
Const strDelim = ","

Sub CreateTxt_Output()
Dim ws As Worksheet
Dim rng1 As Range
Dim X
Dim lRow As Long
Dim lCol As Long
Dim strTmp As String
Dim lFnum As Long
Dim lngCnt As Long
Dim strOut As String

lFnum = FreeFile
For Each ws In ActiveWorkbook.Worksheets
    lngCnt = lngCnt + 1
    Open (sFilePath & lngCnt & ".csv") For Output As lFnum
    'test that sheet has been used
    Set rng1 = ws.UsedRange
    If Not rng1 Is Nothing Then
        'only multi-cell ranges can be written to a 2D array
        If rng1.Cells.Count > 1 Then
            X = ws.UsedRange.Formula
            For lRow = 1 To UBound(X, 1)
                strOut = IIf(InStr(X(lRow, 1), strDelim) > 0, """" & X(lRow, 1) & """", X(lRow, 1))
                For lCol = 2 To UBound(X, 2)
                    'write each line to CSV
                    strOut = strOut & (strDelim & IIf(InStr(X(lRow, lCol), strDelim) > 0, """" & X(lRow, lCol) & """", X(lRow, lCol)))
                Next lCol
                Print #lFnum, strOut
            Next lRow
        Else
            Print #lFnum, IIf(InStr(rng1.Formula, strDelim) > 0, """" & rng1.Formula & """", rng1.Formula)
            End If
    End If
    Close lFnum
Next ws
MsgBox "Done!", vbOKOnly
End Sub

- brettdj

---

我记得在你之前的回答中看到过类似的模式，即逐行写入文件 - 那么先准备好整个内容再一次性写入不是更快吗？ - assylias

@assylias 不是的 - 原因是您正在将整个内容重复连接到越来越长的字符串中。在我的测试中，这段代码对于1000行5列的数据，一次性处理整个内容需要0.07秒，而单独处理每行只需要0.05秒。对于10,000条记录，前者需要0.2秒，而后者需要8秒。对于100,000条记录，前者需要3秒，而Excel则会卡死。 - brettdj

这是一个很棒的解决方案，如果@Tim Williams的解决方案失败了，我们可能会使用它。 - Reivax

Answer 3

除了上一个解决方案，你可以使用fileSystemObject和textStream来替代。在这个链接中查看有关写入文本文件的信息。

- Stephen Lloyd