VBA跨Excel取数优化咨询：动态指定源文件与工作表

Great question! Let's tackle both your issues step by step—first ditching those unreliable ActiveWorkbook/ActiveSheet references, then making your source file path dynamic based on cell Z1. Here's how to refactor your code properly:

1. Replace Active References with Explicit, Reliable Objects #

Using ActiveWorkbook or ActiveSheet is risky because user interactions (like clicking another workbook/sheet while the macro runs) can break your code. Instead, we'll explicitly open the source workbook, let the user select a sheet via the UserForm, then reference that sheet directly.

First, adjust your UserForm to capture the selected worksheet name. Add a public variable to your UserForm code module to store the selection:

' Inside UserForm1 code module
Public SelectedSheetName As String

Private Sub cmdOK_Click()
    If ListBox1.ListIndex <> -1 Then
        SelectedSheetName = ListBox1.List(ListBox1.ListIndex)
        Me.Hide
    Else
        MsgBox "Please select a worksheet first!", vbExclamation
    End If
End Sub

Private Sub cmdCancel_Click()
    SelectedSheetName = ""
    Me.Hide
End Sub

2. Dynamic Source File Path from Range("Z1") #

We'll pull the filename from Z1, combine it with the path of your daily workbook (since all source files are in the same directory), and add error checking to handle missing files or empty cells.

Here's the revised main macro with all fixes:

Sub example()
    Dim dest_wbk As Workbook
    Dim dest_ws As Worksheet
    Dim source_wbk As Workbook
    Dim source_ws As Worksheet
    Dim sourceFilename As String
    Dim sourceFullPath As String
    Dim userForm As UserForm1
    
    ' Set explicit references for the destination workbook/sheet
    Set dest_wbk = ThisWorkbook
    ' Use the sheet containing cell Z1 as the destination sheet
    Set dest_ws = dest_wbk.Range("Z1").Parent
    
    ' Get source filename from Z1 and validate
    sourceFilename = Trim(dest_ws.Range("Z1").Value)
    If sourceFilename = "" Then
        MsgBox "Please enter a source filename in cell Z1!", vbExclamation
        Exit Sub
    End If
    
    ' Build full path (assuming source files are in the same folder as this workbook)
    sourceFullPath = dest_wbk.Path & "\" & sourceFilename
    
    ' Check if the source file exists
    If Dir(sourceFullPath) = "" Then
        MsgBox "Source file not found: " & sourceFullPath, vbCritical
        Exit Sub
    End If
    
    ' Open the source workbook (hidden to avoid user interference)
    Set source_wbk = Workbooks.Open(Filename:=sourceFullPath, ReadOnly:=True, Visible:=False)
    
    ' Initialize and populate the UserForm with source sheet names
    Set userForm = New UserForm1
    With userForm.ListBox1
        .Clear
        Dim ws As Worksheet
        For Each ws In source_wbk.Sheets
            .AddItem ws.Name
        Next ws
    End With
    
    ' Show the UserForm modally
    userForm.Show
    
    ' Process the user's selection
    If userForm.SelectedSheetName <> "" Then
        ' Set explicit reference to the selected source worksheet
        Set source_ws = source_wbk.Sheets(userForm.SelectedSheetName)
        
        ' Proceed with your lookup logic here
        Dim sourceLastRow As Long
        sourceLastRow = source_ws.Cells(source_ws.Rows.Count, 2).End(xlUp).Row
        ' ... rest of your lookup code using source_ws and dest_ws ...
        
        MsgBox "Data retrieval completed successfully!", vbInformation
    Else
        MsgBox "No worksheet selected. Operation cancelled.", vbExclamation
    End If
    
    ' Cleanup: close the source workbook without saving
    source_wbk.Close SaveChanges:=False
    Set userForm = Nothing
    Set source_ws = Nothing
    Set source_wbk = Nothing
    Set dest_ws = Nothing
    Set dest_wbk = Nothing
End Sub

Key Improvements: #

• No more Active references: Every workbook/sheet is explicitly set, so user clicks won't break the macro.
• Dynamic source path: Pulls the filename from Z1 and uses the daily workbook's directory to build the full path.
• Error handling: Checks for empty Z1, missing source files, and unselected worksheets.
• Hidden source workbook: Opens the source file in the background so users don't accidentally interact with it.
• Clean resource management: Properly closes workbooks and clears object references to avoid memory leaks.

Just make sure your UserForm has:

• A ListBox named ListBox1
• Two command buttons named cmdOK and cmdCancel (with captions "OK" and "Cancel")

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