VBA - 尝试打开文件夹中的所有工作簿 [英] VBA - Trying to open all workbooks in a folder

问题描述

我试图循环并打开位于与我的宏的工作表相同的目录中的名为（BU）的文件夹中的所有文件。我可以看到我的文件正确获取第一个文件名，但是当工作簿尝试打开时，我收到运行时错误1004。任何帮助将不胜感激。

  Sub LoopAndOpen（）
 
 Dim myfile As String，Sep As String ，stringA As String，path1 As String 
 Sep = Application.PathSeparator 
 path1 = ActiveWorkbook.Path& Sep& BU& 9月
 
 myfile = Dir（path1&* .xlsm）
 
尽管myfile<> 
 
 Workbooks.Open myfile 
 myfile = Dir（）
循环
 
 End Sub 
  / pre> 
 
 
编辑：我最终使用了Unicco的程序，它的工作完美。 
解决方案
可以使用此过程。 
 
 
 
将ThisWorkbook.Path和.xlsm修改为您想要的目的。如果要使用宏打开Excel文件，请使用InStr（objFile，.xlsm）或InStr（objFile，.xlsx）。
  Option Explicit 
 Sub OpenAllFiles（）
 
 Dim objFSO As Object 
 Dim objFolder As Object 
 Dim objFile As Object 
 
设置objFSO = CreateObject（Scripting.FileSystemObject）
 
设置objFolder = objFSO.GetFolder（ThisWorkbook.Path）
 
对于每个objFile在objFolder.Files 
如果InStr（objFile，.xlsm）然后
 Workbooks.Open（objFile）
 End If 
下一个
 
 End Sub 
  
 
I'm trying to loop through and open all files in a folder named (BU) located in the same directory as the sheet where my macro is. I am able to see the myfile get the first file name correctly, but I am getting a run time error 1004 when the workbook tries to open. Any help would be appreciated.
Sub LoopAndOpen()

  Dim myfile As String, Sep As String, stringA As String, path1 As String
  Sep = Application.PathSeparator
  path1 = ActiveWorkbook.Path & Sep & "BU" & Sep

  myfile = Dir(path1 & "*.xlsm")

  Do While myfile <> ""

     Workbooks.Open myfile
     myfile = Dir()
  Loop

End Sub
Edit: I ended up using Unicco's procedure and it worked perfectly. 
解决方案
You can use this procedure instead. 


Modify "ThisWorkbook.Path" and ".xlsm" to your desired purpose. Use InStr(objFile, ".xlsm") Or InStr(objFile, ".xlsx") if you want to open both standard aswell as Excelfiles with macros.
Option Explicit
Sub OpenAllFiles()

    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    Set objFolder = objFSO.GetFolder(ThisWorkbook.Path)

    For Each objFile In objFolder.Files
        If InStr(objFile, ".xlsm") Then
            Workbooks.Open (objFile)
        End If
    Next

End Sub


                        
这篇关于VBA  - 尝试打开文件夹中的所有工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！