Assign formula to Excel cell using VBA

Question

How to assign a formula (which reads data from another file located on the network drive without opening it) to a cell in another Excel file, on the same drive, but in a different folder?

End users have access rights to all folders on the network drive, so no need for user name and password.

I am new to VBA and tried to use the following piece of code but was prompted to locate and open the data file:

Dim Network_Path As String

Network_Path = "\\MyNetworkDrive\folder\subfolder"

With ThisWorkbook.Sheets("Sheet1")
    .Range("C2").Formula = "=SUM('Network_Path\[Source_File.xlsx]Data'!$B$2:$B$20)"
End With

Vityata Vityata · Accepted Answer · 2017-12-21T09:45:40

Try to write the NetworkPath as a variable in the formula:

.Range("C2").Formula = "=SUM(" & Network_Path  &  "\[Source_File.xlsx]Data'!$B$2:$B$20)"

Concerning the general debugging of formula with variables, try the following simple trick:

Public Sub TestMe()
    Dim np              As String 'do not use "_" in file name FWIW
    np = "C:\Users\"      'or something else
    Debug.Print "=IFERROR(INDEX('" & np & "[Source_File.xlsx]Data'!A:A; MATCH('Sheet1'!C3;'" & np & "[Source_File.xlsx]Data'!R:R; 0)); "")"
End Sub

Then see what is present in the immediate window. Press Ctrl+G to open it. The result from the current formula is:

=IFERROR(INDEX('C:\Users\[Source_File.xlsx]Data'!A:A; MATCH('Sheet1'!C3;'C:\Users\[Source_File.xlsx]Data'!R:R; 0)); ")

Thus, you probably have to find a way to remove the last " sign:

Then try to copy the formula to your Excel file and if it works, then it is ok.

Another option is to try to do the exact opposite. E.g., write the formula in Excel, as it should be written and make sure it works. Then select the cell with the formula and run the following:

Public Sub PrintMeUsefulFormula()

    Dim myFormula  As String
    Dim myParenth  As String

    myParenth = """"

    myFormula = Selection.Formula
    myFormula = Replace(myFormula, """", """""")

    myFormula = myParenth & myFormula & myParenth
    Debug.Print myFormula

End Sub

It will print in the immediate window the formula, as it should be used in VBA. Then replace the path with a variable and give it a try.

Assign formula to Excel cell using VBA

1 Answers