Connecting excel documents

Question

We create a new excel document for daily gift logs and cut/paste all the info into a monthly gift log. All of this is stored on our server. Is there a way to auto populate the monthly gift log excel document from the daily gift logs? Also, is there a way to automatically update the monthly gift log if the daily gift log is changed? Thanks!

Answer 1

Yep - you can automate this process in Excel. You might consider the following options:

Option 1. Using linked Excel workbooks - Excel allows you to link data from one workbook to another:

1. Open the monthly gift log workbook.
2. Go to the sheet where you want the daily log data.
3. Enter a formula that references the daily workbook (this will pull the value from cell A1 in the daily log workbook - you can copy this formula across the needed range) - for example

      ='[DailyLog_2025-10-22.xlsx]Sheet1'!A1
   

4. Repeat for other daily logs (you can sum, consolidate, or stack them as needed).

This is simple, with no coding required. It updates automatically when daily workbooks are open (or prompts for update if closed).

Option 2. Using Excel Power Query (for automation) - Power Query can combine multiple files in a folder and refresh automatically. This is ideal if you create a new daily log each day in the same folder.

1. Place all daily logs in a single folder (e.g., C:\GiftLogs\Daily).
2. Open the monthly gift log workbook.
3. Go to Data → Get Data → From File → From Folder.
4. Select the folder with your daily logs.
5. Power Query will list all files. Click Combine → Combine & Load.
6. Select the relevant sheet in the daily files to combine.
7. Power Query will merge all daily logs into one table.
8. To update: Data → Refresh All whenever new daily logs are added or existing ones are changed.

This requires no formulas and it can handle hundreds of daily logs easily. It also refreshes updates all data automatically.

Option 3. Using VBA Macro - you can write a macro that:

• Opens all daily logs.
• Copies data into the monthly log.
• Runs automatically when the monthly log is opened or on a schedule.

Example VBA snippet:

Sub UpdateMonthlyLog()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim folderPath As String
    Dim fileName As String
    Dim targetRow As Long
    
    folderPath = "C:\GiftLogs\Daily\"
    fileName = Dir(folderPath & "*.xlsx")
    targetRow = 2 'Assume headers in row 1
    
    Do While fileName <> ""
        Set wb = Workbooks.Open(folderPath & fileName)
        Set ws = wb.Sheets(1)
        ws.Range("A2:D100").Copy 'Adjust range as needed
        ThisWorkbook.Sheets("MonthlyLog").Cells(targetRow, 1).PasteSpecial xlPasteValues
        targetRow = targetRow + ws.Range("A2:D100").Rows.Count
        wb.Close False
        fileName = Dir
    Loop
End Sub

This is fully automated and it can run on workbook open or scheduled.

---

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

Answer 2

Hi,

Follow the steps shown in this video - https://www.youtube.com/watch?v=yL11ugShdrk