能否在不使用扩展名的情况下引用或设置Excel工作簿变量？

Absolutely, you can avoid hardcoding file extensions and handle all Excel file types seamlessly—here are two reliable approaches depending on whether the workbook is already open or needs to be accessed from disk:

1. For Already Open Workbooks #

If the workbook you’re targeting is already open in Excel, you can loop through the Workbooks collection to match the base name (without extension) to the right workbook. Here’s a reusable function for this:

Function GetOpenWorkbookByBaseName(baseName As String) As Workbook
    Dim wb As Workbook
    Dim fileNameWithoutExt As String
    
    For Each wb In Workbooks
        ' Extract name without extension (handle rare cases where no extension exists)
        fileNameWithoutExt = IIf(InStrRev(wb.Name, ".") > 0, _
                                Left(wb.Name, InStrRev(wb.Name, ".") - 1), _
                                wb.Name)
        
        ' Case-insensitive match to avoid issues with capitalization
        If LCase(fileNameWithoutExt) = LCase(baseName) Then
            Set GetOpenWorkbookByBaseName = wb
            Exit Function ' Exit early once a match is found
        End If
    Next wb
    
    ' Return Nothing if no matching workbook is open
    Set GetOpenWorkbookByBaseName = Nothing
End Function

How to use it: #

Dim wb As Workbook
Dim targetBaseName As String

targetBaseName = Range("A1").Value
Set wb = GetOpenWorkbookByBaseName(targetBaseName)

If Not wb Is Nothing Then
    ' Perform your operations here
    MsgBox "Found open workbook: " & wb.Name
Else
    MsgBox "No open workbook with base name '" & targetBaseName & "' exists."
End If

2. For Closed Workbooks #

If the workbook isn’t open yet, use the Dir function with a wildcard to locate any Excel file matching the base name, then open it. This works for .xls, .xlsx, .xlsm, .xlsb, and other Excel formats:

Function OpenWorkbookByBaseName(baseName As String, Optional filePath As String = "") As Workbook
    Dim fullFilePath As String
    Dim fileWildcard As String
    
    ' Use the current workbook's directory if no path is specified
    If filePath = "" Then filePath = ThisWorkbook.Path & "\"
    
    ' Wildcard to match all Excel-related extensions
    fileWildcard = baseName & ".xls*"
    
    ' Find the first matching file in the specified path
    fullFilePath = Dir(filePath & fileWildcard)
    
    If fullFilePath <> "" Then
        ' Open the workbook and return the object
        Set OpenWorkbookByBaseName = Workbooks.Open(filePath & fullFilePath)
    Else
        ' Return Nothing if no matching file is found
        Set OpenWorkbookByBaseName = Nothing
    End If
End Function

How to use it: #

Dim wb As Workbook
Dim targetBaseName As String

targetBaseName = Range("A1").Value
' Use default path (current workbook's folder)
Set wb = OpenWorkbookByBaseName(targetBaseName)
' Or specify a custom path: Set wb = OpenWorkbookByBaseName(targetBaseName, "C:\Your\Target\Folder\")

If Not wb Is Nothing Then
    MsgBox "Successfully opened: " & wb.Name
Else
    MsgBox "No workbook with base name '" & targetBaseName & "' found in the specified path."
End If

Quick Notes: #

• Both functions use case-insensitive matching, so "MyReport" will match "myreport.xlsx" or "MYREPORT.XLSM".
• If multiple workbooks with the same base name (but different extensions) are open, the first one encountered in the loop will be returned. You can extend the function to prompt the user to choose if this scenario is common.
• For closed workbooks, Dir returns the first matching file. If multiple files share the same base name, add logic to list all matches and let the user select the correct one.

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