Excel - VBA 问题。需要在不打开文件的情况下访问目录中所有 excel 文件中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7524064/
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 - VBA Question. Need to access data from all excel files in a directory without opening the files
提问by user960358
So I have a "master" excel file that I need to populate with data from excel files in a directory. I just need to access each file and copy one line from the second sheet in each workbook and paste that into my master file without opening the excel files.
所以我有一个“主”excel 文件，我需要用目录中的 excel 文件中的数据填充它。我只需要访问每个文件并从每个工作簿的第二个工作表中复制一行并将其粘贴到我的主文件中，而无需打开 excel 文件。
I'm not an expert at this but I can handle some intermediate macros. The most important thing I need is to be able to access each file one by one without opening them. I really need this so any help is appreciated! Thanks!
我不是这方面的专家，但我可以处理一些中间宏。我需要的最重要的事情是能够在不打开它们的情况下一个一个地访问每个文件。我真的需要这个所以任何帮助表示赞赏！谢谢！
Edit...
编辑...
So I've been trying to use the dir function to run through the directory with a loop, but I don't know how to move on from the first file. I saw this on a site, but for me the loop won't stop and it only accesses the first file in the directory.
所以我一直在尝试使用 dir 函数来循环遍历目录，但我不知道如何从第一个文件开始。我在一个网站上看到了这个，但对我来说循环不会停止，它只访问目录中的第一个文件。
Folder = "\Drcs8570168\shasad\Test"
wbname = Dir(Folder & "\" & "*.xls")
Do While wbname <> ""
i = i + 1
ReDim Preserve wblist(1 To i)
wblist(i) = wbname
wbname = Dir(FolderName & "\" & "*.xls")
How does wbname move down the list of files?
wbname 如何在文件列表中向下移动？
回答by brettdj
You donthave to open the files (ADO may be an option, as is creating links with code, or using ExecuteExcel4Macro) buttypically opening files with code is the most flexible and easiest approach.
你不必须打开文件（ADO可能是一种选择，如创建的代码链接，或使用ExecuteExcel4Macro） ，但通常与代码打开文件是最灵活和最简单的方法。
But why don't you want to open the files - is this really a hard constraint?
但是你为什么不想打开文件——这真的是一个硬约束吗？
My code in Macro to loop through all sheets that are placed between two named sheets and copy their data to a consolidated filepulls all data from all sheets in each workbook in a folder together (by opening the files in the background).
我在宏中的代码循环遍历放置在两个命名工作表之间的所有工作表并将它们的数据复制到一个合并的文件中，将文件夹中每个工作簿中所有工作表中的所有数据拉在一起（通过在后台打开文件）。
It could easily be tailored to just row X of sheet 2 if you are happy with this process
如果您对这个过程感到满意，可以很容易地将它定制为第 2 页的第 X 行
回答by Jean-Fran?ois Corbett
I just want to point out: You don't strictly need VBA to get values from a closed workbook. You can use a formula such as:
我只想指出：您并不严格需要 VBA 从封闭的工作簿中获取值。您可以使用一个公式，例如：
='C:\MyPath\[MyBook.xls]Sheet1'!$A
You can implement this approach in VBA as well:
您也可以在 VBA 中实现这种方法：
Dim rngDestinationCell As Range
Dim rngSourceCell As Range
Dim xlsPath As String
Dim xlsFilename As String
Dim sourceSheetName As String
Set rngDestinationCell = Cells(3,1) ' or Range("A3")
Set rngSourceCell = Cells(3,1)
xlsPath = "C:\MyPath"
xlsFilename = "MyBook.xls"
sourceSheetName = "Sheet1"
rngDestinationCell.Formula = "=" _
& "'" & xlsPath & "\[" & xlsFilename & "]" & sourceSheetName & "'!" _
& rngSourceCell.Address
The other answers present fine solutions as well, perhaps more elegant than this.
其他答案也提供了很好的解决方案，也许比这更优雅。
回答by JMax
brettdjand paulsm4answers are giving much information but I still wanted to add my 2 cents.
brettdj和paulsm4 的答案提供了很多信息，但我仍然想加上我的 2 美分。
As iDevlopanswered in this thread ( Copy data from another Workbook through VBA), you can also use GetInfoFromClosedFile().
正如iDevlop在此线程中回答的（通过 VBA 从另一个工作簿复制数据），您还可以使用GetInfoFromClosedFile()。
回答by Oliver
Some bits from my class-wrapper for Excel:
我的 Excel 类包装器中的一些内容：
Dim wb As Excel.Workbook
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.DisplayAlerts = False ''# prevents dialog boxes
xlApp.ScreenUpdating = False ''# prevents showing up
xlApp.EnableEvents = False ''# prevents all internal events even being fired
''# start your "reading from the files"-loop here
Set wb = xlApp.Workbooks.Add(sFilename) '' better than open, because it can read from files that are in use
''# read the cells you need...
''# [....]
wb.Close SaveChanges:=False ''# clean up workbook
''# end your "reading from the files"-loop here
''# after your're done with all files, properly clean up:
xlApp.Quit
Set xlApp = Nothing
Good luck!
祝你好运！
回答by Bevans
At the start of your macro add
在宏的开头添加
Application.ScreenUpdating = false
then at the end
然后在最后
Application.ScreenUpdating = True
and you won't see any files open as the macro performs its function.
并且在宏执行其功能时您不会看到任何文件打开。
