How to write VBA so that it automatically runs to get data from the source file

Try using VBA to build the external FILTER/BYROW/LAMBDA formula dynamically, rather than hard-coding the source folder, workbook name, or sheet name. Since the source workbook and sheet names change, VBA can determine them at runtime.

Note though that Excel dynamic-array functions such as FILTER generally require the referenced source workbook to be open. VBA can open the source workbook before installing the formula.

For example, if your structure is:

...\1_DB_Collectives\DB Collective_1.xlsm

and the preprocessing workbook is:

...\2_Preprocessing_DB_Data\Preprocessing_DB Collective_1.xlsm

than the following macro can derive the source workbook from the preprocessing workbook name. You only need to specify the source worksheet.

Sub UpdateDBCollectiveFormula()

    Dim wbDest As Workbook
    Dim wbSource As Workbook
    Dim wsDest As Worksheet
    Dim wsSource As Worksheet

    Dim destFolder As String
    Dim sourceFolder As String
    Dim sourceFile As String
    Dim sourceSheet As String
    Dim collectiveName As String

    Set wbDest = ThisWorkbook
    Set wsDest = ActiveSheet

    'Get the name of the current preprocessing workbook
    collectiveName = Replace(wbDest.Name, "Preprocessing_", "")
    
    'Remove the extension
    collectiveName = Left(collectiveName, InStrRev(collectiveName, ".") - 1)

    'Source folder is the sibling folder 1_DB_Collectives
    destFolder = wbDest.Path
    sourceFolder = Replace(destFolder, _
                           "2_Preprocessing_DB_Data", _
                           "1_DB_Collectives")

    'Source workbook name
    sourceFile = collectiveName & ".xlsm"

    'Change this to the required source worksheet
    sourceSheet = "S1"

    'Open source workbook if it is not already open
    On Error Resume Next
    Set wbSource = Workbooks(sourceFile)
    On Error GoTo 0

    If wbSource Is Nothing Then
        If Dir(sourceFolder & "\" & sourceFile) = "" Then
            MsgBox "Source workbook not found:" & vbCrLf & _
                   sourceFolder & "\" & sourceFile, vbCritical
            Exit Sub
        End If

        Set wbSource = Workbooks.Open(sourceFolder & "\" & sourceFile)
    End If

    'Check that the source worksheet exists
    On Error Resume Next
    Set wsSource = wbSource.Worksheets(sourceSheet)
    On Error GoTo 0

    If wsSource Is Nothing Then
        MsgBox "Source worksheet '" & sourceSheet & _
               "' was not found in " & sourceFile, vbCritical
        Exit Sub
    End If

    'Create the FILTER/BYROW/LAMBDA formula
    wsDest.Range("A1").Formula2 = _
        "=FILTER('" & wbSource.FullName & "[" & wbSource.Name & "]" & _
        wsSource.Name & "'!$C$6:$DB$105," & _
        "BYROW('" & wbSource.FullName & "[" & wbSource.Name & "]" & _
        wsSource.Name & "'!$G$6:$DB$105," & _
        "LAMBDA(r,SUM(r)>0)),""N"")"

    MsgBox "Formula updated successfully.", vbInformation

End Sub

The resulting formula will be equivalent to your current formula, but VBA constructs the workbook path, workbook name, and worksheet name automatically. For example, it would generate a reference conceptually like this:

=FILTER('Q:\...\1_DB_Collectives\[DB Collective_1.xlsm]S1'!$C$6:$DB$105,BYROW('Q:\...\1_DB_Collectives\[DB Collective_1.xlsm]S1'!$G$6:$DB$105,LAMBDA(r,SUM(r)>0)),"N")

You would need to change sourceSheet = "S1" if the source worksheet itself changes. If the worksheet name follows a predictable relationship to the workbook—for example, S1, S2, S3 corresponding to DB Collective_1, DB Collective_2, DB Collective_3—that can also be calculated automatically.

Also, A1 in the example is the cell where the spilled FILTER formula is placed. If your existing formula is in another cell, replace wsDest.Range("A1") with that cell, such as wsDest.Range("C6").

If your source workbook is sometimes .xlsx, .xlsm, or another extension, the macro can also be changed so it searches the 1_DB_Collectives folder and automatically finds the matching DB Collective_1, DB Collective_2, etc., without assuming the extension.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Hi @Chaturvedi, Santosh ,

Currently @Marcin Policht approach is a good option if you want to continue using the existing FILTER/BYROW/LAMBDA logic while allowing the workbook path, workbook name, and sheet name to change dynamically .

The macro should do the following:

1. Determine the source workbook name from the destination workbook name.
2. Open the source workbook if it is not already open.
3. Confirm that the required source worksheet exists.
4. Insert the FILTER, BYROW, and LAMBDA formula into the required destination cell.

Building on Marcin's example and the folder structure shown in your screenshots, please try the following version:

Option Explicit

Sub UpdateDBCollectiveFormula()

    Dim wbDest As Workbook
    Dim wbSource As Workbook
    Dim wsDest As Worksheet
    Dim wsSource As Worksheet

    Dim sourceFolder As String
    Dim sourceFile As String
    Dim sourcePath As String
    Dim sourceSheet As String
    Dim destinationSheet As String
    Dim destinationCell As String
    Dim sourceReference As String

    Set wbDest = ThisWorkbook

    ' Change these values to match the actual worksheet names and cell.
    sourceSheet = "S1"
    destinationSheet = "RSL31"
    destinationCell = "J6"

    ' Check whether the destination worksheet exists.
    On Error Resume Next
    Set wsDest = wbDest.Worksheets(destinationSheet)
    On Error GoTo 0

    If wsDest Is Nothing Then
        MsgBox "The destination worksheet '" & destinationSheet & _
               "' was not found in:" & vbCrLf & _
               wbDest.Name, _
               vbCritical, _
               "Destination worksheet not found"
        Exit Sub
    End If

    ' Determine the source workbook name from the destination workbook.
    '
    ' Example:
    ' Preprocessing_DB Collective_1.xlsm
    ' becomes:
    ' DB Collective_1.xlsm
    sourceFile = Replace( _
        wbDest.Name, _
        "Preprocessing_", _
        "", _
        1, _
        1, _
        vbTextCompare)

    If StrComp(sourceFile, wbDest.Name, vbTextCompare) = 0 Then
        MsgBox "The destination workbook name does not contain " & _
               "'Preprocessing_'." & vbCrLf & vbCrLf & _
               "Current workbook:" & vbCrLf & _
               wbDest.Name, _
               vbCritical, _
               "Destination workbook not recognized"
        Exit Sub
    End If

    ' The source and destination workbooks are in sibling folders.
    sourceFolder = Replace( _
        wbDest.Path, _
        "2_Preprocessing_DB_Data", _
        "1_DB_Collectives", _
        1, _
        1, _
        vbTextCompare)

    If StrComp(sourceFolder, wbDest.Path, vbTextCompare) = 0 Then
        MsgBox "The destination folder does not contain " & _
               "'2_Preprocessing_DB_Data'." & vbCrLf & vbCrLf & _
               "Current folder:" & vbCrLf & _
               wbDest.Path, _
               vbCritical, _
               "Destination folder not recognized"
        Exit Sub
    End If

    sourcePath = sourceFolder & Application.PathSeparator & sourceFile

    ' Check whether the source workbook is already open.
    On Error Resume Next
    Set wbSource = Workbooks(sourceFile)
    On Error GoTo 0

    ' Open the source workbook when necessary.
    If wbSource Is Nothing Then

        If Dir(sourcePath) = "" Then
            MsgBox "The source workbook was not found:" & _
                   vbCrLf & sourcePath & vbCrLf & vbCrLf & _
                   "Confirm that the source and destination " & _
                   "workbooks use the expected names and extensions.", _
                   vbCritical, _
                   "Source workbook not found"
            Exit Sub
        End If

        On Error GoTo OpenWorkbookError
        Set wbSource = Workbooks.Open(sourcePath)
        On Error GoTo 0

    End If

    ' Check whether the source worksheet exists.
    On Error Resume Next
    Set wsSource = wbSource.Worksheets(sourceSheet)
    On Error GoTo 0

    If wsSource Is Nothing Then
        MsgBox "The source worksheet '" & sourceSheet & _
               "' was not found in:" & vbCrLf & _
               wbSource.Name & vbCrLf & vbCrLf & _
               "Enter the exact worksheet-tab name in sourceSheet.", _
               vbCritical, _
               "Source worksheet not found"
        Exit Sub
    End If

    ' The source workbook is open, so the workbook name and
    ' worksheet name are sufficient for the external reference.
    sourceReference = "'[" & _
                      Replace(wbSource.Name, "'", "''") & "]" & _
                      Replace(wsSource.Name, "'", "''") & "'!"

    ' Insert the dynamic-array formula.
    wsDest.Range(destinationCell).Formula2 = _
        "=FILTER(" & _
        sourceReference & "$C$6:$DB$105," & _
        "BYROW(" & _
        sourceReference & "$G$6:$DB$105," & _
        "LAMBDA(r,SUM(r)>0)),""N"")"

    Application.Calculate

    MsgBox "The source workbook was located and the formula " & _
           "was updated successfully.", _
           vbInformation, _
           "Update completed"

    Exit Sub

OpenWorkbookError:

    MsgBox "Excel could not open the source workbook:" & _
           vbCrLf & sourcePath & vbCrLf & vbCrLf & _
           "Error " & Err.Number & ": " & Err.Description, _
           vbCritical, _
           "Unable to open source workbook"

End Sub

How to add the macro in Excel

1. Open the Preprocessing_DB Collective_1 workbook.
2. Save the workbook as an Excel Macro-Enabled Workbook, with the .xlsm extension.
3. Press Alt + F11 to open the Visual Basic Editor.
4. Select Insert > Module.
5. Paste the code into the new module.
6. Update these three values to match the actual source worksheet, destination worksheet, and destination cell:

sourceSheet = "S1"

destinationSheet = "RSL31"

destinationCell = "J6"

For sourceSheet, enter the exact name displayed on the worksheet tab inside the source workbook. The source workbook name and source worksheet name are not necessarily the same.

1. Save the workbook.
2. Return to Excel.
3. Press Alt + F8.
4. Select UpdateDBCollectiveFormula, and then select Run.

Based on the screenshot, I also noticed that the current formula is returning #REF!. This can occur if the source workbook is closed or if the generated workbook/worksheet reference does not match the actual source file.

If you found my response helpful or informative, I would greatly appreciate it if you could follow this guide for your confirmation.

Thank you.

Option Explicit

'=========================================================

' AUTOMATICALLY IMPORT DATA FROM SOURCE FILE

'=========================================================

Private Sub Workbook_Open()

GetDataFromSource

End Sub

Sub GetDataFromSource()

Dim sourceWB As Workbook

Dim sourceWS As Worksheet

Dim targetWS As Worksheet

Dim sourcePath As String

Dim lastRow As Long

Dim lastCol As Long

On Error GoTo ErrorHandler

'-----------------------------------------------------

' SOURCE FILE KA PATH YAHAN LIKHO

'-----------------------------------------------------

sourcePath = "C:\Users\YourName\Desktop\Source.xlsx"

'-----------------------------------------------------

' DESTINATION SHEET

'-----------------------------------------------------

Set targetWS = ThisWorkbook.Worksheets("Sheet1")

'-----------------------------------------------------

' SOURCE FILE OPEN

'-----------------------------------------------------

Set sourceWB = Workbooks.Open(sourcePath)

'-----------------------------------------------------

' SOURCE SHEET

'-----------------------------------------------------

Set sourceWS = sourceWB.Worksheets("Sheet1")

'-----------------------------------------------------

' LAST ROW AUR LAST COLUMN FIND KARNA

'-----------------------------------------------------

lastRow = sourceWS.Cells(sourceWS.Rows.Count, 1).End(xlUp).Row

lastCol = sourceWS.Cells(1, sourceWS.Columns.Count).End(xlToLeft).Column

'-----------------------------------------------------

' PURANA DATA DELETE

'-----------------------------------------------------

targetWS.Cells.ClearContents

'-----------------------------------------------------

' SOURCE SE DATA COPY

'-----------------------------------------------------

targetWS.Range("A1").Resize(lastRow, lastCol).Value = _

    sourceWS.Range("A1").Resize(lastRow, lastCol).Value

'-----------------------------------------------------

' SOURCE FILE CLOSE

'-----------------------------------------------------

sourceWB.Close SaveChanges:=False

Set sourceWS = Nothing

Set sourceWB = Nothing

Set targetWS = Nothing

MsgBox "Data successfully updated!", vbInformation, "Data Update"

Exit Sub

ErrorHandler:

On Error Resume Next

If Not sourceWB Is Nothing Then

    sourceWB.Close SaveChanges:=False

End If

Application.ScreenUpdating = True

Application.DisplayAlerts = True

MsgBox "Data update failed!" & vbCrLf & vbCrLf & _

       "Please check:" & vbCrLf & _

       "1. Source file path" & vbCrLf & _

       "2. Source sheet name" & vbCrLf & _

       "3. Destination sheet name", _

       vbCritical, "Error"

End Sub