CODEWITHSUNDEEP
vbaexcel
user3276264
user3276264

Reputation: 15

VBA - Trying to open all workbooks in a folder

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.

Upvotes: 1

Views: 5865

Answers (2)

Unicco
Unicco

Reputation: 2580

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

Upvotes: 4

Tim Williams
Tim Williams

Reputation: 166126

Dir() only returns the file name, not the full path: you need to pass the full path to Open() unless the current directory happens to be the one you're searching through. It's best never to rely on that being the case.

Upvotes: 2

Related Questions