vba Excel 宏：如何从其他 Excel 文件中提取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12382634/
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: How to extract data from other Excel file
提问by TJLD22
Possible Duplicate:
excel macro: browse excel file and use its worksheet data
In brief, I want to write a script can do this. In the current excel file, there is a macro button. The user can click the button to browse files (another Excel). In that browsed Excel, several worksheets will be load to current Excel workbook and the data will be used. I write like this
简而言之，我想写一个脚本可以做到这一点。在当前的 excel 文件中，有一个宏按钮。用户可以单击该按钮来浏览文件（另一个 Excel）。在该浏览的 Excel 中，多个工作表将被加载到当前 Excel 工作簿中并使用数据。我这样写
Sub Button1_Click() ' choose LOAD path
objFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ") ' browse function
... ' following
Call main_function
End Sub
I write the browse function already. but how to write the following part? eg. the certain worksheet's data in objFile will be used in main_function.
我已经写了浏览功能。但是下面的部分怎么写呢？例如。objFile 中某个工作表的数据将在 main_function 中使用。
回答by enhzflep
While what you want isn't really clear (I'm not confident I understand your question's intent) I will have a crack at this.
虽然你想要的不是很清楚（我不相信我理解你的问题的意图）我会对此有所了解。
Assuming you have the name of the file you'd like to open in objFile, you can go about extracting data from that spreadsheet like this - I just open the selected workbook, then write the name of all of the sheets in it to Column A in whichever sheet was open before the button was pressed.
假设您有要在 objFile 中打开的文件的名称，您可以像这样从该电子表格中提取数据 - 我只是打开选定的工作簿，然后将其中所有工作表的名称写入 A 列在按下按钮之前打开的任何工作表中。
Opening a new workbook causes it to be brought to the foreground - so if I didn't grab the active sheet before opening a new book, then set it after opening the new one, you'd end up overwriting data in the workbook you just opened.
打开一个新工作簿会导致它被带到前台 - 所以如果我在打开一本新书之前没有抓住活动工作表，然后在打开新书后设置它，你最终会覆盖你刚刚工作簿中的数据打开。
Sub Button1_Click()
' choose LOAD path
objFile = Application.GetOpenFilename(fileFilter:="All Files (* . *) , * . * ") ' browse function
Set curSheet = ActiveSheet
Set mWorkbook = Workbooks.Open(objFile)
curSheet.Activate
Call someFunction(curSheet, mWorkbook)
End Sub
Sub someFunction(targetSheet, srcWorkbook)
numSheets = srcWorkbook.Sheets.Count
For i = 1 To numSheets
targetSheet.Cells(i, 1) = srcWorkbook.Sheets(i).Name
Next i
End Sub
