If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

working with multiple directories with VBA in excel

I have run into a small problem with a current project I am working on. Bassically what the project will do is open multiple files in multiple directories and reformat some files and combine data between the files to create some reports.

The problem I am having is that when i go to cycle through the the second set of files while cycling through the first set my code seems to "forget" or lose its place within the first directory it is cycling through.

Here is all the code from my project.. the problem occurs during "filename = dir()"

Re: working with multiple directories with VBA in excel

A lot of code to look through, not much time so I just skimmed it. The error is pretty clear though. You can not use Dir() in this fashion and expect it to work the way you are trying to get it to.

When you call Dir() the first time and set the path you override it with the next call. All calls to dir() after that refer to the second instance and not the first.

Jusrt skimming the code I can not tell what exactly you are trying to do with these 2 different paths but you will need to modify the code if you want to cycle through files in 2 different locations using dir()

Re: working with multiple directories with VBA in excel

Yah i think using dir and filesystem might work. Thats what I figured was happening with the dir() having its path overiden i just wasnt sure if there was a way to set up two instances for dir() but from what i have been reading you cant.

To elaborate a bit what im bassically doing is i have a folder that contains 4 sub folders. one of the sub folders holds about 20 files that will eventually become final reports. the other three subfolders hold various files with various types of data to include in the final reports.

what I am trying to do is hold the folder with the main final reports open while cycling through the files in the other 3 subfolders.

Re: working with multiple directories with VBA in excel

Re: working with multiple directories with VBA in excel

Originally Posted by firoz.raj

i did not get Advanced Option.Kindly let me know please.

I have set the advanced editor as my default, so I think I don't even see all options to get there. But the "Quick Reply" window at the end of the thread view for instance has a "Go Advanced" button that will take you there.

Re: working with multiple directories with VBA in excel

I have set the advanced editor as my default, so I think I don't even see all options to get there. But the "Quick Reply" window at the end of the thread view for instance has a "Go Advanced" button that will take you there.

how should i set the advanced Editor as my default ???.i have tried using Quick Reply.But i did not get .Kindly find the attachment also.

Re: working with multiple directories with VBA in excel

Originally Posted by firoz.raj

how should i set the advanced Editor as my default ???.

The default editor setting is in your User CP under "Settings & Options > Edit Options". You will find it way down in the "Miscellaneous Options" window. The correct setting to get the advanced editor is called "Enhanced Interface" there, though.

As about the screen shot of what you get presented as the advanced editor, I've reached the limits of my knowledge now. For me it has always been working just fine, using IE8 on XP pro SP3. You may want to start a new thread about that topic in the Feedback section. I will follow it with interest. (And it's getting a bit off-topic here now... )