将工作表保存到CSV且Excel公式完整无缺 [英] Saving worksheets to CSV with Excel Formulas intact

问题描述

我完全在VBA for Excel中工作.我的解决方案必须完全是编程的，而不是用户驱动的.解决方案的要求是用户启动一个宏以获取工作簿并将8张工作表保存到单独的CSV文件中，从而保留公式并放弃公式分辨率.我遍历了一系列工作表(sht)，并保存了它们.下面的代码可以完美地做到这一点.

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.

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包含文件保存位置的完整路径，我编写了一个布尔函数，用于测试工作表中是否存在工作表.

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.

问题:

我需要CSV文档包含Excel公式，而不是公式求和的内容.公式的结果可以丢弃.

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 website says:

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

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.

这意味着SaveAs函数可能永远不会做我想做的事情，但是我需要某种方式来创建文件.理想情况下，我还希望保留Excel的CSV单元转义功能.可以通过Java和SQL程序读取CSV文件，这些程序可以根据需要正确解析Excel函数.

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.

推荐答案

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

You could try activating each sheet in turn, then adding

ActiveWindow.DisplayFormulas = True

在调用SaveAs之前.

before calling SaveAs.

这篇关于将工作表保存到CSV且Excel公式完整无缺的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！