Excel宏：浏览Excel文件并使用其工作表数据 [英] Excel macro: Browse Excel file and use its worksheet data

问题描述

  Sub Button_Click（）
 objFile = Application.GetOpenFilename（fileFilter： =所有文件（*。*），*。*）'选择加载路径
 ..... 
 
调用main_function 
 End Sub 
  

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

我如何做到这一点？

解决方案

过滤器

  Dim pathString As String 
 Dim resultWorkbook As Workbook 
 Dim found As Boolean 
 pathString = Application.GetOpenFilename（fileFilter：=所有文件（*。xl *），* .xl *）
 
'检查是否已经打开
对于每个wb在工作簿
如果InStr（pathString，wb.Name）> 0然后
设置resultWorkbook = wb 
 found = True 
退出
结束如果
下一个wb 
 
如果未找到则
 Set resultWorkbook = Workbooks.Open（pathString）
 End If 
 
'那么可以使用resultWorkbook作为Excel Workbook对象的参考
  

I write a script like this:

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

    Call main_function
End Sub

This is the script of an Excel macro button to let the user browse the file. Actually, I want use this to load an Excel file and use that Excel file's data in the main_function(the current excel).

How can I do this?

解决方案

Guess you want to restrict the user to Excel only, so I modified the filter for you

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

这篇关于Excel宏：浏览Excel文件并使用其工作表数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！