vba Excel 宏：浏览 Excel 文件并使用其工作表数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12380901/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel macro: Browse Excel file and use its worksheet data
提问by TJLD22
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).
这是让用户浏览文件的 Excel 宏按钮的脚本。实际上，我想用它来加载一个 Excel 文件并在main_function（当前的 excel）中使用该 Excel 文件的数据。
How can I do this?
我怎样才能做到这一点？
回答by Larry
Guess you want to restrict the user to Excel only, so I modified the filter for you
猜你想限制用户只能使用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
回答by AshishPandey
In continuation to @Larry's answer, you may use the following module if needs to refer the sheet by CodeName, or else if want to refer by sheet name you can use worksheets option, thanks to MVP-Juan Pablo González for this answer over another blog:
继续@Larry 的回答，如果需要通过 CodeName 引用工作表，您可以使用以下模块，或者如果想通过工作表名称引用，您可以使用工作表选项，感谢 MVP-Juan Pablo González 在另一个博客上的这个答案：
'Strating sub procedure to write VBA Code to Open an only Excel 2007 macro Files using File Dialog Box
Sub Browse_File()
Dim strFileToOpen As String
Dim Wbk_WeeklyTemplate As Workbook
Dim Tgt_ShtNm As String
Dim Src_ShtNm As String
'Choosing an Excel File using File dialog Box and capturing the file path in the variable
strFileToOpen = Application.GetOpenFilename(Title:="Please select the file to open", FileFilter:="Excel Files *.xlsm (*.xlsm),")
'Checking if file is selected
If strFileToOpen = "False" Then
MsgBox "No file selected.", vbExclamation, "Sorry!"
Exit Sub
Else
Set Wbk_WeeklyTemplate = Workbooks.Open(strFileToOpen)
End If
''Refer sheet by Sheet CodeName
Src_ShtNm = SheetName(Wbk_WeeklyTemplate, "sheetCodeName")
Tgt_ShtNm = SheetName(ThisWorkbook, "sheetCodeName")
Wbk_WeeklyTemplate.Sheets(Src_ShtNm).Range("N15:X18").Copy
ThisWorkbook.Sheets(Tgt_ShtNm).Range("A1").PasteSpecial xlPasteValues
End Sub
''The function SheetName returns the actual name of the sheet by passing sheet code name
Function SheetName(Wb As Workbook, CodeName As String) As String
SheetName = Wb.VBProject.VBComponents(CodeName).Properties("Name").Value
End Function
The link to as MVP-Juan Pablo González solution: https://www.mrexcel.com/forum/excel-questions/58845-codename-selecting-sheets-through-visual-basic-applications.html
MVP-Juan Pablo González 解决方案的链接：https: //www.mrexcel.com/forum/excel-questions/58845-codename-selecting-sheets-through-visual-basic-applications.html
Hope that helps.
希望有帮助。
