How to work on files from two different paths with VBA?

[rumshar]

Hi All,

I have a problem in VBA. I have Reports called 'Masters' in one folder and reports called 'CAR' in another. My problem is I need to open both the files from these two different path and work and close them. Next it has to open 2nd master file and 2nd CAR file and so on.

I tried with this code but it is not working out...Any help is appreciated.

[pre]

Sub DoItNow()

MasterPath = Cells(1, 1).Value
CARPath = Cells(1, 2).Value

Set MacroBook = ThisWorkbook

Set MasterFSO = CreateObject("Scripting.filesystemobject")
Set MasterFolderObj = MasterFSO.getfolder(MasterPath)

Set CARFSO = CreateObject("Scripting.filesystemobject")
Set CARFolderObj = CARFSO.getfolder(CARPath)

For Each masterfileobj In MasterFolderObj.Files

For Each CARfileobj In CARFolderObj.Files

Set Masterfile = Workbooks.Open(fileobj.Path)

Set CARFile = Workbooks.Open(CARfileobj.Path)

' Do some work here

CARFile.Close False
Masterfile.Close True

Next CARfileobj
Next masterfileobj ' macro is opening same file everytime. It has to open next master file in path.

End Sub

[/pre]

In the above code CARFile is looping perfectly fine but MasterFile is looping once only which is the main problem. Can any one edit this code or give me someother code(s) so that it saves my time and energy?

Thank you very much in advance.

You may contact me @ sharmarudra@gmail.com


Thanks

 

[Luke M]

From what you posted above, I think this line

Set Masterfile = Workbooks.Open(fileobj.Path)

should be changed to

Set Masterfile = Workbooks.Open(masterfileobj.Path)