Grab formulas from closed Excel file (not just values)

Question

I'm able to grab values from a closed workbook with the widely found GetValues function; it works great.

But sometimes I need to grab the formula of a cell from the closed workbook. I tried modifying GetValues to grab the cells formula but I'm getting errors.

How to get a formula (not simple value) of cells from a closed excel file?

With Sheets
  For r = 2 To NewRowQty ' from second row to last row
    For c = 1 To ThisColumnEnd ' out to EndColumn (from import dialogue box)
      ThisCell = Cells(r, c).Address
      ThisValue = GetValue(ThisPath, ThisFile, ThisSheet, ThisCell)
      If ThisValue <> "0" Then
        If c = 3 And r > 2 Then
          Cells(r, c).Formula = GetFormula(ThisPath, ThisFile, ThisSheet, ThisCell)
        Else
          Cells(r, c) = ThisValue
        End If
      End If
    Next c
  Next r
End With

Calls these two functions, GetValue works fine, GetFormula won't grab the formula.

Private Function GetValue(p, f, s, c)
  'p: path: The drive and path to the closed file (e.g., "d:\files")
  'f: file: The workbook name (e.g., "budget.xls")
  's: sheet: The worksheet name (e.g., "Sheet1")
  'c: cell: The cell reference (e.g., "C4")

  'Retrieves a value from a closed workbook
  Dim arg As String
  'Make sure the file exists
  If Right(p, 1) <> "\" Then p = p & "\"
  If Dir(p & f) = "" Then
    GetValue = "File Not Found"
    Exit Function
  End If
  'Create the argument
  arg = "'" & p & "[" & f & "]" & s & "'!" & _
  Range(c).Range("A1").Address(, , xlR1C1)
  'Execute an XLM macro
  GetValue = ExecuteExcel4Macro(arg)
End Function

Private Function GetFormula(p, f, s, c)
  'p: path: The drive and path to the closed file (e.g., "d:\files")
  'f: file: The workbook name (e.g., "budget.xls")
  's: sheet: The worksheet name (e.g., "Sheet1")
  'c: cell: The cell reference (e.g., "C4")

  'Retrieves a value from a closed workbook
  Dim arg As String
  'Make sure the file exists
  If Right(p, 1) <> "\" Then p = p & "\"
  If Dir(p & f) = "" Then
    GetFormula = "File Not Found"
    Exit Function
  End If
  'Create the argument
  arg = "'" & p & "[" & f & "]" & s & "'!" & _
  Range(c).Range("A1").Address(, , xlR1C1).Formula
  'Execute an XLM macro
  GetFormula = ExecuteExcel4Macro(arg)
End Function

Update: Joel's first code post was the basis of what I ended up using so I marked that correct. Here's my actual implementation using a copy paste of entire row formulas. This is best because I don't know how many columns out may contains values or formulas, could be C or ZZ.

' silent opening of old file:
Application.EnableEvents = False
Set o = GetObject(FileTextBox.Text)
With Sheets
    For r = 2 To NewRowQty ' from second row to last row
        ThisCell = "A" & r
        o.Worksheets(ThisRate).Range(ThisCell).EntireRow.Copy
        Sheets(ThisRate).Range(ThisCell).PasteSpecial xlFormulas
    Next r
End With
' Close external workbook, don't leave open for extended periods
Set o = Nothing
Application.EnableEvents = True

You get far more control from opening workbooks in the background, which can be done with vbscript not just vba. Plus looping through a UDF to retrieve values will be very slow for a decent data grab. See http://stackoverflow.com/q/7524064/641067 for different methods to access data from closed workbooks. As per Tim's comment you can disable events to stop any workbook events (which is good practice regardless when running codee) — brettdj, Nov 02 '11 at 02:10

score 3 · Accepted Answer · answered Nov 01 '11 at 22:38

3

Why such convoluted code? The code you are using, for some reason, is invoking the Excel 4.0 backwards compatibility mode macro processor. I can't imagine why you would do that.

Here's a simple way to get the formula from cell Sheet1!A1 of c:\tmp\book.xlsx:

Dim o As Excel.Workbook
Set o = GetObject("c:\tmp\Book.xlsx")
MsgBox o.Worksheets("Sheet1").Cells(1, 1).Formula
Set o = Nothing ' this ensures that the workbook is closed immediately

answered Nov 01 '11 at 22:38

Joel Spolsky

33,372
17
89
105

Convoluted code is because it's part of a larger data importer, bringing in values and formulas from earlier versions of a toolkit we use. These external files have Workbook_Activate macros that mess with the current file and present a poor UX; hence why I need to fetch the data from closed workbooks (hence the question). – Stay-at-home-dad Nov 01 '11 at 23:04
I tried your code and it launches the macros, not going to work. The ExecuteExcel4Macro is actually mentioned in modern books and sites as being an oldie but the only goodie for grabbing values from closed workbooks, but I can't figure out out to get formulas from closed files. Any other ideas that don't open the external workbooks? – Stay-at-home-dad Nov 01 '11 at 23:07
2

@Kirk - have you considered turning off events before opening the file? – Tim Williams Nov 01 '11 at 23:10
There you go, I didn't know I could shut off Application.Events on the external file, so I was fighting to sneak things out of the closed file. I was able to reduce this section's code in half, thanks a lot, I learned well from this. – Stay-at-home-dad Nov 03 '11 at 16:27

score 1 · Answer 2 · edited Aug 28 '12 at 06:33

If you insist on running Excel 4 - style macros (obsolete in 1994!) you need to use the XLM function GET.FORMULA to retrieve the formula instead of the value as follows:

arg = "GET.FORMULA('" & p & "[" & f & "]" & s & "'!" & _
      Range(c).Range("A1").Address(, , xlR1C1) & ")"

Note that the result will have formulas using R1C1 notation instead of A1 notation.

Converting back to A1 notation (if you really want to do that) is left as an exercise to the reader.

Grab formulas from closed Excel file (not just values)

2 Answers2