I have a piece of code that opens up .csv files within a master folder.... But, I found out that they will each be in separate subfolders (inside master folder). So I need to adjust this code to help me go about searching through the subfolders:
Can anyone guide me in the right direciton? Any help would be appreciated greatly.
Please Login or Register to view this content.
This would do it, but you need to call in from another sub to pass in the Extension, Directory path, and use TRUE for incSub (include Subfolders)
Please Login or Register to view this content.
Last edited by GeneralDisarray; 08-05-2014 at 11:30 AM.
Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
Thanks, I will give it a shot!
Could you briefly explain what the portion of code in red above does?
Also, in my prior code, I set mainwb as the workbook that is active at the start (the one I run the macro from, and the one that I paste stuff that I get from these .csv files). Would I need to outline mainwb in this sub routine, or in the main macro?
The portion in red just calls the sub routine again, for each subfolder (it's iterative, it will snake through sub folders within sub folders... within sub folders....)
The workbook could be passed into either routine - this structure just loops through and doesn't reference any workbooks yet. Be careful how you do that in the "DO YOUR THING TO THE FILE HERE" section.
So just to clarify:
I know that my subfolders will not contain any more subfolders... A 1-1 ratio, each subfolder has one .csv file, the file that im looking for.
Since this is the case, and nothing is buried too deep, this incSub will only iterate ONCE (because its only one layer deep)?
Lastly, say I call mainwb = ActiveWorkbook at the very start of the main routine, will that definition change based on what is currently active? In other words, I need mainwb to stay constant throughout this entire code because I continue to reference it (and I will reference it in this sub routine many many times).
So just to clarify:
I know that my subfolders will not contain any more subfolders... A 1-1 ratio, each subfolder has one .csv file, the file that im looking for.
Since this is the case, and nothing is buried too deep, this incSub will only iterate ONCE (because its only one layer deep)?
Lastly, say I call mainwb = ActiveWorkbook at the very start of the main routine, will that definition change based on what is currently active? In other words, I need mainwb to stay constant throughout this entire code because I continue to reference it (and I will reference it in this sub routine many many times).
*SORRY FOR DUPLICATE POST* computer was lagging
Last edited by hutch94; 08-05-2014 at 01:51 PM.
Here's what I would suggest. Add another parameter to the loop routine to allow you to pass in a workbook variable (byRef).
Then, in the main routine that will call the loop routine, SET the workbook as needed. After that, you can refer to that workbook by variable name SomeWorkbook in the Loop routine - no matter which iteration.
Having only one subfolder won't be any problem - the code can just handle as many as needed. The "For Each" style of loop is designed to loop over a "collection" (in this case, a collection of subfolders for any folder) and is by design flexible enough to handle 0,1,2,3.... members in the collection.
BTW, I didn't make these edits within the IDE so watch for typos (always use "Option Explicit" IMO)
Please Login or Register to view this content.
Last edited by GeneralDisarray; 08-05-2014 at 02:05 PM.
I get an Expected:expression bug when I try to compile.
It happens when I CALL the sub routine into play, specifically it highlights byRef
AHHHH! i should have typed it into the IDE.
That shouldn't be there (it only needs to appear below, in the LoopDirectoryExample definition of parameters)
Take that "ByRef" out!
Please Login or Register to view this content.
Awesome it compiled! Many thanks.
The mechanisim is working perfectly.
However one quick problem:
The main goal is to open the csv file and paste contents into the target (main) WB on separate sheets. I did this by tracking the sheet index. I made a variable called sheet_index which I added +1 to everytime through the loop, this told the macro to paste the data onto the next sheet, or create a new sheet and paste depending on how many there were in the work book. NOW I can't use that method it seems. Below I have posted a shortened version of the main code. Currently, it just copies everything onto the same sheet...
Please Login or Register to view this content.
Last edited by hutch94; 08-05-2014 at 04:06 PM.
To more concisely phrase it:
Where should I define sheet_index as Integer, initialize it as 1, and then tell it to sheet_index++ each time it gets to a new csv file?
The current locations are not working because this new method iterates in a different fashion than before.
Try it without the index - just add a new sheet every time. It's a good habit to use Option Explicit and always introduce variables used in a sub at the top with a DIM statement (like your WB).
Also, instead of just referencing "sheets()" or "range()" it's a good idea to qualify everything. So, instead of a sheets.add (which you then refer to later) I would use "Set NewSheet = WorkbookVariable.add"
When you fully qualify, you also don't have to worry about what's active or not -- all those .select or .activate statements can be trimmed out
Also, your code to copy then paste can be altered to "equate values of same-sized ranges".
Please Login or Register to view this content.
Last edited by GeneralDisarray; 08-05-2014 at 04:22 PM.
Thanks! This does the job, and works well! And way more efficient!
If you can't tell haha, I am super new to VBA... I know C, so I was able to figure the logic out, but the new syntax and object oriented stuff was killing me.
Thanks for the quick help
I will let you know if I run into any other problems
No problem, we've all been there. My only programing experience prior to working in excel was Turbo Pascal, and Python / C also
Please mark your original post as "Solved"
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks