vba 使用完整的 Excel 公式将工作表保存为 CSV
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9491719/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Saving worksheets to CSV with Excel Formulas intact
提问by Reivax
I'm working entirely in VBA for Excel. My solution must be entirely programmatic, and not user driven. The requirement for the solution is the user initiates a single macro to take workbook and save the 8 sheets into separate CSV files, preserving the formulas and discarding formula resolutions. I have an array of sheets (sht) that I iterate through, and save them. The following code does this perfectly.
我完全在 VBA for Excel 中工作。我的解决方案必须完全是程序化的，而不是用户驱动的。解决方案的要求是用户启动单个宏来获取工作簿并将 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
Where fullpath contains the entire path to the file save location, and I have written a boolean function that tests to see if the sheet exists in the workbook.
其中 fullpath 包含文件保存位置的完整路径，我编写了一个布尔函数来测试工作簿中是否存在工作表。
The Problem:
问题：
I need the CSV documents to contain the Excel formulas, not what the formulas evaluate to. The results of the formulas can be discarded. The Microsoft websitesays:
我需要 CSV 文档来包含 Excel 公式，而不是公式的计算结果。公式的结果可以被丢弃。 微软网站说：
If cells display formulas instead of formula values, the formulas are converted as text. All formatting, graphics, objects, and other worksheet contents are lost. The euro symbol will be converted to a question mark.
如果单元格显示公式而不是公式值，则公式将转换为文本。所有格式、图形、对象和其他工作表内容都将丢失。欧元符号将转换为问号。
This means the SaveAs function will probably never do what I want it to do, but I need some way to create the file. Ideally, I would like to keep Excel's ability to escape the CSV cells in tact as well. The CSV files will be read by Java and SQL programs that can properly parse the Excel functions as needed.
这意味着 SaveAs 函数可能永远不会做我想要它做的事情，但我需要某种方式来创建文件。理想情况下，我也希望保持 Excel 转义 CSV 单元格的能力。CSV 文件将由 Java 和 SQL 程序读取，这些程序可以根据需要正确解析 Excel 函数。
采纳答案by Tim Williams
You could try activating each sheet in turn, then adding
您可以尝试依次激活每个工作表，然后添加
ActiveWindow.DisplayFormulas = True
before calling SaveAs.
在调用 SaveAs 之前。
回答by brettdj
You would need to do something like this to export the formula to a csv file cell by cell rather than save each sheet as a CSV which strips the formulae
您需要执行这样的操作才能将公式逐个单元格导出到 csv 文件，而不是将每个工作表另存为 CSV 以去除公式
This code is similar to my answer in Generate a flat list of all excel cell formulas
此代码类似于我在生成所有 excel 单元格公式的平面列表中的答案
For a three sheet workbook it will create files called
对于三页工作簿，它将创建名为
C:\temp\output1.csv
C:\temp\output2.csv
C:\temp\output3.csv
C:\temp\output1.csv
C:\temp\output2.csv
C:\temp\output3.csv
VBA (added escaping of delimiter)
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
回答by Stephen Lloyd
Alternatively to the last solution, you could substitute filesystemobject and textstream. Check this link on writing to a text file
作为最后一个解决方案的替代方案，您可以替换文件系统对象和文本流。在写入文本文件时检查此链接
