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.