Summary
Some visual-basic for applications code to extract all the formulas from every sheet in an Excel Workbook and save them in a text file.
Background
Avoiding a long explanation, we’ve been writing a reporting tool that combines a Microsoft Access front-end to a SQL Server database. It generates and updates some Excel reports/data visualizations in a sort of dashboard.
As part of managing multiple worksheets and sheets, at times sheets get copied into another workbook. When that occurs, Excel modifies the copied formula to refer back to data in the original sheets. This is a problem when we are copying both the underlying data table and the sheet. We want the formulas to reference the new (copied) data table in the new workbook.
This function writes the formulas, with their cell-references, to a text file. We then have a function that reads that text file and re-writes the formulas cell by cell in the new sheet and workbook.
Regular Formulas and Array Formulas
Array formulas must be written (CSE – control-shift-enter) into a cell differently than standard formulas. This is true in code as well. So the function identifies whether a function is an array function or not and provides an identifier.
The output
The text file created includes the following data:
[Cell Reference]:@@[formula]@@
The colon “:” and the “@@” surround the formula.
Array formulas include a “{“ squiggly bracket prior to the formula.
Regular formula text example
$A$1:@@[=SUM(F1:F100)]@@
Array formula text example
$G$20:@@[{=SUM((SHEETNAME!$E$3:$E$96=$B11)*(SHEETNAME!$AB$2:$AV$2=”HEADING”)*HCBudgetFY2017!$AB$3:$AV$96)/12*(DATEDIF($D$4,$G$4,”m”)+1)]@@
Most important to note is that the two formulas differ in the “@@[“ and “@@[{“ – with the second prefix used to identify an array formula.
The Code
The following function writes a single sheet out to a text file. I’ll add the expanded code next week and include code that writes the same formulas into a worksheet.
Sub ReadFormulas()
Dim lformulas As String, lrow As Integer, lcol As Integer, strAflag
'strAflag is empty or adds specifier if formula is an array formula
Dim strWksName As String
Dim wks As Worksheet
Set wks = ActiveSheet
strWksName = wks.Name
lformulas = "WKS:" & strWksName & vbCrLf
lrow = Range("A1").SpecialCells(xlCellTypeLastCell).Row
lcol = Range("A1").SpecialCells(xlCellTypeLastCell).Column
For c = 1 To lcol
For r = 1 To lrow
strForm = wks.Cells(r, c).Formula
strRef = wks.Cells(r, c).Address
strAflag = IIf(wks.Cells(r, c).HasArray = True, "{", "")
If Len(strForm) > 0 And Trim(Mid(strForm, 1, 1)) = "=" Then 'not just values but actual formula
lformulas = lformulas & strRef & ":@@[" & strAflag & strForm & "]@@" & vbCrLf
End If
Next
Next
blnFile = MakeFile(lformulas, "c:\data\form" & strWksName & ".txt")
End Sub
Function MakeFile(inFText, inFileName)
' inFText = File text
Set oFS = CreateObject("Scripting.FileSystemObject")
Set nRF = oFS.CreateTextFile(inFileName, True)
nRF.Write inFText
Set nRF = Nothing
Set oFS = Nothing
MakeFile = True
End Function
Function ReadFileStr(inFile)
Dim strFile As String
'Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(inFile)
strFile = objFile.ReadAll
objFile.Close
ReadFileStr = strFile
End Function
Conclusion
I provided a couple helper functions – one to write the file out, the other to read the file into memory. The second function would be used later to iterate through the file to write the data back to a sheet of your choice.
This is a simple but useful routine. We included it as part of a larger sheet management solution. Sheets and entire workbook data is stored in SQL tables and key lists of data that we report on are synchronized across various reporting sheets and summary dashboards.