使用循环打开文件路径中的所有 excel 文件后，有没有办法通过 vba 创建工作簿变量来引用这些文件?

Question

Dim MyFolder As String
Dim MyFile As String
MyFolder = "C:--"
(leaving out the file path)
MyFile = Dir(MyFolder & "*.xlsx")
Do While MyFile <> ""
Workbooks.Open fileName:=MyFolder & "" & MyFile
MyFile = Dir
Loop
(see paragraph below)
Workbook.Open
Dim wbk1 as workbook
Set wbk1 = ActiveWorkbook
(can reference workbook like this)
wbk1.Activate

我查看了其他几个论坛，发现您可以通过首先打开其他工作簿来引用它们，然后创建一个变量并将其设置为打开的工作簿，如上面第二个代码段落中列出的那样。

当我尝试想出一种方法来为这个特定文件路径中的所有文件创建引用变量时，我意识到我无法在运行时动态命名不同的变量以将每个工作簿设置为不同的工作簿变量。

那么，除了完成我尝试完成的任务之外，是否有其他选择，或者有没有办法动态创建变量？

Answer 1

以下是您的操作方法：(这会将其作为具有新名称的模板打开(

Dim wb1 as Workbook
Set wb1 = Workbooks.Add(MyFile)

或者：(如果工作簿已经打开，这将失败((如果您需要以后能够保存它，请使用这个(

Dim wb1 as Workbook
Set wb1 = Workbooks.Open(MyFile)

然后，您可以创建一个工作表对象，如下所示：

Dim ws1 as Worksheet
Set ws1 = wb1.Worksheets(1)

然后，每当您想引用该工作表上的内容(例如Range或Cell(时，请确保使用如下所示的Worksheet引用对其进行限定：

Dim rng as Range
Set rng = ws1.Range("A1:B1")

Answer 2

您可以使用数组、集合或字典来保存对多个工作簿的引用。
最好打开单个工作簿，执行所需的操作，将其关闭，然后使用相同的变量打开下一个工作簿。

注意：要在变量中正确存储工作簿，请使用@brax提供的代码。

但。。。这是你要求的：

此代码将打开文件夹中的每个工作簿，然后返回有关每个工作簿的信息。

Option Explicit 'You wouldn't believe how important this is at the top of your module!
Public Sub Test()
Dim MyFolder As String
Dim MyFiles As Collection
Dim wrkBk As Workbook
Dim sFile As String
Dim secAutomation As MsoAutomationSecurity
MyFolder = "C:"
Set MyFiles = New Collection
'We don't want any WorkBook_Open macros to fire when we open the file,
'so remember the current setting and then disable it.
secAutomation = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
sFile = Dir$(MyFolder & "*.xls*")
Do While Len(sFile) > 0
'We don't want to open the file if it's got the same name as this one.
If sFile <> ThisWorkbook.Name Then
'Open the workbook and add it to the collection, give it a key of the file name.
Set wrkBk = Workbooks.Open(MyFolder & sFile)
MyFiles.Add wrkBk, wrkBk.Name
Set wrkBk = Nothing
End If
sFile = Dir$
Loop
'Reset macro security settings.
Application.AutomationSecurity = secAutomation
'----------------
'All files are open and ready to be referenced.
'----------------
Dim SingleFile As Variant
'List all details from each file in the immediate Window.
For Each SingleFile In MyFiles
With SingleFile
Debug.Print "Name:  " & .Name & " | Sheet Count: " & .Sheets.Count & _
" | Last Row on '" & .Worksheets(1).Name & "' Column A: " & .Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
End With
Next SingleFile
'Get the value from a specific file using the key value (Book7.xlsm)
Debug.Print MyFiles("Book7.xlsm").Worksheets("Form").Range("A6")
'Now close all the files.
For Each SingleFile In MyFiles
Debug.Print "Closing " & SingleFile.Name
SingleFile.Close SaveChanges:=False
Next SingleFile
End Sub