Edit formulas in unopened Excel file using VBA

You'll need to open each excel file, in order to edit it.  Just create a loop to iterate through all the files (say, in a folder) open them (be sure to turn EnableEvents False, unless they are .xlsx files to ensure macros don't kick off), and change the formula, then save...

Here's an example...

See "MAKE CHANGES HERE" for where changes need to be made to customize your application.

Here's the code:  
 

Option Explicit
Sub loopAndChangeFormulas()
Dim fPath As String
Dim fName As String, fSaveAsFilePath As String, fOriginalFilePath As String
Dim wBook As Workbook, fFilesToProcess() As String
Dim numconverted As Long, cntToConvert As Long, i As Long
Dim killOnSave As Boolean, xMsg As Long, overWrite As Boolean, pOverWrite As Boolean
Dim silentMode As Boolean
Dim fileType As String
Dim sht As Worksheet
Dim rng As Range


    Application.DisplayAlerts = False 'no user prompting, taking all defaults
    Application.EnableEvents = False
    
    fileType = "*.xlsx" 'MAKE CHANGES HERE
        
    fPath = GetFolderName("Select Folder for formula change")
    
    If fPath = "" Then
        MsgBox "You didn't select a folder", vbCritical, "Aborting!"
        Exit Sub
    Else
        fName = Dir(fPath & "\" & fileType)
        If fName = "" Then
            MsgBox "There aren't any " & fileType & " files in the " & fPath & " directory", vbCritical, "Aborting"
            Exit Sub
        Else

            Do
                ReDim Preserve fFilesToProcess(cntToConvert) As String
                fFilesToProcess(cntToConvert) = fName
                cntToConvert = cntToConvert + 1
                
                fName = Dir
                
            Loop Until fName = ""
            
            For i = 0 To cntToConvert - 1
            
                Application.StatusBar = "Processing: " & i + 1 & " of " & cntToConvert & " file: " & fName
                
                fName = fFilesToProcess(i)
                
                'open and convert file
                On Error GoTo errHandler
                fOriginalFilePath = fPath & "\" & fName
                
                Set wBook = Application.Workbooks.Open(fOriginalFilePath)
                
                'get the range for formula conversion
                Set sht = wBook.Sheets("put your sheet name here") 'MAKE CHANGES HERE
                Set rng = sht.Range("put your range address here") 'MAKE CHANGES HERE to the first formula
                
                rng.Formula = "=the corrected formula" 'MAKE CHANGES HERE
                
                rng.Resize(50, 1).FillDown 'MAKE CHANGES HERE - this copies the formula starting at rng.address down for a total of 50 rows
                
                wBook.SaveAs Filename:=fSaveAsFilePath
                
                wBook.Close savechanges:=False
                numconverted = numconverted + 1
                    
            Next i
        End If
    End If
    
processComplete:
    On Error GoTo 0
    MsgBox "Completed formula conversions in " & numconverted & " files", vbOKOnly
    Application.EnableEvents = True 'uncomment if doing other conversions where macros are involved in source workbooks
    Application.StatusBar = False
    Application.DisplayAlerts = True
    Exit Sub
    
errHandler:
    Application.StatusBar = False
    MsgBox "For some reason, could not open/save the file: " & fPath & "\" & fName, vbCritical, "Aborting!"
    Resume processComplete
    
End Sub

Select allOpen in new window

See attached,

Hopefully, you can take it from here.  However, if you need more help, you'll need to provide sheet name and addresses where things need to be changed, etc., to be precise.

Dave
loopAndFixFormulas-r1.xlsm

It would be better to open each of the files, make the modifications and then save the results. The following code opens every file in a user selected folder and makes the desired modifications. Install it in a regular module sheet.

Sub FormulaUpdater()
Dim rg As Range

'Replicate the next four lines as necessary
Set rg = ActiveSheet.Range("CJ2")
rg.Formula = "=(IF(INDEX('Data Entry'!$Q:$Q,ROW($Q18))="""","""",IF(INDEX('Data Entry'!$Q:$Q,ROW($Q18))="""","""",INDEX('Data Entry'!$Q:$Q,ROW($Q18)))))"
rg.Copy
Range(rg, rg.End(xlDown)).PasteSpecial xlPasteFormulas

End Sub


Sub LoopThroughWorkBooksInFolder()
'Runs a macro on every workbook in a user-specified folder
Dim ws As Worksheet
Dim wb As Workbook
Dim celHome As Range
Dim f As String, flPath As String
    'Change the default folder name in the following statement to a reasonable starting place
flPath = Application.GetSaveAsFilename( _
    "C:\", _
    Title:="Please pick any file in the desired folder, then click 'Save' button")
If flPath = "False" Then Exit Sub
Set celHome = ActiveCell
flPath = Left(flPath, InStrRev(flPath, "\"))

Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Application.EnableEvents = False    'Comment out if workbooks don't contain event macros
f = Dir(flPath & "*.xls")           'Make sure file extension matches
Do Until f = ""
    Set wb = Workbooks.Open(f)
    Application.StatusBar = "Processing " & wb.Name
    
    On Error Resume Next
    Set ws = Nothing
    Set ws = Worksheets("Sheet1")   'The worksheet that needs to be changed
    If Not ws Is Nothing Then
        ws.Activate
        FormulaUpdater
    End If
    
    wb.Close SaveChanges:=True
    f = Dir     'Get next filename & path
Loop

Errhandler:
    Application.GoTo celHome
    On Error GoTo 0
    'Application.EnableEvents = True     'Comment out if workbooks don't contain event macros
    Application.StatusBar = False
    Application.DisplayAlerts = True
End Sub

Select allOpen in new window

byundt - "It would be better..." Were you addressing me or the OP?

Dave

Dale,
I see that my code to loop through files in a folder duplicates that of dlmille.

It is worth noting that the formulas you want to update include double-quotes. Since you must pass the new formula to the worksheet as text, you need to double up on each of the double-quotes contained in the formula. I did that for you in my FormulaUpdater sub. If you choose to modify Dave's macro for your updating, please be aware of this requirement.

Brad

byundt - FWIW - I like the formulaUpdater() routine you added - it is a nice compartmentalization.

Cheers,

Dave

Dave,
My remarks were aimed at Dale.

I checked the thread several times for other posts, but didn't bother at the very end. Had I seen your Comment, I would have posted just my sub to modify the formulas. Your macro to loop through files in a folder is obviously battle-tested.

Brad

hi everyone,

Here is a small change to the formulaUpdater code which removes the use of ".copy" & acts on the whole range in one go. I hope it is right...

Option Explicit

Sub FormulaUpdater_v2()
Dim rg As Range
Dim ws As Worksheet

    'RB: If possible I would try to modify the next line to state
    'the explicit workbook & sheet (perhaps supply them as parameters/arguments).
    Set ws = ActiveWorkbook.ActiveSheet

    'Replicate the next four lines as necessary
    With ws.Range("CJ2").Offset(0, 1)
        Set rg = ws.Range(.Cells(1, 1), .End(xlDown))
        'rg.Formula = "=(IF(INDEX('Data Entry'!$Q:$Q,ROW($Q18))="""","""",IF(INDEX('Data Entry'!$Q:$Q,ROW($Q18))="""","""",INDEX('Data Entry'!$Q:$Q,ROW($Q18)))))"
        rg.FormulaR1C1 = "=(IF(INDEX('Data Entry'!R1C17:R" & ws.Rows.Count & "C17,ROW(R[16]C17))="""","""",IF(INDEX('Data Entry'!R1C17:R" & ws.Rows.Count & "C17,ROW(R[16]C17))="""","""",INDEX('Data Entry'!R1C17:R" & ws.Rows.Count & "C17,ROW(R[16]C17)))))"
    End With
Set rg = Nothing
Set ws = Nothing
End Sub

Select allOpen in new window

Dale,
This formula seems to be the same on both sides of the IF statement (ie it has duplicated Index statements), or am I missing it something?
Should one of the tests be for "=0"?
If the files are large (or even if they are not), I would use a separate "helper column" to remove duplicated calculations for efficiency (see Charles Williams site: http://www.decisionmodels.com/optspeedb.htm). For example, assuming that the CK column is empty...

Sub FormulaUpdater_v3()
Dim rg As Range
Dim ws As Worksheet

    'RB: If possible I would try to modify the next line to state
    'the explicit workbook & sheet (perhaps supply them as parameters/arguments).
    Set ws = ActiveWorkbook.ActiveSheet

    'Replicate the next few lines as necessary
    With ws.Range("CJ2")
        Set rg = ws.Range(.Cells(1, 1), .End(xlDown))
    End With
    
    'the helper column formula in CK (note: I would normally make the flow of a spreadsheet's calculations
    'go from left to right but I'm putting this in CK to keep your
    'existing result in the CJ column and on the assumption that CK is empty).
    With rg
        .Offset(0, 1).FormulaR1C1 = "=INDEX('Data Entry'!C17,ROW(R[16]C17))"
        .FormulaR1C1 = "=(IF(RC[1]="""","""",IF(RC[1]="""","""",RC[1])))"
''or is it meant to be...?
        '.FormulaR1C1 = "=(IF(RC[1]="""","""",IF(RC[1]=0,0,RC[1])))"
    End With
    
    Set rg = Nothing
    Set ws = Nothing
End Sub

Select allOpen in new window

hth
Rob

Ooopps!

I forgot to simplify my R1C1 code. The string in FormulaUpdater_v2 can be stated as:

        rg.FormulaR1C1 = "=(IF(INDEX('Data Entry'!C17,ROW(R[16]C17))="""","""",IF(INDEX('Data Entry'!C17,ROW(R[16]C17))="""","""",INDEX('Data Entry'!C17,ROW(R[16]C17)))))"

Select allOpen in new window

Dave,

That's awesome. I got it to work just fine. However, I want to point out that the first time I ran it I got an error message saying the file could not be saved. So, I changed 2 lines:

From this:
                wBook.SaveAs Filename:=fSaveAsFilePath
               
                wBook.Close savechanges:=False

To this:
               ' wBook.SaveAs Filename:=fSaveAsFilePath
               
                wBook.Close savechanges:=True

Question:

Some of these files may be: xls, xlxs, or xlsm. How can I modify this line to cover all of them?
    fileType = "*.xlsx" 'MAKE CHANGES HERE

Brad,

Thanks for catching the double quotes. I spend most of my time in Access and don't know a lot of the little tricks in Excel. Isn't there something else I can use to refer to a blank cell? I never understood why an Excel formula returns 0 when pointing to an empty cell.

Rob,

You are correct. There is some redundancy in that formula. Someone on EE helped me with that back in the spring. I have no idea what INDEX is doing. I must have modified it at some point and failed to realize what I had done. I will check out the link you provided.

Thanks, Dale