VBA技术问询：实现判断同路径目标工作簿是否存在并返回实例

Let's get this function working exactly as you need it—checking if Destination.xlsx exists in the same folder as your current workbook, returning it if it does, and creating a new one if it doesn't. Your existing code was close, but had a couple of key gaps and a filename mismatch.

First, let's address the issues in your original code: #

• You had Student Information.xlsx hardcoded instead of Destination.xlsx—that's a simple filename mixup
• The function logic wasn't completed: you started the If TestStr = "" block but didn't finish the code to create the workbook or return the object

Here's the corrected, complete function: #

Function CheckForExistingWorkbooks() As Workbook
    Dim wbDest As Workbook
    Dim filePath As String
    Dim testStr As String
    
    ' First, make sure the current workbook has been saved (so we have a valid path)
    If ThisWorkbook.Path = "" Then
        MsgBox "Save this workbook first to set a valid folder path!", vbExclamation
        Set CheckForExistingWorkbooks = Nothing
        Exit Function
    End If
    
    ' Build the full path to Destination.xlsx
    filePath = ThisWorkbook.Path & "\Destination.xlsx"
    
    ' Check if the file exists using Dir
    testStr = ""
    On Error Resume Next
    testStr = Dir(filePath)
    On Error GoTo 0
    
    If testStr <> "" Then
        ' File exists: open it and assign to our workbook variable
        Set wbDest = Workbooks.Open(filePath)
    Else
        ' File doesn't exist: create a new workbook and save it to the target path
        Set wbDest = Workbooks.Add
        wbDest.SaveAs Filename:=filePath
    End If
    
    ' Return the workbook object to the calling procedure
    Set CheckForExistingWorkbooks = wbDest
End Function

Key details explained: #

• Path Validation: We added a check to make sure the current workbook is saved—if it's not, ThisWorkbook.Path will be empty, and we can't create/save the destination workbook properly.
• Filename Correction: Swapped out the wrong filename for Destination.xlsx to match your requirement.
• Existence Check: Using Dir() is a quick way to verify if the file exists without throwing an error (we use On Error Resume Next to suppress any minor issues during the check).
• Workbook Handling:
  • If the file exists, we open it and assign it to wbDest.
  • If not, we create a new workbook and immediately save it to the correct path—this ensures the workbook is tied to the right file location from the start.
• Return Value: We explicitly set the function's return value to the workbook object, so your main procedure can use it directly.

How to use this in your main function: #

You can call it like this:

Sub MainProcedure()
    Dim targetWB As Workbook
    Set targetWB = CheckForExistingWorkbooks()
    
    ' Now you can work with targetWB—e.g., write data, format sheets, etc.
    If Not targetWB Is Nothing Then
        targetWB.Sheets(1).Range("A1").Value = "Hello from the main procedure!"
    End If
End Sub

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