vba - Excel 宏 : Browse Excel file and use its worksheet data

Question

我写了这样一个脚本:

Sub Button_Click()
    objFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ") ' choose load path
    .....

    Call main_function
End Sub

这是让用户浏览文件的 Excel 宏按钮的脚本。实际上，我想用它来加载一个 Excel 文件并在 main_function(当前的 excel)中使用该 Excel 文件的数据。

我该怎么做？

Answer 1

我猜你想限制用户只能使用 Excel，所以我为你修改了过滤器

Dim pathString As String
Dim resultWorkbook As Workbook
Dim found As Boolean
pathString = Application.GetOpenFilename(fileFilter:="All Files (* . xl*) , *.xl* ")

' check if it's already opened
For Each wb In Workbooks
    If InStr(pathString, wb.Name) > 0 Then
        Set resultWorkbook = wb
        found = True
        Exit For
    End If
Next wb

If Not found Then
    Set resultWorkbook = Workbooks.Open(pathString)
End If

' then you can use resultWorkbook as a reference to the Excel Workbook Object