VBA 2010 Open files in current folder loop

Solved/Closed
whitepanther - Aug 7, 2011 at 05:43 PM
 whitepanther - Aug 10, 2011 at 04:06 PM
Hello there,

Think this is a relatively simple code and I've found examples but can't quite piece it all together to do what I want to do. I want to write a code that when run on an open workbook will then go to the folder of that active workbook and open the next file to run the macro again - and so on for each file in the folder.

I've found this code which looks like it'll do what I want it to but I'm not sure how to re-write the start so it's not looking for a specified file path and will look in the active workbooks path. I think this is the code I need to use activeworkbook.path but can't seem to piece it together right...

Anaddress=vba.dir(folderaddress\*.mpp)

do while not len(anaddress)=0
.... handle the file as needed

anaddress=vba.dir
loop

Any help appreciated!

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 7, 2011 at 06:00 PM
I am not sure what you mean by "next file" What makes a file "a next file" ?
If you next you just meant that process all files in a folder one by one. If you want to do that then you can use the functionality of DIR

Public Sub doProcessAllFiles() 
   Dim sThisFilePath          As String 
   Dim sFile                  As String 
    
   sThisFilePath = ThisWorkbook.Path 
   If (Right(sThisFilePath, 1) <> "\") Then sThisFilePath = sThisFilePath & "\" 
    
   sFile = Dir(sThisFilePath & "*.xls*") 
   Do While sFile <> vbNullString 
      MsgBox "The next file is " & sFile 
      sFile = Dir 
   Loop 
    
End Sub
2
Hi thanks for that... it sort of works... but I've got a problem.

I have a personal workbook that I save all my macros to (it was a fix we came up with as the automatic personal macro file wasn't working - something to do with the way our system is setup). I always have that workbook hidden.

When I run the code as you've written it it uses the file path for the Personal Macro book as the file path to find the folders which of course doesn't work. So I tried adapting it changing this bit from:
sThisFilePath = ThisWorkbook.Path to sThisFilePath = ActiveWorkbook.Path

but when it runs even though it identifys correctly with the pop up message what the next file in the folder is that it is going to work on it doesn't seem to open the file and instead runs the macro on the same workbook again.... any advice?

P.S. I've put it together with my code like this

Public Sub doProcessAllFiles()
Dim sThisFilePath As String
Dim sFile As String

sThisFilePath = activeWorkbook.Path
If (Right(sThisFilePath, 1) <> "\") Then sThisFilePath = sThisFilePath & "\"

sFile = Dir(sThisFilePath & "*.xls*")
Do While sFile <> vbNullString
MsgBox "The next file is " & sFile
sFile = Dir


'My own code inserted here

Loop

End Sub
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 7, 2011 at 07:56 PM
When you added the comment , ".... handle the file as needed ", I presumed your issue was only limited to how to get the files.
The code that you modified, is only giving you the name of the file. It is not opening any file. You need to open the file

Dim wbBook As Workbook

Set wbBook = Workbooks.Open(sFile)
0
Ok, sorry for being vague/dim but still can't quite get it to work. This is how I've put it together:

Public Sub doProcessAllFiles()
Dim sThisFilePath As String
Dim sFile As String
Dim wbBook As Workbook


sThisFilePath = activeWorkbook.Path
If (Right(sThisFilePath, 1) <> "\") Then sThisFilePath = sThisFilePath & "\"

sFile = Dir(sThisFilePath & "*.xls*")
Do While sFile <> vbNullString
sFile = Dir


'My own code inserted here

Set wbBook = Workbooks.Open(sFile)


Loop

End Sub

It's coming up with Run Time error '1004'. In the message it includes the name of the file it's looking for (which is the right one) but says it can't find it....?
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Aug 8, 2011 at 05:49 AM
see if this helps

Public Sub doProcessAllFiles()
   Dim sThisFilePath          As String
   Dim sFile                  As String
   Dim wbBook                 As Workbook
   
   sThisFilePath = sThisFilePath = ActiveWorkbook.Path
   If (Right(sThisFilePath, 1) <> "\") Then sThisFilePath = sThisFilePath & "\"
   
   sFile = Dir(sThisFilePath & "*.xls*")
   ChDir (sThisFilePath)
   Do While sFile <> vbNullString
      MsgBox "The next file is " & sFile
      'if the next file is same as "active file" then skip the file
      If (sFile = ActiveWorkbook.Name) Then GoTo Next_File
      
      Set wbBook = Workbooks.Open(sFile)
      ' your code here
      
      wbBook.Close SaveChanges:=True
      
Next_File:
      sFile = Dir
   Loop
   Set wbBook = Nothing
End Sub
0
Thanks - tried that but is doesn't seem to like this part:

ChDir (sThisFilePath)

It comes up with Error 76 - Path not found....?
0