excel - 从关闭的工作簿中检索数据而不提示选择工作簿文件？

Question

以下代码非常适合从文件路径中已关闭的工作簿中检索数据。但是，每次循环都会提示我选择实际文件。如何避免手动选择文件？谢谢你

Sub BrandSpecificSettings()

Dim i As Integer
Dim inputs As Worksheet
Dim brand As String

Set inputs = Worksheets("inputs")

brand = inputs.Range("k2").Value

For i = 1 To 27
    If inputs.Range("a" & i).Value = "" Then
        i = i + 1
    Else:
        inputs.Range("b" & i).Value = "='J:\Wassner Enterprises\brand inventory sheets\[" & brand & ".xlsm]settings'!b" & i
    End If
Next i

标签： excelvba

Answer 1

使用GetObject，您可以从工作簿中检索信息而无需显示。

Sub BrandSpecificSettings()
    Dim i As Integer
    Dim inputs As Worksheet: Set inputs = Worksheets("inputs")
    Dim brand As String: brand = inputs.Range("k2").Value
    
    Dim Filename As String: Filename = "J:\Wassner Enterprises\brand inventory sheets\" & brand & ".xlsm"
    
    Dim wbTemp As Workbook: Set wbTemp = GetObject(Filename)
    Dim wsTemp As Worksheet: Set wsTemp = wbTemp.Sheets("Settings")
    
    For i = 1 To 27
        If inputs.Range("A" & i).Value = "" Then
            i = i + 1
        Else:
            inputs.Range("B" & i).Value = wsTemp.Range("B" & i).Value
        End If
    Next i
    
    ' Closes the workbook
    wbTemp.Close SaveChanges:=False
End Sub