Python-Excel：如何从每日更新Excel拉取数据并追加写入另一Excel？

Hey there! Let's tackle your problem step by step—pulling specific cell data from a daily-updated Excel file into a tracking sheet without overwriting existing entries is totally doable, and I'll walk you through a few reliable methods depending on how much automation you want.

If you prefer a simple, no-code approach, direct cell linking works great for occasional updates:

• Open both your source daily Excel file and your tracking file.
• In the tracking file, navigate to the first empty row in your data range (e.g., if your last entry is in row 10, go to row 11).
• Link to the source file's specific cells. For example, if your source date is in [DailyData.xlsx]Sheet1!A1 and the data you need is in [DailyData.xlsx]Sheet1!C5, enter these formulas in your tracking sheet:
  • Date cell (A11): ='[DailyData.xlsx]Sheet1'!$A$1
  • Data cell (B11): ='[DailyData.xlsx]Sheet1'!$C$5
• Each day, just copy these formulas down to the next empty row (use Ctrl+↓ to jump to the last filled row, then move down one and paste). No overwrites here—you're just adding new rows every time.

For a more hands-off approach that avoids manual copying, Power Query is perfect. It can automatically append new data to your tracking table:

1. Open your tracking file, go to the Data tab > Get Data > From File > From Excel Workbook.
2. Select your source daily file, pick the sheet with your data, and choose Load To > Only Create Connection.
3. Go to Data > Queries & Connections, right-click the new connection, and select Edit.
4. In the Power Query Editor, isolate the specific cells you need:
   • If you only need a single row of data (like the daily stats), use Home > Keep Rows > Keep Top Rows and set it to 1.
   • To directly reference a cell (e.g., C5), add a custom column with: =Source{4}[Column3] (note: Power Query uses 0-indexing—row 5 is index 4, column C is Column3 if your data starts at A1).
5. Rename columns to something clear (e.g., "Date" and "Daily Metric"), then close and load. When prompted, select Append data to existing table and choose where your tracking table starts.
6. Each day, just click Data > Refresh All to pull the latest data into your tracking table without touching old entries.

If you want a one-click solution, a simple VBA macro will handle everything automatically:

Sub PullDailyData()
    Dim sourceWB As Workbook
    Dim trackingWB As Workbook
    Dim sourceWS As Worksheet
    Dim trackingWS As Worksheet
    Dim lastRow As Long
    
    ' Update these values to match your files/sheets/cells!
    Set trackingWB = ThisWorkbook ' Your tracking file (the one with this macro)
    Set trackingWS = trackingWB.Sheets("TrackingLog") ' Name of your tracking sheet
    Set sourceWB = Workbooks.Open("C:\Your\Path\To\DailyDataFile.xlsx") ' Source file path
    Set sourceWS = sourceWB.Sheets("DailyStats") ' Name of the source sheet
    
    ' Find the next empty row in the tracking sheet
    lastRow = trackingWS.Cells(trackingWS.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Copy specific cells from source to tracking
    trackingWS.Cells(lastRow, "A").Value = sourceWS.Range("A1").Value ' Source date cell
    trackingWS.Cells(lastRow, "B").Value = sourceWS.Range("C5").Value ' Source data cell
    
    ' Close source file without saving changes
    sourceWB.Close SaveChanges:=False
    
    MsgBox "Daily data added successfully!", vbInformation
End Sub

To use this:

• Open your tracking file, press Alt+F11 to open the VBA Editor.
• Right-click your workbook in the Project Explorer > Insert > Module.
• Paste the code above, then update the file paths, sheet names, and cell references to match your setup.
• Save the tracking file as a .xlsm (macro-enabled workbook).
• Each day, run the macro by pressing Alt+F8, selecting PullDailyData, and clicking Run—your new data will be added to the next empty row automatically.

Quick Tips #

• For Power Query or VBA, make sure the source file is closed when refreshing/running the macro (or adjust the VBA to handle open files if needed).
• If your source file path changes, update the connection in Power Query via Data > Queries & Connections > Edit Source, or modify the file path in the VBA code.

内容的提问来源于stack exchange，提问作者ldurani