Insert an external workbook reference (selected by user) in a formula using VBA

Question

I need to insert a formula in a cell (say A1) that references some cells from another workbook (say sum(A1:A3)). I don't know the path nor the name of the workbook so I need to ask the user (edit. I do know the sheet name which is "Foglio1"), I'm doing it like this:

Sub open_workbook_dialog()
Dim my_FileName As Variant

my_FileName = Application.GetOpenFilename(FileFilter:="Excel Files,*.xl*;*.xm*")

End Sub

How do I insert "my_Filename" in a formula?

Jochen Jochen · Accepted Answer · 2020-02-10T13:20:40

I'm sorry, but: You can't, because you are missing one very relevant information: the worksheet(-name) your formula should reference to.

edit: OK, If you have a fix Worksheetname you can do the following: Add a Function to Format your 'my_FileName' with the needed surroundings for a formula:

Private Function Formula_prefix(ByVal Filepath As String, ByVal WsName As String) As String
Dim Path As String, Filename As String, Idx As Long
    Idx = InStrRev(Filepath, "\")
    If Idx > 0 Then Path = Left(Filepath, Idx)
    Filename = Mid(Filepath, Idx + 1)

    Formula_prefix = "'" & Path & "[" & Filename & "]" & WsName & "'!"
End Function

then you can put your formula in your sheet with some code like that:

ThisWorkbook.Worksheets(1).Range("B2").Formula = "=SUM(" & Formula_prefix(my_FileName, "Foglio1") & "A1:A3)"

Maybe there is a more elegant way, but it should work :)

Insert an external workbook reference (selected by user) in a formula using VBA

1 Answers