Excel - VBA问题。需要从目录中的所有excel文件访问数据，而不打开文件 [英] Excel - VBA Question. Need to access data from all excel files in a directory without opening the files

问题描述

所以我有一个masterexcel文件，我需要填充从目录中的excel文件的数据。我只需要访问每个文件，并从每个工作簿的第二个表复制一行，并粘贴到我的主文件，而无需打开Excel文件。

我不是这方面的专家，但我可以处理一些中间宏。我需要的最重要的是能够逐个访问每个文件，而无需打开它们。我真的需要这样任何帮助是赞赏！谢谢！

编辑...

所以我一直在尝试使用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）
  

wbname如何向下移动文件列表？ p>

解决方案

我只是想指出：你不是严格需要VBA来从一个封闭的工作簿中获取值。您可以使用以下公式：

  ='C：\MyPath\ [MyBook.xls] Sheet1'！$ A $ 3 
  

您也可以在VBA中实现此方法：

Dim rngDestinationCell As Range
Dim rngSourceCell As Range
Dim xlsPath As String
Dim xlsFilename As String
Dim sourceSheetName As String

设置rngDestinationCell = Cells（3,1）'或Range（A3）
设置rngSourceCell = Cells（3,1）
xlsPath =C：\ MyPath
xlsFilename =MyBook.xls
sourceSheetName =Sheet1

rngDestinationCell.Formula ==_
& '& xlsPath& \ [& xlsFilename& ]& sourceSheetName& ！ _
& rngSourceCell.Address


其他答案也提供了很好的解决方案，也许比这更优雅。

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.

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.

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?

解决方案

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:

='C:\MyPath\[MyBook.xls]Sheet1'!$A$3

You can implement this approach in VBA as well:

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.

这篇关于Excel - VBA问题。需要从目录中的所有excel文件访问数据，而不打开文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！