VBA 2010 Open files in current folder loop [Solved/Closed]

whitepanther - Aug 7, 2011 at 05:43 PM - Latest reply:  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!
See more 

20 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 7, 2011 at 06:00 PM
1
Thank you
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

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1699 users this month

Sorry scrap that last note about that line of code for moving to next file. Just took it out and tried it again and it's working now :) .... still not sure about the printing problem though. Does it have something to do with the string commands at the beginning of the script?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 9, 2011 at 07:23 PM
For printer, string does not have to do with it. The best advice that I can give is that, you record your macro to see how printer is set up.
Start the macro recorder
click on print
change the printer
and print
stop the recorder
that I am inclined to think will give you how to change printer.
Gosh, ok - got myself in a muddle over all that but think I've straightened it out.
(Sorry you must be getting sick of me by now!)

As I discovered when I first wrote this macro recording changing the printer doesn't work. That's why I hunted down the above code. What I since discovered was it won't work unless you have the exact printer name (the one I'm using is on a network). I managed to find a code to figure out what the actual printer name is. You have to change the printer to the one you want to use then run this code:

Sub test()
MsgBox Application.ActivePrinter
End Sub

and bingo you get a nice little pop-up with the full network printer name. So got that sussed properly.

I tried putting the code we've been working on back in and it works fine showing the right file path and file name in the pop up messages except when it gets to this part now:

Set wbBook = Workbooks.Open(sFile)

It comes up with Error 1004 - "name of file" could not be found. This is what I've got:


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*")
MsgBox "The path is " & sThisFilePath
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)

'My code here

wbBook.Close SaveChanges:=True

Next_File:
sFile = Dir
Loop
Set wbBook = Nothing

End Sub


At first I thought it might be because the workbook it was trying to open was already open (I had ignored the go to next file command) but I activated that code again and tried it so it would go to the next file and it identified it properly but still came up with the error. I've checked it and checked but can't see anything that's different to what I ran earlier today when it worked - am not sure what's gone wrong...?
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Aug 10, 2011 at 04:11 AM
see if this work

instead of Set wbBook = Workbooks.Open(sFile)
try
Set wbBook = Workbooks.Open(sThisFilePath & sFile)
Success! - you're a legend - Thank you so much for your help and patience :)