Saving worksheets to CSV with Excel Formulas intact

Question

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

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.

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 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.

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.

Answer 1

You could try activating each sheet in turn, then adding

ActiveWindow.DisplayFormulas = True

before calling SaveAs.

Answer 2

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

This code is similar to my answer in Generate a flat list of all excel cell formulas

For a three sheet workbook it will create files called

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

VBA (added escaping of delimiter)

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